Monday, March 12, 2012

Key Level of a Hierarchy - Why or Why not?

I'm trying to understand why I'd want to include a key level in one my hierachies. Can someone give me a use case? I don't really understand why they're useful and it seems that using it can be bad for perf from what I've read.

Thanks!

In terms of use in hierarchies there is nothing particularly special about the key attribute. The key attribute is the lowest level of granularity, and hence has the most unique members. This is the main reason that you might see reduced performance by using them, because the any queries and calcuations will involve more data point that if you used a higher level of granularity. But if you need to see your facts at that particular level of granularity then there is no specific reason not to use them.

The key is usually the attribute that links to the facts in the fact table. If you had a date dimension with a key of the date and you needed to drill down from year to month to the date, then you would use the key attribute as the bottom level of the hierarchy.

No comments:

Post a Comment