Product DIM with multiple hierarchies

[DJ]

I am working on a new product dimension

We have various different hierarchies for our products. Eg each business unit might want its own hierarchy, along with Marketing, and anyone else in the future

ALSO - the meaning of each level of a hierarchy might change in the future. That is, right now, business unit XX's hierarchy might be product line / product brand / product group... but tomorrow it could be something entirely different

I am inclined to add a set of columns to my product DIM for each needed hierarchy

Eg add these columns for BUsiness unit XX's hierarchy.
XX_Level_1_hierarchy_name
XX_Level_2_hierarchy_name
XX_Level_3_Hierarchy_name

Then add these for BUsiness unit YYs hierarchy.
YY_Level_1_hierarchy_name
YY_Level_2_hierarchy_name
YY_Level_3_Hierarchy_name

and let our reporting tool use column names such product line / product group etc.

Other approaches?

Performance is extremely important here

Thanks for the ideas!!

DJ

[ngalemmo]

If the hierarchies are fixed and well defined... i.e. the levels are known and named, then adding them as attributes on the dimension table is a simple and effective way to go. It is also the easiest for the business to understand as it is a concept that is familiar to them. Wither you need to spell out each level of each hierarchy or simply have each of the values that they can arrange any way they want is your own call... I prefer the latter.

If you need to deal with unstructured/ragged hierarchies of unknown depth, then you need to create bridge tables with supporting dimensions to handle them.

[DJ]

I am inclined to add these to the dimension as well

We are trying to avoid table changes in the future

Therefore I am considering column names such as

YY_Level_1_hierarchy_name
YY_Level_2_hierarchy_name
YY_Level_3_Hierarchy_name

instead of columns names of

YY_product_line_name
YY_product_group_name
YY_product_brand_name

Our business objects names will be

YY_product_line_name
YY_product_group_name
YY_product_brand_name

where YY is a business unit acronym

[Sponsored content]