Thursday, October 28, 2004

Use Hierarchies are Fake

One of the strongest points I tried to make while teaching about the new world of Yukon Analysis Services is that User Hierarchies are completely devoid of meaning--except insofar as they map to attribute hierarchies. One of the students had an interesting scenario, where she wanted to calculate something only at the product level, never at any higher level of a user hierarchy. Adding IIF structures to test for every possible user hierarchy seemed daunting--and unsupportable. So we tried a simple test.

We put a set of User Hieararchy members on Rows in a report, and then created calculated members to show the name of the current member of the attribute hierarchies. So on rows, we had [User Hierarchy].Members, but on columns displayed [Attribute Hierarchy].CurrentMember.UniqueName, etc. In all cases, the current member of the attribute hierarchy was the member mapped to from the user hierarchy.

This was perfect. In the MDX, we could test for [attribute hierarchy].currentmember.level.ordinal <> 0, and it wouldn't matter where we were in whatever user hierarchy.

- Reed

No comments: