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: ,

Feedback

# re: MDX ratio of "current parent" issue

Pingback.
Link to this post was added to our website in the [Articles]/[MDX] section 7/27/2008 2:32 AM | SSAS-Info.com

# re: MDX ratio of "current parent" issue

And if you have a specific ratio at each level? And you cannot agregate it?
Regards,
Pedro 12/31/2010 5:28 AM | PedroCGD

# re: MDX ratio of "current parent" issue

@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. 1/9/2011 4:29 PM | Darren Gosbell

# re: MDX ratio of "current parent" issue

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 2/22/2014 12:15 AM | Marcos

# re: MDX ratio of "current parent" issue

@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. 2/22/2014 10:03 AM | Darren Gosbell

# re: MDX ratio of "current parent" issue

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 2/24/2014 11:38 PM | Marcos

Post a comment





 

 

News

About Me
I am a consultant, based in Melbourne Australia. I primarily work in the Business Intelligence area with SQL Server, although I also dabble in Content Management Server and .Net coding

Contact Me

MVP


Legal
Any and all code, software, examples, suggestions and anything else on this web site is available for you to use at your own risk. No warranty is expressed or implied.
Views and Opinions
The views and opinions expressed on this web site are not necessarily the views or opinions of my employer.

Subscribe in Bloglines Subscribe in NewsGator Online

Locations of visitors to this page

Twitter












Tag Cloud


Article Categories

Archives

Post Categories

.Net Blogs

Aussie Bloggers

BI Blogs

CMS Blogs

Syndication: