Every now and then the issue of computing a generic “ratio to parent” calculation comes up. There is a good overview of the problem in Darren Gosbell’s blog “MDX ratio of current parent issue”. I want to pick up the conversation where Darren left it:
It is technically possible to get "kind of" close doing something like the following using the Axis() function: <MDX fragment skipped> 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.
I am not big fan of calculation which depends on the content of axis, mostly because when result of the same expression at the same coordinates differ from query to query (and this happens when expression uses Axis function), the whole caching is destroyed, and as a result performance suffers. However, if there is a real business requirement to build calculated member like that – it is certainly possible, and in most generic way, which will work no matter how many hierarchies are in the row axis. What’s more interesting, it is even possible to do it in pure MDX, and this MSDN forum thread provides different approaches. However, none of these approaches is practical one. The practical solution is to use the following stored procedure:
public decimal RatioToParent(Set axis, Expression exp)
{
Hierarchy h = null;
// Iterate over all hierarchies in the set
int cHier = axis.Hierarchies.Count;
int iHier;
for (iHier = cHier-1; iHier >= 0; iHier--)
{
h = axis.Hierarchies[iHier];
// and find the hierarchy where the current member is not yet at the highest possible level
if (h.CurrentMember.ParentLevel.LevelNumber > 0)
break;
}
// If there were no such hierarchy found - report ratio of 100%
if (h == null || iHier < 0)
return 1;
// Since current member in this hierarchy is not yet at the highest level, we can safely call .Parent
TupleBuilder tb = new TupleBuilder(h.CurrentMember.Parent);
// and divide value at current cell by the value of its parent
return (decimal)exp.Calculate(null) / (decimal)exp.Calculate(tb.ToTuple());
}
The way to use this stored procedure in the definition of calculated member is as in the following query – we have three different hierarchies on rows, and all the ratios come back correctly
WITH
MEMBER Measures.SalesRatioToParent AS
IIF
(
IsEmpty([Measures].[Sales Amount])
,null
,ASSP.ASStoredProcs.SetOperations.RatioToParent
(
Axis(1).Item(0)
,[Measures].[Sales Amount]
)
)
,FORMAT_STRING = 'Percent'
,NON_EMPTY_BEHAVIOR = [Measures].[Sales Amount]
SELECT
{
[Measures].[Sales Amount]
,[Measures].[SalesRatioToParent]
} ON 0
,NON EMPTY
(
[Promotion].[Promotion Category].MEMBERS
,[Product].[Category].MEMBERS
,[Sales Territory].[Sales Territory].MEMBERS
) ON 1
FROM [Adventure Works];
Note the careful combination of IsEmpty([Sales Amount], NULL, …) construct and NON_EMPTY_BEHAVIOR. The condition of IIF ensures that NON_EMPTY_BEHAVOIR is set correctly in this scenario
I added RatioToParent function to my local copy of Analysis Services Stored Procedures project, I hope that it will get approved and become part of the official ASSP release.