Setting Up Pentaho Business Intelligence with xTuple ERP

UPDATE: This information is now outdated and is no longer supported. Please visit xTuple Forums for up-to-date discussion.

I want to talk briefly about my experiences integrating and using Pentaho Business Intelligence tools over an xTuple ERP system. This was relatively easy to set up and is now providing comprehensive reports from xTuple and other systems. I know xTuple has its own report writing solution, which is very good, however Pentaho gives us the ability to allow end-users to author their own reports and provides a Metadata model that converts technical database terms (tablenames, field names) into business terms that your organisation can understand. Furthermore, Pentaho allows you to access and report on xTuple and non-xTuple data presented in a consolidated reporting portal.

I am not going to talk about the installation of the systems as Pentaho is quite complex and that is a major topic in itself. I am using a xTuple 3.3.1 system and Pentaho's latest community release 3.5. I am also using the PostgreSQL 8.4 database which impacts on the drivers required. Both systems are running on Ubuntu Linux 64 bit server.

In order to make the reporting solution work you will need to set up a PostgreSQL database account that has read access to the information you want to report on. In my case I limited the account to the api schema and ensured it was read only so it cannot be used to update information. You might want to restrict the account to specific views if your information is more sensitive. This account will be used throughout the Pentaho solution.

You will need to have an account on the Pentaho Administration Console in order to set up the connections to your xTuple system. You will need to set up your reporting authorisation model by creating roles and users in the Administration Console. Make sure you set up yourself as an Admin user in the system as the login to the Console is not the same as a reporting user account. Take note of the directory the Pentaho solution is installed on. I will refer to the Pentaho installation directory as $pentaho from here. You will also need the Publisher password which is used in addition to your Pentaho account to prevent unauthorised publishing to the reporting portal.

First you need to install the correct JDBC drivers for the release of PostgreSQL that you are using. This is a simple matter of copying the jdbc driver jar file (in my case that is postgresql-8.4-701.jdbc3.jar) to the $pentaho/administration-console/jdbc directory. You will need to restart the administration console service for the change to apply. Log into the Administration Console and go to the Database Connections tab. Create a new connection to point to your xTuple database. Give the connection a meaningful name. Remember this name as you will need to name your local client connection the same for your report to work once you publish it to the Pentaho BI server. Select the PostgreSQL driver class (in my case org.postgresql.Driver). Enter the user account and password that you set up on the PostgreSQL database previously. Enter the jdbc URL for connecting to the database. The URL consists of jdbc:postgresql://server_name_or_ip:port_number/database_name. Save.

Now you are ready to begin writing reports. In my example I am building a very simple model to report on CRM Account information. Open the MetaData Editor client tool. The first thing you need to do is import the security roles and users from your Pentaho solution so you can assign authorisations to the model. Log in to the Pentaho system using your account you set up in the Admin Console previously.

Now you need to set up a connection to the xTuple system. Make sure you use the same name as you defined in the Database Connections in the Console. You can now begin adding xTuple tables or views, relationships between tables, renaming fieldnames to business names, add calculated fields and whatever else you might require. In my example I have selected just the CRM Account view, picked a few fields, and provided business names and descriptions. Make sure you assign authorisations from the imported security model or you will not be authorised to write or run reports from this model.

Once you are happy with your model you need to publish it to the server. Under the File menu there is an option Publish to Server... Enter the Pentaho server name, and location that you want the model stored in. For some reason the dialog always defaults to steel-wheels so you will need to change that each time to the directory you want to save the model to. Enter the Publisher password and your username and password to the Pentaho system. The model is now published and can be used by report writers across your company.

Log on to the Pentaho User Console. Select New Report.

You and your peers can now quickly develop reports using online drag and drop functionality.

The Report Designer is a client tool for developing complex reports, with report selections, graphs, images, and highly formatted layouts. This works similarly to the above. You need to set up a connection, develop the report, then publish it to the Pentaho BI server. I hope this helps shed some light on the setup of comprehensive reporting over the xTuple ERP system.