December 11, 2007 at 8:24 pm
I have a customer dimension with a number of hierarchies. Nearly always, calls to the customer dimension will be down to the customer code level. Here's a simplification of some of the hierarchies as they stand:
Hierarchy 1 - Delivery Region
Level 1 - Country
Level 2 - State
Level 3 - Region
Hierarchy 2 - Sales Region
Level 1 - Country
Level 2 - State
Level 3 - Region
Hierarchy 3 - Corporate Customer
level 1 - corporate parent
Level 2 - Customer Code
Level 3 - Customer Ship-to
Is it better to have all my hierarchies drill down to the customer ship-to level (so my delivery region and sales region hierarchies would have 6 levels) or to use a function like NON EMPTY ([Delivery Region].AllMembers * [Corporate Customer].AllMembers) in my MDX? This is made slightly more complex as there is a natural hierarchy directly from the customer ship-to to delivery/sales region, rather than from the corporate parent (though these still need to be seen above the ship-to).
December 12, 2007 at 5:59 am
I am assuming you are using Analysis Services 2005.
"is it better" That is going to really depend on what you mean by better. You need to balance performance and user needs and they often conflict.
The first thing to think about is that from a performance standpoint, your attribute relationships are far more imporant than anything you configure in your hierarchies. Hierarchies drive how the data is going to be used, but yout attribute relationships drive what aggregations will be generated and ultimately be used.
You say you have a natural hierarchy - set this up in your attribute relationships correctly and try to configure hierarchies that match the relationship. That will drive users to query using what will most likely end up as aggregated information. I also tend to make any attribute hierarchies invisible for information that can be found at or near the top lof any hierarchies to force users to use the defined hierarchies rather than the individual attribute hierarchies.
December 13, 2007 at 11:04 am
Michael is bang on. I'd like to add a small qualification:
Think hard about what you'd like the hiearchies to do / support. For example, based on your terms, I'd expect the Delivery Region hierarchy to drill from Region to Customer Ship-to. Sales Region on the other hand, is more likely to drill to a corporate address. There's nor performance difference either way (aside from some influences on which aggregations might get built), but your focus should be on use when defining hierarchies.
BTW. Both hierarchies looked like they had the same levels. Don't know if that was intentional.
December 13, 2007 at 3:12 pm
Hi Michael and Dave, thanks for the responses. I've designed the hierarchies to flow all the way to customer ship-to - a customer may have ship-tos in different states and thus different sales regions. The hierarchies have the same levels, it's intentional (though the levels represent different values).
So my question was really directed at the performance/size implications of duplicating levels in a hierarchy as opposed to cutting a hierarchy short and crossing it with another (common) hierarchy at query time. Certainly though, the user experience comes well before performance in my case and so I've extended both hierarchies. Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply