MDX ratio of "current parent" issue

Every now and then on the SSAS MSDN forum, the issue of doing a generic "ratio to parent" calculation comes up. Unfortunately there are a number of problems with the premise of giving users a generic "ratio to parent" measure.

The first is that the concept of "parent", by definition, requires a hierarchy and SSAS 2005 supports multiple hierarchies.

Consider this query:

SELECT
  Non Empty [Product].[Product].[Product].Members on Rows,
  {Measures.[Sales Ratio]} ON Columns
FROM [Adventure Works]

I am using the [Product] attribute, which belongs to two user defined hierarchies.

image image

So what does the user want to see? In the context of the Adventure Works DW database, they probably expect to see a percentage of the Product sales for either the Safety Stock Level or Subcategory. However each attribute is also a single level hierarchy it would also be valid to return the ratio of each Product to [Product].[Product].[All Products].

But the fun does not stop there. What about this next query:

SELECT
  [Date].[Calendar].[Month].Members on Rows,
  {Measures.[Sales Ratio]} ON Columns
FROM [Adventure Works]
WHERE [Product].[SubCategory].[Mountain Bikes];

Which "parent" do you think the user wants to see? We know which date hierarchy to use as I have specified the [Calendar] hierarchy, but we are also slicing by the [Mountain Bike] Subcategory, so giving the ratio based on the Bikes category would be another valid interpretation.

And as you can probably guess we can keep getting more and more complicated. A relatively simple query like the following has a dimension on each axis and a slicing member, so now we have 3 possible "parents" that we could choose from...

SELECT
  [Date].[Calendar].[Month].Members on Rows,
  [Promotion].[Promotion Category].[Promotion Category] ON Columns
FROM [Adventure Works]
WHERE (
 [Measures].[Sales Ratio]
,[Product].[SubCategory].[SubCategory].[Mountain Bikes]
);

The problem is that within the multi-dimensional space of the cube there are many parents and MDX really needs you to tell it which parent you are talking about. Even with relatively simple queries you can have multiple dimensions on a single axis and hence multiple potential parents.

It is technically possible to get "kind of" close doing something like the following using the Axis() function: 

CREATE
 
MEMBER CURRENTCUBE.[MEASURES].[Sales Ratio] AS

   
IIF

   
(
       
(
          [Measures]
.[Sales Amount]
        
,Axis(1).Item(0).Item(

         
Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent
       
)
     
= 0
    
,null
    
,
        [Measures]
.[Sales Amount]
     
/

       
(

          [Measures]
.[Sales Amount]
        
,Axis(1).Item(0).Item(

         
Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent
       
)
     
   
)
  
,FORMAT_STRING = "Percent"

Which sort of gives us a  "Percent of Row parent" calculation and this is probably the best you can do, but if you crossjoin multiple hierarchies on the row axis we are in trouble again.

The whole idea of "current parent" is really much more difficult than it first appears. In general anything that requires a calculation to have an awareness of the current context of a query should be avoided.

Given all the above it is preferable to look at building specific ratio calculations or pushing this sort of task onto the client. Some client tools will let the user define calculations or if you use Excel you could create these ratios off to one side of the pivot table.

Technorati Tags: ,

Print | posted on Saturday, July 26, 2008 10:18 AM

Comments on this post

# re: MDX ratio of "current parent" issue

Requesting Gravatar...
Pingback.
Link to this post was added to our website in the [Articles]/[MDX] section
Left by SSAS-Info.com on Jul 27, 2008 2:32 AM

# re: MDX ratio of "current parent" issue

Requesting Gravatar...
And if you have a specific ratio at each level? And you cannot agregate it?
Regards,
Pedro
Left by PedroCGD on Dec 31, 2010 5:28 AM

# re: MDX ratio of "current parent" issue

Requesting Gravatar...
@Pedro then you can't use this generic approach. You would have to either use a case statement testing the level of the currentmember if the expression is query scoped or use scoped assignments if it is a cube scoped calculation.
Left by Darren Gosbell on Jan 09, 2011 4:29 PM

# re: MDX ratio of "current parent" issue

Requesting Gravatar...
Hi Darren,
I scoped the calculation in my cube. The ratio only show values when another measure is picked together.
would be possible to make it work without the necessity of picking this second measure?
regards,
M
Left by Marcos on Feb 22, 2014 12:15 AM

# re: MDX ratio of "current parent" issue

Requesting Gravatar...
@Marcos - can you post a calc / query against adventure works which shows what you are trying to do? I don't really understand what your issue is.
Left by Darren Gosbell on Feb 22, 2014 10:03 AM

# re: MDX ratio of "current parent" issue

Requesting Gravatar...
Hi Darren,

You can see what I'm meaning using Excel to query the cube.
Choosing only the ratio an error is shown, something like "can't establish the slicer axis reference", when you pick a second measure apart of the ratio, the ratio value is calculated perfectly.

Regards,
Marcos
Left by Marcos on Feb 24, 2014 11:38 PM

Your comment:

 (will show your gravatar)