Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Creating a dimension table (with hierarchy)

Hello  -  I am using our Orders table to create a Family Code dimension table for our products.   (just referencing the table, and deleting all other columns).  

 

However, we also have a hierarchy.   

 

So, a particular type of product, goes under a family code, along with other related products. Something like:

Bikes:  Mountain Bikes,  Cruising Bikes,  Double-Seated Bikes  

Tents:  Round Tents,  Square Tents, Rectangle Tents

 

or 

 

Family Code    Product Code

 

HAPO                HAPO-A

                          HAPO-B

                          HAPO-C

LAPR                  LAPR-A

                          LAPR-B

                          LAPR-C

 

I was going to just keep these two columns for my Dim table, but then quickly realized I would not have unique values on the Family Code.     So, my question is, what is the best practice to create a dim table when you have hiearchies?  

 

Thanks in advance!

Need Help

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    For a dimension table, it's NOT necessarily to make each column contain distinct values.

    Typically, take the most common time table for example, Year and Month columns usually contain repeated items whereas Date column is distinct; because the granularity of Date is what we pay attention to.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Yes, that makes sense.   So as long as at least one column contains unique values, and that is the key column, then it can be a dimension table, correct?

       

  • HashamNiaz's avatar
    HashamNiaz
    Solution Sage
    5 years ago

    Hi !

    Try creating a [Family Code] dimension with only unique family code values either using DAX or Power Qury (M);

     

    DimFamilyCode = DISTINCT(Table[Family Code])

     

    You can treplave [Table] with your existing table name. Now you can create a 1-many relationship between [DimFamilyCode] & [Table] based on [Family Code] column.

     

    This technique is called bridging table. Similary you need to have a dimension with Distinct Product code & connect this table to [Table] using 1-many relationship based on [Product Code] column.

     

    Regards,

    Hasham