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