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
Super UserHi 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.
- Jeanxyz
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?
- Idrissshatila
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
- Jeanxyz
Thanks for explaining the benefits of dimension table. That makes sense.
So here is my next question, what if the dimension table contains only one column as in the case of Dim_SalesOrderID? MS is saying it's better to remove the dimension table since there is only one column in the dimension table.
1. is that correct? Based on your explanation, even if there is only one column in the dimension table, it's better to keep the dimension table because it will speed up the filtering/slicing.
2. what if there are lots of unique rows in the dimension table? Will a long dimension table slow down the filtering query?
3. Can someone explain in more detail how the filtering works in Veripaq. In DataBricks, there is a clear explanation about how groupBy is handled in Executors step by step (shuffle-sorting - partition coalesce etc.)? How does Veripaq handle the filtering (i.e., groupBy) query?
-
