Mondrian 4 in Pentaho BA Server

Prerequisites

Should you be working with Pentaho CE (Community Edition) you will first have to enable the support of Mondrian 4 in your BI Server. As I am working with EE (Enterprise Edition, version 5.2) I will not be able to help you on this subject. However, the following article might be of some help: Mondrian 4, OSGi In Pentaho 5.1 CE. Once you have fullfilled all prerequisites or if you are using Enterprise Edition you are good to go.

Creating a Mondrian 4 schema

This is a bit tricky part as for the time being there is no official documentation for Mondrian 4 (at least that’s what their support says). Nevertheless, you will be able to find some examples online which should give you some general idea about the new schema. And I tell you, it changed a lot since version 3! There are few sources that I find particuraly useful on that matter:

Joining two physical tables into one fact table

Why we even started looking into Mondrian 4 in our project is that it provides some additional functionality with respect to Mondrian 3. One of the things we missed particularly in the previous version of the schema was the possibility of joining two physical tables into one fact table. You could do that for dimensions but it was not possible for fact table. This can be achieved without any problem with Mondrian 4.

Let’s say we have a database schema like the one presented below. With two tables that should be treated as one logical fact table and a dimension table.

Database schema

Now what we will do is we will create a Mondrian 4 schema, that will have one fact table and one dimension table. However, as you can guess the fact table will consist of values coming from two physical tables. Let’s have a look at the schema now.

As you can see I have used a very simple way to achieve that. Using the SQL tag tells Mondrian the exact query that should be used when it needs to retrieve data for the fact table. Right now I cannot say much about the performance of this solution or neither whether this problem can be resolved using some more sophisticated Mondrian 4 techniques.

Uploading schema to server

Now that you have a Mondrian 4 schema file you need to upload it to the server. Unfortunately right now you cannot do that as you would normally do with Mondrian 3 schema while creating a new datasource. Nevertheless, you can still upload the file through the GUI in the BI server using a File Browser. In my case I have uploaded file Mondrian4Schema.xml directly the to /public folder.

Adding Mondrian 4 schema

Setting up a datasource for Mondrian 4

Now the schema file is on the server we can create a datasource for reports. To do that we need to edit olap4j.properties file, located under ../pentaho/server/biserver-ee/pentaho-solutions/system

The most crucial is the third line of the configuration, which I hope is pretty much self-explanatory. Basically you should only change location of your schema after Catalog and database settings after Jdbc, JdbcUser and JdbcPassword and leave the rest in this line as it is.

Creating a report with Mondrian 4

Now when you create a new Analysis report, you should be able to see Mondrian 4 Test during datasource selection next to standard datasources.

ds-select

When we insert some sample data to database.

The report will look like below.

report

Enabling logs

You might be suprised not to see logs for queries run against Mondrian 4 in their usual location. To enable them you need to edit log4j.xml file, located in ../pentaho/server/biserver-ee/pentaho-solutions/system/osgi, the necessary configuration should be already there and just requires uncommenting.

External links

One thought on “Mondrian 4 in Pentaho BA Server”

Please join discussion