How to handle : Product with N level of categroeis

[kuldeepchitrakar]

Hi

I have a product say ‘ABC’ now its product dimension, however a product can have N level of categories.

e.g.

Product ‘ABC’ can have following categories. Cat1->Cat2-Cat3

And product ‘XYZ’ can have C at1->Cat2->Cat3->Cat4

How do I handle this scenario. Can I use bridge table between Product dimensions and Fact table.

Also, It is also possible that product can change the categories means SCD, If I use bridge table how to handle SCD.

--Kuldeep

[Jeff Smith]

One way of doing it is to have the hiearchy for ABC repeat one of it's levels. Nothing says that Cat3 to Cat4 can't be one to one.

[kuldeepchitrakar]

One way of doing it is to have the hierarchy for ABC repeat one of it's levels. Nothing says that Cat3 to Cat4 can't be one to one.

But levels are not limited it could 4, may be 5 or may be 10.

What if I create a categories table which has a bridge table type structure (represents hierarchies within categories) and join it to Product dimensions.

Categories(:1) -----> (:N) Product (:1) ------->(:N) FactTable

As Product can have only one root level hierarchy. Do you see any problem traversing the product hierarchy like

Product1->Category1->Categgory2->------- etc

--Kuldeep

[John Simon]

You need to either use a parent-child hierarchy, or a bridge table.
Whichever you choose will depend on your OLAP/Reporting tool.

[Sponsored content]