Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

A simple MDX ratio problem

Posted by thomasivarssonmalmo on January 18, 2009

"Ratio to parent" in MDX is a frequent discussion in the SSAS newsgroup and on the blogs. Mosha’s and Darren Gosbell’s thoughts are linked here and involves the MDX Axis function. This blog entry is about a less advanced problem in the "ratio to parent scenario". It is about how you can get a total percentage distribution of members from two crossjoined dimensions on rows in a MDX select statement. I think it can be help if your boss demands a quick solution and do not accept the default behaviour in a SSAS client.
 
The default behaviour can be seen if you run this MDX in Management Studio.
 

Select {[Measures].[Internet Sales Amount],[Measures].[Internet Ratio to All Products]} On Columns,
NON EMPTY CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])) On Rows
FROM [Adventure Works]
WHERE ([Date].[Calendar Year].&[2004]); 

The Internet Ratio to all products is defined like this in the Adventure Works cube.

[Measures].[Internet Sales Amount]
    /
    (
      Root( [Product] ),
      [Measures].[Internet Sales Amount] )

The result will look like this:

 

For each country you will get the percentage distribution of [Internet Sales Amount] in the product categories for each country.  A client will accept the dimension you have crossjoined and calculate the percentages according to this.

And now to the simple problem that might not have an obvious simple solution. If I would like to to see the percentage distribution for all the countries and the product categories as a ratio to total.  What was the percentage contribution of [Internet Sales Amount] for Bikes in Australia to the total sales for all markets? This analysis is an issue for the SSAS clients that I know about.

My solution is to build a calculated measure that references All Products and All Customers members like this.

WITH Member Measures.AllInternetSales2004 As
([Measures].[Internet Sales Amount],[Date].[Calendar Year].&[2004],[Customer].[Customer Geography].[All Customers]
,[Product].[Product Categories].[All Products])

Member Measures.RatioInternetSales2004AllGeo As
([Measures].[Internet Sales Amount]/Measures.AllInternetSales2004),Format_String = "#0.0%"

Select {[Measures].[Internet Sales Amount],[Measures].[Internet Ratio to All Products],
Measures.RatioInternetSales2004AllGeo} On Columns,
NON EMPTY CrossJoin(Descendants([Customer].[Customer Geography],[Customer].[Customer Geography].[Country]),
Descendants([Product].[Product Categories],[Product].[Product Categories].[Category])) On Rows
FROM [Adventure Works]
WHERE ([Date].[Calendar Year].&[2004]);

The result will look like this.

 

In order to solve this simple ratio to parent problem you will not need to use the MDX Axis()-function but build a calculated measure with the two top members for each dimension on rows. The All-member is useful in other scenarios as well and I will return to that later.  

The problem with my approach is that you will have to make this ratio for each measure that the ratio applies to. If this takes less or more time than learning about the Axis function is up to you to decide.

Edit: The pictures are updated. To validate the percentages you copy the resultset in Management Studio with a right click in the upper left corner. Paste it in to Excel but you might have to change the decimal sign from (.) to (,). I have only one decimal so you will see a minor gap. Add decimals in the format_string property of the calculated measure. 

Advertisements

4 Responses to “A simple MDX ratio problem”

  1. Miky Schreiber said

    I wasn’t familiar with Mosha’s post and I was surprised to see that this trivial solution wasn’t suggested by him.By the way, please put screen-shots with higher resolution next time. No one can see and read what’s on the picture.

  2. Thomas said

    Hi Mikey. i hope that the pictures are better now. I think that Mosha discusses a more complex case of ratio to parent than mine.

  3. Radim said

    Hej Thomas, unfortunatelly it’s still very difficult to pull anything valuable out of the image, it’s too small or rather low-res.

  4. Thomas said

    I will see what I can do tomorrow. I think it is an issue with live spaces. I uploaded the pictures in the same way as in previous post but this time it does not work. I hope that you can run the examples and see that the percentages in the second example works.

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

 
%d bloggers like this: