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 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
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.
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