Adding Postgresql support to Pentaho BI Server Community Edition

At the start of a new project, I wanted to use postgresql database as my database repository for a BI dashboard project. I had selected Pentaho as the BI Server of choice along with its associated components. Previous posts have walked through the installation of the Pentaho BI Server Community Edition on a Ubuntu box as well as Installation of Dashboard tools on Pentaho BI Servers Community Edition.

I launched the Pentaho BI Server and accessed the Community Data Dashboard to see the default dashboard.

The first step is to add a data source, which I did my accessing File> New > Data Source on the top level menu for Pentaho BI Server.

This brings the following page, where I selected Database Table(s) as the option.

Selecting Database Table(s) brings the following options, where I gave the database name and the option that I wanted to use the data source for reporting and analysis.

At this point, it also lists the available connections on the Pentaho server environment. To add a new connection, you need to click on the + button to the right of list of data connections, that is a green circular button with a plus sign.

Clicking on the button brings the following dialog box to define a new connection.

As you can see, Postgresql is not a supported option. Thankfully, they have provided a link for Adding Databases, which is a help link that provides the information needed. It suggests that to add support for a new database, it has to be deployed and configured in multiple places, and provides the list of places where changes need to be made.

Here is a step by step description of what all I did.

Downloading the Postgresql JDBC drivers

First step was to download the Postgresql JDBC drivers. Postgresql JDBC drivers are available at http://jdbc.postgresql.org/download.html

The page provides links to the latest drivers as well as older drivers. To ensure I was getting the right version of the drivers, I opened a new terminal window, launched Postgresql and typed the command to get the version.

> psql

psql > show server_version;

So I downloaded the JDBC drivers for Postgresql 9.1 from the website and copied them to the folder where I could access them.

Before copying them over to the pentaho location, I had to shutdown the Pentaho server, which I did by issuing the stop command in the Pentaho installation folder.

> ./stop-pentaho.sh

I copied the driver jar file to the first location below.

//Work/Servers/Pentaho/biserver-ce/tomcat/lib

I also copied postgresql to the administration-console folder for pentaho.

Then, I restarted the Tomcat server and retraced my steps to define a new connection as shown earlier in this post by accessing File > New > Data Source on the menu and clicking on the option to add a new connection.

On the connections dialog box, we can now see the option to add Posrgresql as a valid data source.

Adding all the details in for the connection, we can add the connection to the environment.

Now we can configure our portal using postgresql data.