Monday, January 4, 2010

Dealing with attributes that appear in more than one parent group

Sometimes you have an attribute in your dimension where the values appear in more than one grouping or category above it. Like the following:
Product     Product Category
Apple          Fruit
Apple          Snack

When you build this attribute in Analysis Services, you need to make the key for the Product attribute either a compound key, or build it off of something else that is unique, such as the business key. I don't happen to have the BK for Product, so I setup a composite key of Product Category and Product. If I didn't Analysis Services would choose one of the Product Categories and put all the measures in that Category when I was at that level of aggregation.
So, seems simple enough. The problem is that if I just setup the Product to have a composite key, I now get 2 Apple rows when I use Product as an attribute on a report outside of the Product Category->Product hierarchy. Note: if Product were 3 levels deep in a hierarchy, you would need to include all 3 levels. I also would get sorting based on Product Category and Product. I can switch the sorting pretty easily by having the sort based on Name instead of Key, but my users certainly don't want Apple to appear twice on the report.
The solution
Create an attribute with the composite key (don't forget the all important Attribute Hierarchies!), or base it on something unique like the business key, and give it a name such as Product for Hierarchy. Then create a second attribute (easily accomplished by dragging the field from the Data Source View pane of the Dimension Structure page to the Attributes pane) with the default settings (Product as KeyColumn, (none) as Name Column). Use the Product For Hierarchy for the Hierarchy, and rename it in the Hierarchy itself to Product. Then set the AttributeHierarchyVisible property to False, so it can't be seen outside of the hierarchy.
I routinely do this for many attributes so that I'm not forcing an analyst to use an attribute only via the hierarchy.

Hope this helps someone,

1 comment:

Anonymous said...

Amiable fill someone in on and this mail helped me alot in my college assignement. Thank you as your information.