| Excerpt |
|---|
October 24, 2006 |
This technical article was generously contributed by Chris Webb, an OLAP guru and independent consultant (check him out at Crossjoin Consulting, or on his blog). Chris also has written a book on MDX called 'MDX Solutions', that you can get here once you are looking to go deep with Mondrian and MDX!
In this series I'll be showing you how to implement some common MDX calculations using the SteelWheelsSales sample cube. In many cases you will be able to take these calculations and, after changing some of the measure and dimension names, be able to re-use them in your own cubes; hopefully you'll also be able to pick up a bit of the theory on the way and be able to tackle more specific problems yourselves. Nicholas Goodman has already given excellent explanations on how to edit the MDX query used by JPivot to display data (http://www.nicholasgoodman.com/bt/blog/2006/06/08/mondrian-power-of-olapmdx-at-your-fingertips/) and how to add calculated members to a Mondrian cube (http://www.nicholasgoodman.com/bt/blog/2006/06/09/mondrian-olap-power-at-your-users-fingertips/) so I won't repeat what he has to say.
Resources Before You Get Started
To run the queries that Chris presents in this technical article, you will need JPivot, hooked up to the SteelWheelsSales sample cube. You can get both of these pre-configured and ready to use out of the box in the Pentaho Pre-configured Installation (PCI). Just navigate to the Steel Wheels Analysis Samples, and select the MDX Query button from the JPivot toolbar.
- Pentaho Pre-configured Installation (PCI) running locally, and the hypersonic database running locally with the SampleData database, version 1.2 or later.
Overview
Let's imagine that you need to see one of the commonest types of Business Intelligence calculation in your reports: previous period growth. Let's say you've got sales for several months in your cube and you want to be able to calculate, for each month, the percentage or absolute growths from the previous period. To be more specific, what you want to be able to do is to take a measure value and create a new calculated member in the cube which will display either the absolute or percentage difference between the value displayed for that measure for the current time period and the previous time period. The previous time period could be a Year if we were looking at the Year level in our Time dimension, it could be a Quarter if we were at the Quarter level or it could be a Month if we were at the Month level. What's important is that we somehow need to get a reference to a member relative to the member we're currently looking at on the Time dimension.
...