powerbidata-modelingdimensional-modeling

Should product hierarchy be added to 1 or multiple dim tables?


I have product hierarchy as follows:

Category

Sub category

Product

When does it make sense to have all these 3 columns in 1 table called Prooduct, or to make 3 tables - 1 for each?


Solution

  • They should all be in a single table as they're logically related. Not only that, but data modelling is very different compared to OLTP. For instance, it is not uncommon to have a junk dimension which contains several columns which can all appear unrelated. Modelling is a very detailed subject and if you're after reading material, I can recommend Star Schema: The Complete Reference below.

    https://www.amazon.co.uk/Schema-Complete-Reference-Christopher-Adamson/dp/0071744320

    The only time is makes sense to have these in separate tables and "snowflake" them is to take advantage of a little trick in PowerBI to suppress blanks in a slicer or matrix if you have a ragged hierarchy but this comes with drawbacks in having to write more complicated DAX down the line.