Forum Discussion

Jeanxyz's avatar
Jeanxyz
Icon for Power Participant rankPower Participant
9 months ago
Solved

degenerate dimensions

I'm reading MS documentation about data modeling, and got confused about degenerate dimensions. Basically MS says if there is only one column in the dimension table and this column (e.g., sales order...
Need Help
  • v-achippa's avatar
    v-achippa
    9 months ago

    Hi Jeanxyz,

     

    Yes you are right that one column does not automatically mean 1:1. The real rule is that,
    If that column has repeated values and is used to group or filter across many fact rows, then it is a real dimension and should stay as a dimension.
    If the column is almost unique for each fact row and has no descriptive attributes or reuse, then it behaves like a degenerate dimension and there is no benefit in keeping a separate table.

     

    So the decision is not based on the number of columns, it is based on cardinality and whether the column actually functions as a dimension.

     

    Thanks and regards,

    Anjan Kumar Chippa

MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 months ago

Hi Jeanxyz ,

 

This is a very specific usecase where the Sales Order Table is not a dimension in itself because it aggregates values of the sales order that will allow you to pickup data for the dates, customer and so on that would connect to other dimension tables, so in the end you would need to have a Snowflake model with the Sales connected to the sales order and the sales order connected to the other dimensions.

 

In this case you should pass all your attributes to the fact table and generate a star schema and since the Sales Order Column is a single column in that model you can filter directly from the fact table.