In Part 1, I built a very simple rudimentary OLAP model for reporting in Pentaho’s Business Analytics suite by pointing the Web-based auto modeling tool to my SQL Server Adventure Works data warehouse.
In part 2, I am going to show you how to use the OLAP Thin Modeler and Analyzer tool to modify that base auto-generated model and to create custom calculations from Adventure Works.
I’m going to start in the Pentaho User Console and I’m going to edit the model that was generated for AdventureWorks in Part 1 by selecting “Manage Existing”
In the subsequent Thin Model Editor screens, you will be able to do a number of operations on the cube model that you may be familiar with if you are developed SSAS cubes in the past and had to modify data source views, dimensions, measures, calculations, etc. What I am going to do is to take out some of the fields that will just cause confusion and noise for the end Analyzer user and to make sure that I have the proper formatting and properties set on the most important and common reporting fields in the model.
You may notice that the default auto-generated OLAP model contains Dimension Levels that may have been created with assumptions that don’t fit your business needs. This is where you will go in there to clean those up, modify hierarchies, etc. In my case, I renamed CalendarYear to just “Calendar” and dragged in Year, Quarter, Semester to build a hierarchy to aid users in navigating the data.
Now when a user builds Analyzer reports from my model, they will have a much cleaner logical model to work from. Make sure that you also use that last step to clean up names and make the entity names user-friendly with business entity desciptors, not database column names!
Next, create an Analyzer report from this new AdventureWorks cube model. I’ve dragged over UnitPrice as m measure into the pivot table and I’m going to slice the data my Model Name. But I also want to see the total per member of the total amount, which is Unit Price + Tax Amount. To do this, i made a custom measure from Analyzer with a right-click on the table header and enter this simple formula into the edit box: [UnitPrice] + [TaxAmt]”. Any valid MDX statement will work here to make calculated measures, i.e. ([Measures].[Unit Sales], Ancestor([Product].CurrentMember, [Product].[Product Family]))”.
And here’s what that looks like as a chart with a bar for the UnitPrice from the AventureWorks DW and my new OLAP Calculated Measure for Total Price:
Next up in the Part 3 finale of the series: We’ll take that cube model outside of the Analyzer / Auto Modeler realm and build it out even further and test it with our own MDX queries directly.