MDX in Mondrian – for Percent of Total calculation for – Table Charts

What are we trying to do?  Assume we have a Table Chart – which lists some measures which are grouped by members of Dimension1. See below.

BreakoutByDimension1 Measure1 Measure2
Segment1 9,722,863 9,432,877
Segment2 184,569,451 187,198,313
Segment3 26,471,424 24,360,739
Segment4 348,214 313,446
Segment5 830,859 889,048
Total 221,942,812 222,194,423

We would like to get the Percent of Total for Each measure – rather than actual measure values.

A B
Dimension1 POT Measure1 POT Measure2
Segment1 4.38% 4.25% 1
Segment2 83.16% 84.25% 2
Segment3 11.93% 10.96% 3
Segment4 0.16% 0.14% 4
Segment5 0.37% 0.40% 5
Total 100.00% 100.00% 6

MDX Query

with member [Measures].[POT Measure1] as

‘[Measures].[Measure1]

/

([Measures].[Measure1], [Dimension1.Dimension1].[Level1].CurrentMember.Parent)’,

format_string=’00.00000%’

member [Measures].[POT Measure2] as

‘[Measures].[Measure2]

/

([Measures].[Measure2], [Dimension1.Dimension1].[Level1].CurrentMember.Parent)’,

format_string=’00.00000%’

SELECT {[Measures].[POT Measure1], [Measures].[POT Measure2]} ON COLUMNS ,

{[Dimension1.Dimension1].[Level1].members} ON ROWS

FROM [Cube]

The highlighted line shows the Breakout by Dimension and the Mondrian Calculated Measure – [Measures].[ POT Measure1] – is part of the MDX Query that is generated at Run time. This has to be run time – because of actually 2 reasons

  1. Look at the denominator of the [Measures].[ POT Measure1] – it is – ([Measures].[Measure1], [Dimension1.Dimension1].[Level1].CurrentMember.Parent)’

The above denominator – is what gives the Total value for the Measure – Measure1 for the Parent Member of Dimension1.

Now the above Denominator is only known at run time since it depends on what the breakout by Dimension the user has selected in the AdHoc Query tool.

  1. All the above changes related to forming the modified MDX would not have been necessary had Mondrian supported the Axis(). If that were the case then the POT Measure1 – would be a Calculated Measure as below in the Mondrian Schema

<CalculatedMember name=” POT Measure1″ caption=” POT Measure1″ dimension=”Measures” formula=”[Measures].[Measure1]

/ ([Measures].[Measure1], Axis(1).Item(0).CurrentMember.Parent)”>

</CalculatedMember>

Where – the highlighted section above – would be automatically understood by Mondrian as the breakout by Dimension.

Slight Changes to the above MDX Query for POT

All the above MDX – is fine – when the breakout by dimension is a flat dimension or the end user wants a POT comparison at the same level. However, if the end user wants to get the POT Values at the global level – For example Look at multiple breakouts by :

Dim1-Level1 Dim1-Level2 SUM POT ( from 1st MDX ) Actual POT at Global Level

( Desirable Answer )

Manuf1 Brand1 20 20/(20+30) = 40% 20/100 = 20%
Manuf1 Brand2 30 30/(20+30) = 60% 30/100 = 30%
Manuf2 Brand21 15 15/(15+25+10) = 30% 15/100 = 15%
Manuf2 Brand22 25 25/(15+25+10) = 50% 25/100 = 25%
Manuf2 Brand23 10 10/(15+25+10) =10% 10/100=10%

What happens is that the MDX Query as it was before – for a Hierarchical case – would calculate the POT ( intra Level ) and not global across all the Segments in all levels.

So I had to change the MDX Query to this

WITH member [Measures].[POT Measure1] as [Measures].[Measure1] / ([Measures].[Measure1],[Dim1.Dim1].[All Members]),format_string=’00.00%’
SELECT { [Measures].[POT Measure1]} ON COLUMNS ,
{[Dim1.Dim1].[Level1].Children} }} ON ROWS
FROM [Cube]

Now we are computing the POT at the Global Level since we have added
([Measures].[Measure1],[Dim1.Dim1].[All Members])

as the Denominator – where [All Members] means the Root Level ( Global Level )

Advertisements

One response to “MDX in Mondrian – for Percent of Total calculation for – Table Charts

  1. Sumit, it necessary to notice that Axis function is not available in Mondrian.. http://jira.pentaho.com/browse/MONDRIAN-487 – it’s request to add that feature and this http://lists.pentaho.org/pipermail/mondrian/2011-May/003279.html – creators of mondrian reply about that feature

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