Splitting hierarchies and hierarchy levels into multiple dimensions.

[dcow]

I have read that Splitting hierarchies and hierarchy levels into multiple dimensions is mistake frequently made when developing the dimensional Models. Consequently i am trying not to make such mistakes. In most situations i have found the Hierachy is certainly valid to the business and will be usefull in drill down operations and serves to define the 1 many relationship within tthe dimension and limit snowflaking. I seem to however be frequently encountering the need to creat a dimension at the different hierachy levels. For example
i might start with a PRODUCT Dimension
wuth attributes alomng the lines of

Product Category
Product Brand
Product
...


But also find the business being modelled would benefit from a dimension at the Product Category level and sometimes even the Product Brand level when the granularity of the facts being modelled is changed.


Is this something that others frequently encounter.

[kapoor_dh]

It's always good to define the entire hierarchy in a single dimension (a kind of de-normalized structure) and let the fact be at the lowest granularity,later as the business demands we can go with the summary/aggregate tables or mat views,this gives us the full flexibility for the drill down to the lowest level when it comes to reporting