Forum Discussion
Power Participantdegenerate 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
Community SupportHi 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
Power ParticipantSorry, 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.
- v-achippa
Hi Jeanxyz,
Thank you for the response and yes the dimension tables normally provide several benefits. The key point here is that those benefits only apply when the table is functioning as a real dimension that means like it has repeated values, attributes or is shared across fact tables.
In a degenerate dimension where a single column that is 1:1 with the fact and has no attributes, none of those benefits apply including AutoExist, because there are no attribute combinations to prune.
That is why microsoft treats it as an exception and recommends keeping it in the fact table. This is not contradicting the general rules, it’s just recognising that a 1-column, high-cardinality table is not functioning as a real dimension.
FYI please refer this below documents:
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema#dimension-tables
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema#degenerate-dimensions
Thanks and regards,
Anjan Kumar Chippa
- Jeanxyz
It's not correct. There is only one column in the dimension table, but it doesn't mean there is a 1:1 relationship between the dimension and fact table.
- v-achippa
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