Registering a new database connection is quite easy in Pentaho, and any new database connection is automatically published as JNDI data source as well.
The JNDI datasource name is a common used feature in JavaEE enviroment, where a connection name can be used in external tools (if proprely configured), solving maintenance problems when JDBC connection parameters changes.
You can register a new data source directly from Pentaho Business Intelligence portal (Press File->Manage->Data sources..) or using the Administration console provided with the default installation in the folder administration-console.
Install the correct JDBC driver
The first step is to make sure if the correct JDBC driver for the data base you are using is installed in Pentaho platform.
Your JDBC driver must be deployed in
- pentaho/tomcat/lib - for Pentaho BI portal
- administration-console/jdbc - for Pentaho Administration Console
For example if your target database is MySQL a file called mysql-connector-xyz.jar must be deployed in the above folders. If this file is not present already you can download the driver from MySQL web site and copy the .jar in the folders.
In this tutorial we'll 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 to make the driver avaiable to Pentaho platform.
Create a new database connection in Pentaho BI suite
Once the correct JDBC driver is deployed defining a new data base connection is easy and can be done from Pentaho web portal or from Pentaho administration console. In this example we'll use Pentaho administration console because this tool can be usefull in many situations.
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/
Make sure Pentaho BI server is running and the JDBC driver of your data base vendor (Oracle) is published in the previous folders.
The default Pentaho administration console credentials are:
username: admin
password: password
After login press Administration button and then Data base connections tab, like in the next picture.
Now press the plus button next Database connection label and in the new window write you data base connection credentials. For Oracle XE 10 the parameters are as follow:
Name: a connection name, for example OraDataSource
Driver class: oracle.jdbc.OraceDriver
User Name: <your username>
Password: <your password>
URL: jdc:oracle:thin:@<host>:<1521>:XE
Now try to Test the connection. If everyting is fine then press Ok and you have done. If some error appears make sure the JDBC driver is correcly deployed in Pentaho's folders and the connection parameters are correct.
Using the new connection
Now you can use the new connection. After rebooting the server you can use the new connection for example by pressing File -> New -> Datasource.. on Pentaho Business Intelligence web portal (http://localhost:8080).
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 JNDI parameters are defined in simple-jndi/default.properties. This file can be found on each tool folder or in /.pentaho of your home folder, for example:
- In Pentaho Report Designer JNDI connections must be defined in configuration-template/simple-jndi/default.properties.
- In Pentaho Schema Workbench JNDI connections must be defined in /.pentaho/simple-jndi/default.properties file in your unser home, for example in Windows C:\Documents and Settings\<windows user>\.pentaho\simple-jndi\default.properties
When you deploy a report built in Pentaho Report Designer to a Pentaho web portal, the JNDI connection used is the one defined in Pentaho web portal, while the connection parameters defined in default.properties are used only for the tool.
default.properties is simply a support file while you define a JNDI connection name and its parameters for design time, but the real connection used in production is the one defined in Pentaho Business Intelligence web platform.
Post new comment