MDX for doing the Percent of Total in Magic Square – using Mondrian OLAP Engine

Assume we had a magic square where one can have any measure in the squares and the values are drilled down in both the Axes – Rows and Columns – by dimensions. The Magic Square also shows the row totals and column totals and at the lower right hand side we show the total of everything – which we call the cross total – see figure 3. The picture below illustrates it explicitly. In the columns we have the dimension – Patient Cosmetic, while the rows have the dimension – Toric/Multifocal/Spherical. The squares have the measure – Value.

Figure 1

Showing the above table with the Percent of Total would yield the following values.

Figure 2

Notice how the Total Column adds up to 100%, but each row does not. This is the desired behavior. If the user wants to see the percent of total for the Rows, they can transpose the chart.

The figure below shows what is meant by Horizontal and Vertical Total and Cross Total.

Figure 3.

Figure 4

MDX for generating the Percent of Total for all Squares and Cross, Vertical and Horizontal Total in Mondrian

Original MDX Query

The MDX query for generating the data values in the MagicSquare as in Figure 1 would be

SELECT

{[Dimension1.Dimension1].members} ON COLUMNS,

{[Dimension2.Dimension2]..members}  ON ROWS

FROM [Cube]

WHERE ([Measures].[Value])

Let us see how we modifiy the above MDX Query for the Percent of Total values so that we get the results as in Figure 2.

WITH   member [Measures].[POT]  as [Measures].[Value]  /  ([Measures].[Value],[Dimension2.Dimension2].CurrentMember.Parent),format_string=’00.00%’

SELECT

{[Dimension1.Dimension1].members}, {[Dimension1.Dimension1].[All Members]} ON COLUMNS,

{[Dimension2.Dimension2].members} }} ON ROWS

FROM [Cube]

WHERE ([Measures].[POT])

An important addition to this MDX Query is – the section in BOLD – , {[Dimension1.Dimension].[All Members]}

This gives the Vertical Total automatically for Percent of Total calculations.

Since Percent of Total is based on Sum/Volume – it is additive – the Horizontal Totals & Cross total is then manually calculated for each column by adding the Percent of Total value result is obtained in each column. The highlighted sections in Figure 4 below – have to be manually calculated by the Application layer before presenting the results to the end user.

Later on we found an important issue with the above MDX.

The above MDX works only when there is no Filter in the Column Segments. So in other words the above Query works if all the Dimension1 members are to be shown in the Magic Square column. If the user applies any filter on Dimension1 – for example if the user wants to show only 2 members of Dimension1 in the Column the above MDX query will NOT return correct data values for the Vertical Total – since it calculates the Vertical Totals which is based on – [All Members] – which means it takes into consideration all members of Dimension1 and NOT just the filtered ones OR the ones the end user needs.

[All Members] – is actually the default member for any Level’s Parent Level in Mondrian.

So how to Resolve this issue

The simplest way to resolve this is to do this new Query for the Total Column. Initially the thought was  a single MDX Query would be easier to formulate and execute and better to handle the results – but actually it fails when as illustrated above the Columns has Segments from the SG filter.

It has to be understood that – 1 MDX query cannot help us – to get the values of the Squares as well as the Total Column values. There has to be 2 MDX Query. The application layer  then assembles the results from the 2 MDX Queries and merges them together in a Data Structure and populates the UI accordingly.

The MDX Query to get the values in the squares is exactly as the one above – but without [All Members ] member. Assume that the user has selected to be shown on the columns only the following members of Dimension1 – { member1, member2 }.

WITH  member [Measures].[POT] as

[Measures].[Value] /

([Measures].[Value],[Dimension2.Dimension2]. CurrentMember.Parent),format_string=’00.00%’

SELECT

{[Dimension1.Dimension1].[member1]}, {[Dimension1.Dimension1].[member2]} }} ON COLUMNS,

{[Dimension2]. members} }} ON ROWS

FROM [Cube]

WHERE

([Measures].[POT])

While the MDX Query to get the Vertical Column is :

WITH  member [Measures].[POT] as [Measures].[Value] / ([Measures].[Value],[Dimension2.Dimension2].CurrentMember.Parent),format_string=’00.00%’

member [Measures].[PartialSum] as

Sum({[Dimension1]. [member1], [Dimension1].[member2]}, [Measures].[POT] )

member [Measures].[TOTAL_POT] as

[Measures].[PartialSum] / ([Measures].[PartialSum],[Dimension2.Dimension2].CurrentMember.Parent),format_string=’00.00%’

SELECT  { [Measures].[TOTAL_POT] }  ON COLUMNS,

{[Dimension2.Dimension2]. members} ON ROWS

FROM [Cube]

The MDX query above has 3 Calculated Measure defined dynamically at run time.

Let us understand each measure:

1.

WITH  member [Measures].[POT] as [Measures].[Value] / ([Measures].[Value],[Dimension2.Dimension2].CurrentMember.Parent),format_string=’00.00%’

This is the same as the Percent of Total Measure

2.

member [Measures].[PartialSum] as

Sum({[Dimension1]. [member1], [Dimension1].[member2]}, [Measures].[POT] )

This measure calculates the POT  for the 2 Segments which are filtered by – [Dimension1]. [member1], [Dimension1]. [member2]

3.

member [Measures].[TOTAL_POT] as

[Measures].[PartialSum] / ([Measures].[PartialSum],[Dimension2.Dimension2].CurrentMember.Parent),format_string=’00.00%’

The above defines the Percent of Total based on the 2nd Measure ( as defined above ) – [Measures].[PartialSum]

This Measure calculates the POT based on the Totals for the 2 Segments in the SG which are shown in the UI

So the actual MDX Query for the Total Column is

SELECT  { [Measures].[TOTAL_POT] }  ON COLUMNS,

{{{[Manufacturer.Manuf_Brand_H].[Manufacturer].members} }} ON ROWS

FROM [Prescriber Product PeriodRollup]

WHERE

([Product.Product_Package].[product_name].[ProjectedTotalLenses])

The above Query for the total Column – uses the measure – [Measures].[TOTAL_POT]

Advertisements

3 responses to “MDX for doing the Percent of Total in Magic Square – using Mondrian OLAP Engine

  1. Pingback: 7 osi layers

  2. Pingback: seo magic

  3. Thanks for this 😀

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