Adventure Works SQL DW on Pentaho OLAP, part 2

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”

aw3

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.

aw10

 

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]))”.

aw11

 

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:

aw12

 

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.

 

Advertisements

3 responses to “Adventure Works SQL DW on Pentaho OLAP, part 2

  1. Pingback: Getting Started with Mondrian For SSAS Developers | Big Data Analytics

  2. Pingback: Migrate Adventure Works Sales Cubes from SSAS MOLAP to Mondrian ROLAP | Blog Home for MSSQLDUDE

  3. Pingback: AdventureWorks DW on Pentaho, part 3: Geo Mapping AdventureWorks | Blog Home for MSSQLDUDE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s