Partial Alternate Hierarchies

There have been a couple of questions on the Microsoft OLAP newsgroup (microsoft.public.sqlserver.olap) recently about making dimension members appear in more than one place in the dimension. Have a look at this article for one method of implementing this. [More]

[Updated 27 Oct 2005] I have add more detail on the Analysis Services 2005 solution as it was a little bit light on regarding exactly how the dimension usage was set up.

Print | posted on Monday, October 24, 2005 9:08 AM

Comments on this post

# re: Partial Alternate Hierarchies

Requesting Gravatar...
Nicely written article. I've been rewritting similar needs for AS2005 with many-to-many dimensions.

However I don't have a clean solution for the higher level accounting needs. These require a parent-child dimension where each leaf member aggregates the financial transactions for some arbitrary accounts and for some arbitrary departments. The measures, acct and dept are all in the trans fact table.

I cannot see how to implement this conceptual union of two m2m dimensions. I plan to reuse our inelegant AS2000 approach based on calculated member formulas [a GUI lets users specify the (acct,dept) + (acct,dept) for each "line" and then generates the MDX]. However, this will prevent drilldown and stroke user frustration.

Could you point the way toward another approach that would allow drilldown?

Thank you ahead of time ... John Szwaronek
Left by John Szwaronek on Oct 28, 2005 2:55 AM

Your comment:

 (will show your gravatar)