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.
alternative link download