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:
- (it looks as if it was) a draft of official documentation for Mondrian 4: http://mondrian.pentaho.com/schema.html,
- unofficial* XML Schema Definition for Mondrian 4: xsd file,
- official .DTD file can be downloaded from Pentaho’s CI server: http://ci.pentaho.com/job/mondrian/
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.
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.
<Schema name="Example schema" metamodelVersion="4.0" xmlns="http://thejavatar.com"
<Cube name="Example cube" visible="true" cache="false" enabled="true">
<MeasureGroup table="FACT" name="MEASURES">
<Measure name="MEASURE" aggregator="sum" column="MEASURE"/>
<Measure name="MEASURE_2" aggregator="sum" column="MEASURE_2"/>
<ForeignKeyLink dimension="Dim" foreignKeyColumn="DIM_ID"/>
<Dimension name="Dim" table="DIMENSION" key="ID">
<Attribute name="DIM_1" keyColumn="DIM_1"/>
<Attribute name="ID" keyColumn="ID" visible="false"/>
select f.*, f2.measure_2 from FACT f INNER JOIN FACT2 f2 ON f.id = f2.fact_id
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.
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
customds.name=Mondrian 4 Test
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.
When we insert some sample data to database.
insert into dimension values(1, 'DIM')
insert into fact values(1,10,1)
insert into fact2 values(1,1,20)
The report will look like below.
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.
- Mondrian 4 schema specification
- * a blog entry about Mondrian 4 where I found the .xsd file for Mondrian 4 schema
- Using JNDI connection for Mondrian 4 in Pentaho BA Server