Page Comparison

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!

...

Take some time to drill up and down through the Time dimension and you'll see that it consists of four levels: an All level, a Year level, a Quarter level and a Month level, in that order. This means that each Year consists of four Quarters and each Quarter consists of three Months; all the members on the Time dimension can be thought of being arranged in a tree structure with the single member on the All level, the All Member, at the top and the Months right at the bottom, and indeed you can see it displayed as such if you open the OLAP Navigator:

// //

If we want to find out which member is above any given member in a query, or which members are immediately below any given member, we can use the PARENT and CHILDREN functions. So, for example, the following query shows how the PARENT function works:

select {[Measures].[Sales]}
ON COLUMNS,
{[Time].[All Years].[2003].Parent}
ON ROWS
from [SteelWheelsSales]

// //

The member immediately above the Year 2003, its 'parent', is the All Member. Similarly, the following query shows that when you apply the PARENT function to the first Quarter in 2003 you get the Year 2003:

select
{[Measures].[Sales]} ON COLUMNS,
{[Time].[All Years].[2003].[QTR1].Parent} ON ROWS
from [SteelWheelsSales]

Image Added

The CHILDREN function allows you to return the set of members immediately below any given member. For example, the following query returns all the Quarters in 2003:

select {[Measures].[Sales]}
ON COLUMNS,
{[Time].[All Years].[2003].Children}
ON ROWS
from [SteelWheelsSales]

// Image Removed //Image Added

We don't need to restrict ourselves to looking up or down one level in the hierarchy, either. The ANCESTOR function allows us to look up more than one level, for example:

...

select {[Measures].[Sales]}
ON COLUMNS,
{Descendants([Time].[All Years].[2003], [Time].[Months])}
ON ROWS from [SteelWheelsSales]

// Image Removed //Image Added

There are a number of flags which can be supplied in the third parameter to DESCENDANTS about which I won't go into in detail (I'm afraid you'll have to look them up! See http://mondrian.sourceforge.net/api/mondrian/olap/fun/DescendantsFunDef.Flags.html for a complete list), but here's an example using the SELF_AND_AFTER flag:

select
{[Measures].[Sales]}
ON COLUMNS,
{Descendants([Time].[All Years].[2003], [Time].[Quarters], SELF_AND_AFTER)}
ON ROWS from [SteelWheelsSales]

// Image Removed //Image Added

This, as you can see, returns all of the descendants of the Year 2003 at the Quarters level and all levels below the Quarters level.

...

with member [Measures].[ShareToParent] as '[Measures].[Sales]/([Measures].[Sales], [Time].CurrentMember.Parent)',
format_string='0.00%'
select
{[Measures].[Sales],[Measures].[ShareToParent]}
ON COLUMNS, Time.Members ON ROWS
from [SteelWheelsSales]

// Image Removed //Image Added

Rather than find the percentage compared to the current member's parent, we might want to calculate the percentage based on the ancestor at a given level ' for example find the percentage of a Quarter or Month value relative to the Year it's in. We can use the ANCESTOR function to do this:

with member [Measures].[ShareToYear] as
'[Measures].[Sales]/([Measures].[Sales],
Ancestor([Time].CurrentMember, [Time].[Years]))',
format_string='0.00%' select
{[Measures].[Sales],[Measures].[ShareToYear]}
ON COLUMNS,
Time.Members ON ROWS from [SteelWheelsSales]

// Image Removed //Image Added

These calculated measures are fairly straightforward and not too different to the time series calculations we created in the previous article in this series. Let's now consider, though, the problem of allocating a value down a hierarchy: imagine that in 2003 the CEO of Steel Wheels had decided that the target sales for the year 2004 would be $5,000,000. On this basis, what should the target sales values for each Quarter and Month in 2004 be in that case? If we were to calculate this as an unweighted allocation so that each Quarter and Month would have the same value, then when we were looking at a Quarter show the value of $5,000,000 divided by the number of Quarters in 2004, and if we were looking at a Month show the value of $5,000,000 divided by the number of Months in 2004. We know how to find the set of descendants of a given member at a particular level with the DESCENDANTS function; combining this with the CURRENTMEMBER function and the LEVEL function, to return the level of the current member on Time, we can use the following expression to find the set of members on the same level as the current member on Time beneath the Year 2004:

...

with member [Measures].[2004 Sales Targets] as '
iif(Ancestor([Time].Currentmember, [Time].[Years]) is
[Time].[All Years].[2004], 5000000/
Count(
Descendants(
[Time].[All Years].[2004],
[Time].Currentmember.Level
) ) , 0) '
select
{[Measures].[Sales], [Measures].[2004 Sales Targets]}
ON COLUMNS, [Time].Members ON ROWS
from [SteelWheelsSales]

// Image Removed //Image Added

Thanks to Chris Webb for submitting this article!