Every new database connection registered in Pentaho is automatically published as JNDI data source.
The JNDI datasource is a common feature in JavaEE. Basicly a connection name can be used as alias in external tools, solving maintenance problems when JDBC parameters changes.
A new data source can be directly registered from Pentaho Business Intelligence portal (Click File->Manage->Data sources..) or from Pentaho Administration console (provided with the default installation in the folder administration-console).
Install the correct JDBC driver
The first step is to check if the JDBC driver of the destination data base is installed in the Pentaho platform (for MySQL the driver is already deployed by default). The JDBC driver can be downloaded from the database vendor web site and must be deployed in:
- pentaho/tomcat/lib - for Pentaho BI portal
- administration-console/jdbc - for Pentaho Administration Console
For example in the case of MySQL a file called mysql-connector-xyz.jar is already present in the above folders. If this file is not present you can download the driver from the MySQL web site and copy the .jar in these folders.
In this tutorial we will use an Oracle XE 10 database, which JDBC driver can be downloaded from http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html. For Oracle XE 10, a good choice is the ojbc14.jar driver.
Copy the ojbc14.jar file on pentaho/tomcat/lib and administration-console/jdbc folders and then restar all Pentaho server instances.
Create a new database connection in Pentaho BI suite
Creating a new data base connection is easy once the correct JDBC driver is deployed and can be done from either Pentaho web portal or Pentaho administration console.
Make sure that the Pentaho BI server is already running and the JDBC driver of the data base vendor (eg Oracle) is copied in the folders mentioned above. To start Pentaho administration console run start-pac.bat (start-pac.sh on Unix) from the folder administration-console and point your browser to http://localhost:8099/. The default Pentaho administration console credentials are:
After login press Administration button and then Data base connections tab, as in the following picture.
Now press the plus button next to the Database connection label and in the new window fill you data base connection credentials. For Oracle XE 10 the parameters are:
Name: a connection name, for example OraDataSource
Driver class: oracle.jdbc.OraceDriver
User Name: <your username>
Password: <your password>
Finally Test the connection and If everyting is fine then press Ok. In case of error make sure the JDBC driver is correcly deployed in Pentaho folders and the connection parameters are correct.
Using the new connection
The new connection is now ready to be used. After rebooting the server the new connection can be used on Pentaho Business Intelligence web portal (http://localhost:8080) by pressing File -> New -> Datasource..
Pentaho publish a data base connection as a JNDI datasource, so you can use its name in any Pentaho compatible external tool, like Pentaho Schema Workbench or Pentaho Report Designer. While using a JNDI datasource make sure the JDBC driver is deployed on the lib folder of each tool you are using and the tool is properly configure to use the JNDI connection. For example:
- In Pentaho Report Designer JNDI update configuration-template/simple-jndi/default.properties.
- In Pentaho Schema Workbench JNDI update /.pentaho/simple-jndi/default.properties file in your unser home (in Windows C:\Documents and Settings\<windows user>\.pentaho\simple-jndi\default.properties)
This means that when a report is deployed from Pentaho Report Designer to the Pentaho web portal the JNDI connection name is used to estabilish the connection.
default.properties is simply a support file used to define the JNDI connection name and its parameters during time, however the real connection used in production is the one created in Pentaho Business Intelligence web platform.