Setting Up Pentaho Business Intelligence with xTuple ERP

 

anderson's picture

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.

AttachmentSize
Pentaho New Report40.7 KB
Admin Console47.31 KB
MetaData Connection Setup50.47 KB
Example MetaData Model33.24 KB
Publishing the MetaData Model36.07 KB
Importing MetaData Security Roles42.8 KB
Pentaho Adhoc Report Writer53.72 KB
 
dkmeans's picture
Offline
Joined: 11/27/2007
Wow - this is fantastic!

Wow - this is fantastic!

 
rcharbonneau's picture
Offline
Joined: 11/17/2007
Pentaho Report Designer

Hi,

Has anyone work with the Pentaho Report Designer and was able to do sub-report?

If the answer is YES;
- Pentaho version:
- PostgreSQL version:
- Report Designer version:

Thanks,

Robert Charbonneau

 
rcharbonneau's picture
Offline
Joined: 11/17/2007
Found it!

Hi,

You need to define your queries at the report header and use select it in the sub-report.

Watch out, in the sub-report, parameters are reversed the OUTER NAME is the field from the PARENT query (don't select it, type it) and the INNER Name is the parameter name (type it too). You them use the parameter in your WHERE CLAUSE of your sub-report query.

Robert Charbonneau

 
anderson's picture
Offline
Joined: 01/28/2009
Sub Reports

Hi Robert,

Glad you found the answer and yes it is very confusing. To clarify further you need to place your sub-report in a group section of your main report. You then restrict your sub-report by passing the value of a group field into a parameter of your sub-report so that it only displays data relevant to that section of your main report.

As Robert mentions the configuration of parameters in the sub-report appears to be reversed. Add the INNER parameter to the Where clause of your sub-query so that it only selects data relevant to the main group.

Dave.

 
1stukgent (not verified)
1stukgent's picture
When it comes to choosing the

When it comes to choosing the right Linux Pos Software for your business it is important that you know what the advantages and disadvantages are to be had from the Linux based ones. This is because there are so many different ones that you are now able to choose from. But first let us provide you with a brief explanation of what POS software is and why it is essential if you own any kind of retail business.

 
giacomo's picture
Offline
Joined: 05/10/2010
Anderson, thank you for

Anderson, thank you for sharing this. After attempting many times to modify OpenRPT reports to show tax break down I have to ask this simple question. Can I use pentaho designer to create invoice, S/O, P/O, Quote templates and show tax breakdown? How easy it is? I could just print the report from Pentaho interface.

 
anderson's picture
Offline
Joined: 01/28/2009
Short Answer is yes

Hi Giacomo, the short answer is yes you could use Pentaho Report Designer an link directly into xTuple to generate your reports. Pentaho comes with sample reports which include Invoices.

However I wouldn't do this. OpenRpt is easy to use, built into xTuple and I believe OpenRpt would achieve what you are after easier than Pentaho. I would suggest persevering with OpenRpt or asking for help for your particular problem.