Accessing Mondrian cubes through Pentaho Report Designer

Today I had the opportunity to design some complex reports using the cubes published in my customer's Pentaho BI Server as datasources for my report. Using a cube as a datasource to produce reports is good, in my opinion, because it gives you a perfect way to make easy reporting whenever you've, for example, to produce reports that compares data on different periods.

A good sample for what we're going to discuss here is the Top N Analysis report you can find in the Pentaho's Steel Wheels samples. For abbreviation I'll refer to this report simply as "the sample". If you open it and have a look at the defined datasources you can clearly see that it takes the data it needs from steelwheels.mondrian.xml cube schema. So that is good for us.

Publish the report to Pentaho BI Server

Before thinking about the publishing of your report to your bi-server running instance, you've to think about the way Pentaho will use to access the schema it needs for your report. The strategy used by the reporting plugin goes through two possible path:

• Firstly it tries to access the Analysis Schema file using the path you've specified in the datasource definition of the Pentaho Report Designer. Whenever you're in the Pentaho BI  Server execution environment every path is calculated respect to <BISERVER_HOME>/tomcat/bin. That means that if you set a reference to your Analysis Schema cube as a relative path in your report datasource definition (as is for the sample I mentioned in my opening) Pentaho will look for you schema file calculating the absolute file path respect to <BISERVER_HOME>/tomcat/bin. So you need to be sure that your file is in the right place before the system will try to access it. I think that this way is not as good because is dependent on your BI Server filesystem layout.
• Secondly it tries to access the schema as an XMLA datasource. That is, in my opinion, the more elegant way to make the schema available to the reporting engine.

How to add a new XMLA datasource to Pentaho BI Server

To define a new XMLA datasource in our Pentaho BI Server environment we've to update the datasources.xml file in <BISERVER_HOME>/pentaho-solution/system/olap.

This file contains the definitions of all the XMLA datasources available in the system. We can add a new datasource definition using one of these two ways:

1. Manually add a new Catalog element to configure a new Mondrian catalog
2. While publishing the Analysis Schema from the Schema Workbench flag the Enable JNDI datasource and set the JNDI Data Source field appropriately. You can find the procedure to publish the schema clearly explained in Pentaho's wiki.