Mondrian – an OLAP which is not Essbase 😵 6

I was planning to write about some Groovy (EPBCS and Essbase On-Premises) and thought about doing this one instead 😉

Once Kscope was done, and because of an upcoming change I was evaluating some products and found some OLAP tools other than Essbase.

These are open source technologies.

The product I’m going to talk about is called Mondrian.

Well, I’m not saying that they are superior. However, this feature (Mondrian Role Security) that I’m going to talk about is interesting.

I cannot call myself a Mondrian expert because all this was done in 2 days and I’ve just scratched the surface of the tool.

There is less information (or rather scattered information) on the internet about the setup and how it works. I guess that is one issue when you start looking at OSS technologies.

Mondrian is distributed as a jar file, yes you heard that right, not a 20 Gb installer ;).

The installation documents you’ll find is going to talk about a war file and how you can deploy it using Tomcat, well if you want to try it I would recommend downloading Pentaho BI server, and Mondrian comes along with the BI server as a prepackaged backend server. Otherwise, you’ll have to find a way to use Mondrian in your application 😉

Now that issue is sorted out, Mondrian comes (or the documents says) with a default sample schema (read Sample application) called Foodmart. I was not able to find this sample application, the sample that is shipped with Pentaho BI server is called Steel Wheels!!!

Now either my Google-fu is bad (or it was good) I was able to find a link which helped me get the required data for Foodmart for Oracle XE, yeah back to you Oracle 🙂

I didn’t want to try an Oracle database as Pentaho instance was already using HyperSql as database and I’m testing all OSS, so I had to now prepare the data for HyperSql database, and some hours of work I got the data required for Foodmart database.

Now comes the hard/easy part 🙁 Creating cubes

Mondrian is an ROLAP (XOLAP), this is one reason why I was testing this product. For those who don’t know about ROLAP, it is Relational OLAP and Mondrian allows you to fire MDX queries against its cube and converts that MDX into SQL and presents the required data in a multidimensional way.

I don’t know how many of you have used the XOLAP feature in Essbase? That is XOLAP (aka ROLAP)

The way you build cubes in Mondrian is by editing an XML file!!!! Wohooo (this is something I was always looking for in Essbase, maybe not a feature for everyone though 😉 )

Below given is a sample of Steel Wheels Schema, and you can define cubes and virtual cubes there.

For those who are looking for a UI component, there is a UI editor available called Schema Workbench. Here is the screen shot of SteelWheels schema in PSW (Pentaho Schema Workbench).

The hard part was to make Pentaho BI server understand that there is a new Mondrian cube(s) that it can use. There are a couple of blog posts that talk about editing olap4j.properties file and I couldn’t make it work that way.

I tricked it!!!

There are two ways you can create a cube in Mondrian

1. You can add a data source in BI server
2. You can create your schema and then make BI server see that schema (for me this was not working)

So I went with the first route and added a data source in BI server and here is how you can do it.

Manage Data source -> New Data Source

Name your Source and Select the Type

Choose “Reporting and Analysis.”

Click on the “+” to add the database if you don’t have the database listed.

Mine is a HyperSQL database.

Once the database is added, go back to the data source wizard and pick your tables. You’ve to tell the wizard which is the fact table (this will be later used to create joins, spoiler alert 😉 )

Define the joins and that’s it, a new data source is created. You’ll get the below-given screen once wizard finishes the data source creation.

I chose to customize the model so that I can show you that there is an option to edit the cube from BI server too.

Now that there is a schema Mondrian recognizes what I did was to use the UI editor aka Schema Workbench to create Schemas.

I took the sample schema published on a site and made some changes to it.

All I did after that was to Publish my schema to Pentaho BI server.

There you go, the schema was published, and now I can start analyzing data in BI server.

Let’s login as an admin and look at a report

Think about Catalog as Application and Cube as cube

I can toggle between Non-Empty and it’ll show the MDX and the results, so far this is pretty much similar to Essbase.

Now let’s login as a user Pat.

He only sees one cube (filter, same concept like Essbase)

Similar to our own Essbase security. However, it is defined through XML and here is how it is defined.

 <Role name="California Manager">
 <SchemaGrant access="none">
 <CubeGrant cube="Sales" access="all">
 <HierarchyGrant hierarchy="[Store]" topLevel="[Store].[Store Country]" rollupPolicy="full" access="custom">
 <MemberGrant member="[Store].[USA].[CA]" access="all">
 </MemberGrant>
 <MemberGrant member="[Store].[USA].[CA].[Los Angeles]" access="none">
 </MemberGrant>
 </HierarchyGrant>
 <HierarchyGrant hierarchy="[Customers]" topLevel="[Customers].[State Province]" bottomLevel="[Customers].[City]" rollupPolicy="full" access="custom">
 <MemberGrant member="[Customers].[USA].[CA]" access="all">
 </MemberGrant>
 <MemberGrant member="[Customers].[USA].[CA].[Los Angeles]" access="none">
 </MemberGrant>
 </HierarchyGrant>
 </CubeGrant>
 </SchemaGrant>
 </Role>

All that piece of code is saying is this

1. Give access to 1 cube Sales
2. On Store Hierarchy, the role can only start seeing from the level Country (yes you can filter by a top level, and you can also say stop at this level using a bottom level)
3. rollupPolicy is nothing but no matter whether the user has access to children of a parent show the sum of all children
4. show Idescendants of CA, however, don’t show Los Angeles
5. Similar way Customer dimension is also restricted

Now comes the fun part 🙂

Let’s login as South Pacific Manager Suzy and see what data access she has for the same report.

For the same report, she can see all the three children, however, now the total is different. The total is now the sum of all children that she can see. How is that done? I just changed the rollup policy to partial.

 <Role name="South Pacific Manager">
 <SchemaGrant access="none">
 <CubeGrant cube="Sales" access="all">
 <HierarchyGrant hierarchy="[Store]" topLevel="[Store].[Store Country]" rollupPolicy="partial" access="custom">
 <MemberGrant member="[Store].[USA].[CA]" access="all">
 </MemberGrant>
 <MemberGrant member="[Store].[USA].[CA].[Los Angeles]" access="none">
 </MemberGrant>
 </HierarchyGrant>
 <HierarchyGrant hierarchy="[Customers]" topLevel="[Customers].[State Province]" bottomLevel="[Customers].[City]" rollupPolicy="partial" access="custom">
 <MemberGrant member="[Customers].[USA].[CA]" access="all">
 </MemberGrant>
 <MemberGrant member="[Customers].[USA].[CA].[Los Angeles]" access="none">
 </MemberGrant>
 </HierarchyGrant>
 </CubeGrant>
 </SchemaGrant>
 </Role>

There is one more type of rollup policy you can setup (any guesses 😉 ) hidden

Let’s see that one too

 <Role name="Beverly Hills Manager">
 <SchemaGrant access="none">
 <CubeGrant cube="Sales" access="all">
 <HierarchyGrant hierarchy="[Store]" topLevel="[Store].[Store Country]" rollupPolicy="hidden" access="custom">
 <MemberGrant member="[Store].[USA].[CA].[Beverly Hills]" access="all">
 </MemberGrant>
 </HierarchyGrant>
 <HierarchyGrant hierarchy="[Customers]" topLevel="[Customers].[State Province]" bottomLevel="[Customers].[City]" rollupPolicy="partial" access="custom">
 <MemberGrant member="[Customers].[USA].[CA].[Beverly Hills]" access="all">
 </MemberGrant>
 </HierarchyGrant>
 </CubeGrant>
 </SchemaGrant>
 </Role>

When is this going to come to Essbase, oh please please get this done Oracle.