Forum Discussion
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 id) exists in the fact table, it's better to delete the sales order id dimension table and filter the fact table using the sales order id in the fact table itself (https://learn.microsoft.com/en-us/power-bi/guidance/star-schema#junk-dimensions).
"A degenerate dimension refers to an attribute of the fact table that's required for filtering. At Adventure Works, the reseller sales order number is a good example. In this instance, it doesn't make sense to create an independent table consisting of just this one column because it would increase the model storage size and result in Data pane clutter.
In the Power BI semantic model, it can be appropriate to add the sales order number column to the fact table to allow filtering or grouping by sales order number. It's an exception to the formerly introduced rule that you shouldn't mix table types (generally, model tables should be either dimension or fact)."
Is this really ok? I have long believed any category fields use for filtering or grouping should come from a dimension table in order to : reduce model size and simplify calculation, avoid auto-exist errors (https://www.sqlbi.com/articles/understanding-dax-auto-exist/) . But this MS documentation seems to break this golden rule.
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
12 Replies
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.
Hi Jeanxyz, a degenerate dimension applies specifically when the field would produce a dimension with a 1:1 relationship to your fact, where the dimension only contains that single column.
In that case, creating a separate dimension doesn't add any analytical value. You'd just end up with a table that mirrors the fact table key, which also increases the model size and adds an unnecessary relationship. Power BI would need to filter through a join, just to return exactly the same data you'd get by using the column directly from your fact table.The real question is why do we need dimension tables in the first place? Are you saying dimension table is only used to remove duplicates from the fact table? How about auto-exist?
Hello Jeanxyz .
mainly facts store events / transactions like amounts quantities and clicks for example. while dimensions store the attributes you filter / slice with and analyze by like stores, product names, customer names.
so instead of leaving the store name, customer name and attributes like age, gender, email. you create a dimension table that stores all columns of the customer and have a column key.
and only reference the key in the fact so that you eleminate repeated texts so that it performs better and have less storage.
If you keep these attributes in the Fact table:
-
They are repeated millions of times
-
They blow up file size
-
They slow relationships
-
