Percent Contribution for ALL Hierarchies

I ran across an interesting question from the MSDN forum which challenged my MDX ability making it worth sharing. The question posed on the forum was, "The user requirement is ability to get cross tabulations among the dimensions. For example, they may want the proportion of cases in each year at a given location or the proportion of cases in each year by product? In this case, I'd need a generalized contribution precentage formula because I'll never be sure which dimension(s) users may cross-tab."

This type of "generic" contribution precentage calculation is useful for reports where a users experience is "guided", however, I'd caution against using this type of percentage calculation for ad-hoc users because they'll likely end up with slices of the data which affect percentages displayed not realizing all the slices in effect.

The tricks to this calculated member are: (1) Avoid division by zero error, and (2) Use the .DefaultMember when the .CurrentMember is the highest member in the hierarchy.

Trick (1) is taken care of in the IIF() statement which returns NULL if ANY (.CurrentMember, Measure) tuple returns empty. Trick (2) is handled in the denominator of the percentage calculation by detecting the .CurrentMember level and using DefaultMember when appropriate.

WITH MEMBER Measures.[Percent Customer Count] AS
'
IIF(ISEMPTY([Measures].[Customer Count]), NULL,
IIF([Product].[Product Categories].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Product].[Product Categories].CurrentMember.Parent, Measures.[Customer Count])), NULL,
IIF([Date].[Calendar].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Date].[Calendar].CurrentMember.Parent, Measures.[Customer Count])), NULL,
IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal <> 0 AND
ISEMPTY(([Customer].[Customer Geography].CurrentMember.Parent, Measures.[Customer Count])), NULL,
// Do the calculation
([Product].[Product Categories].CurrentMember
,[Date].[Calendar].CurrentMember
,[Customer].[Customer Geography].CurrentMember
,[Measures].[Customer Count])
/
(IIF([Product].[Product Categories].CurrentMember.Level.Ordinal = 0, [Product].[Product Categories].DefaultMember, [Product].[Product Categories].CurrentMember.Parent)
,IIF([Date].[Calendar].CurrentMember.Level.Ordinal = 0, [Date].[Calendar].DefaultMember, [Date].[Calendar].CurrentMember.Parent)
,IIF([Customer].[Customer Geography].CurrentMember.Level.Ordinal = 0, [Customer].[Customer Geography].DefaultMember, [Customer].[Customer Geography].CurrentMember.Parent)
,[Measures].[Customer Count])
)
)
)
)
' ,SOLVE_ORDER=10, FORMAT_STRING="#,0.0%"


SELECT
{[Measures].[Customer Count], Measures.[Percent Customer Count]} ON COLUMNS
,{[Product].[Product Categories].[All]
,DESCENDANTS([Product].[Product Categories].[All], [Product].[Product Categories].[Category])} ON ROWS
FROM [Adventure Works]
WHERE(
[Date].[Calendar].[Calendar Year].&[2003]
,[Customer].[Customer Geography].[Country].&[United States]
)

The above example works with the AdventureWorks DW database, but only on three of the hierarchies. Which implies the biggest drawback of this implementation technique - if you have more than 5-6 hierarchies the nesting of the IIF() and maintenance of the MDX is tedious and error prone. However, if you're diligent it will work.

Officially - I'm a Nerd (though barely)

OK - I took the test. Nerd score of 59. Not much. Looks like I need some work.

What's your nerd score?

Cube Collation causes ERROR in Excel Addin

I ran across an interesting error earlier this week using the Cube Analysis add-in of Excel. I set up a connection using Cube Analysis (Cube Analysis Manage Connection), then proceeded to create a report (Cube Analysis Build Report). When I selected the Data View pane and selcted a dimension/hierarchy the pane which shows the hierarchy with members showed -ERROR.



Through a trial and error process of comparing an Adventure Works cube to the cube which caused the error I discovered there was an difference in the collation sequence of the cube (cube property exposed in BI Studio). The Adventure Works cube had collation setting "Latin1 General, Accent sensitive". The cube which threw the error had collation setting "Latin1 General, Case sensitive, Accent sensitive". Changing the collation of the cube to "Latin1 General, Accent sensitive" eliminated the error.



My conclusion is that the Case sensitive setting in the cube collation forced the Cube Analysis Excel add-in to throw the error.