01. Setting Up Your Database Connections

Unknown macro: {scrollbar}

Connections

The Pentaho Metadata Editor (and the Pentaho metadata architecture) supports a vast and rich set of data sources. Before you begin defining your business model, you must first describe the database or data source that you would like to model. You do this by defining one or more connections in the editor.

One Connection Per Business Model

While the current implementation of metadata supports multiple connections and multiple business models in the same domain, each business model can use physical objects (columns and tables) from only a single connection. We hope to expand this functionality so one model can use two or more connections' tables and columns, but for now, it's important to understand this limitation.

Creating a New Connection

To create a new connection:

  1. Right-click (or CTRL-click) on the Connections branch of the Tree Navigator on the left side of the editor screen.
  2. Select the New Connection... option from the popup menu.
  3. The Connection Information dialog will be displayed. This dialog allows you a rich set of options for defining your database connection in detail.


The Connection Information Dialog

This dialog deserves a bit of explanation before we continue.

Connection Types

First, note the Connection Type list on the General tab of the dialog. This is the list of database connections that the Pentaho Metadata Editor and metadata models can support. The list is quite extensive. If you do not see your database in the Connection Type list, you may be able to add it. To add a new database type, you will have to copy the JDBC driver archive for your database into the [studio:PME install directory]/libext/JDBC directory. Restart the Pentaho Metadata Editor, and you will see your database in the Connection Type list.

Method of Access

Under Connection Type, you will see the Method of Access list. Defining a JDBC or OBDC connection typically requires all of the remaining fields on the general tab have the correct information. But if you are into abstracting those details from your metadata domain, then using the JNDI method of access is for you. The JNDI access method keeps your server implementation cleaner as well; once you publish your domain to the server, as long as you have defined the JNDI connections with the same names, you still have a nice implementation where your database information is only described to the JNDI layer.

To take advantage of the JNDI method of access in the Pentaho Metadata Editor, you will need to define your database connection information in a properties file for the editor. We walk through a JNDI connection studio:later in this page.

An Example Connection: A JDBC Connection to a MySQL Database

This example uses the Steels Wheels sample data provided with the Pentaho Open BI Suite (aka the "pre-configured installation" or "PCI"), available from our downloads site.

To connect to a MySQL database using JDBC:

  1. Name your connection. For this example, we will name this connection MySQLSampleData .
  2. Select MySQL as the connection type.
  3. Select Native(JDBC) as the access method.
  4. Enter localhost as the server host name. This assumes you have a local Pentaho BI Server running. If your server (or your sample database) is hosted elsewhere, enter the name of that host here instead.
  5. Enter the name of the database, in our example, sampledata.
  6. Our example requires the default port for MySQL. Adjust the port as necessary for your setup.
  7. And last, the username and password for the Steel Wheels sample data is pentaho_user, password, respectively.
  8. You should now click the Test button at the bottom of the dialog. If all the information is entered properly, you should receive a "Test OK" message. If something is wrong, you should receive a message that is specific enough to help you determine the problem.
  9. Click OK when you are satisfied with your connection information.

An Example Connection: A JNDI Connection to a Hypersonic Database

Our second example starts out much like the first, but requires a few different steps.

  1. Name your connection. For this example, we will name this connection HySampleData .
  2. Select Hypersonic as the connection type.
  3. Select JNDI as the access method.
  4. Once you've selected JNDI, notice that the only remaining piece of information that you need to enter on the General tab is the database name. Enter SampleData here.

This name is actually the JNDI name that you will specify to map to the JNDI connection properties. In order for the Pentaho Metadata Editor to know what this name really means, you need to set up a simple JNDI properties file.

  1. Navigate to [studio:PME install directory]/simple-jndi/jdbc.properties file. Open this file in your favorite text editor.
  2. Enter the following text into the jdbc.properties file:

    SampleData/type=javax.sql.DataSource
    SampleData/driver=org.hsqldb.jdbcDriver
    SampleData/url=jdbc:hsqldb:hsql://localhost/sampledata
    SampleData/user=pentaho_user
    SampleData/password=password

  3. Save the file and close it.
  4. Go back to the Connection Information dialog, and click the Test button. If all goes well, you should get a "Test OK" message. If not try saving your connection, exiting the Pentaho Metadata Editor, restarting the editor and trying the Test button again.

More on the Connection Information Dialog

See the studio:Database Connection Guide for more information about this dialog.

0%

TODO