Forum Discussion
degenerate dimensions
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
Hi Jeanxyz,
Thank you for reaching out to Microsoft Fabric Community.
Thank you MFelix, KarinSzilagyi and Idrissshatila for the prompt response.
In this case microsoft’s guidance is correct. If a dimension contains only a single column that is 1:1 with the fact table, then keeping that column in the fact table is better so that it avoids an unnecessary relationship, reduces model clutter and storage and also speeds queries.
- Keep a separate dimension when the column has many repeated values, so that deduplication saves space or when you need attributes such as descriptions, hierarchies, translations, RLS or reuse across multiple fact tables. If the value is unique per fact row and has no additional analytic value, it’s best to keep as a degenerate dimension directly in the fact.
Thanks and regards,
Anjan Kumar Chippa
Sorry, but I'm not convinced with any of the explanations. MS documentation says dimension table has benefits 1, 2, 3, 4, 5..., but the degenerate dimension doc says because benefit 4 doesn't apply, hence the dimension table should be removed. Am I the only one who see the fallacy?
So unless I understand how the fact and dimension relationship acts step by step, I'm afraid I won't buy into this documenation. Also Marco Russo said slicing and dicing should always come from dimension table to avoid auto exist error. No one talks about this in the thread.