70-467 · Question #76
You need to slice data by the CouponUsed, OnSale, and Closeout columns. What should you do?
The correct answer is D. Create one junk dimension. A junk dimension consolidates multiple low-cardinality flag or indicator columns into a single dimension table, reducing fact table width and the number of dimension foreign keys.
Question
You need to slice data by the CouponUsed, OnSale, and Closeout columns. What should you do?
Options
- ACreate one linked dimension for each column.
- BCreate one degenerate dimension.
- CCreate one role-playing dimension.
- DCreate one junk dimension.
How the community answered
(30 responses)- A10% (3)
- B13% (4)
- C3% (1)
- D73% (22)
Why each option
A junk dimension consolidates multiple low-cardinality flag or indicator columns into a single dimension table, reducing fact table width and the number of dimension foreign keys.
A linked dimension is a dimension that is shared across multiple measure groups or cubes; it does not address the problem of storing flag columns from a fact table.
A degenerate dimension holds a key attribute (such as an order number) directly in the fact table with no associated dimension table, which is not applicable to multi-column flag attributes.
A role-playing dimension is a single physical dimension used multiple times in a fact table under different aliases (e.g., Order Date and Ship Date both pointing to a Date dimension), which does not apply to unrelated flag columns.
CouponUsed, OnSale, and Closeout are classic low-cardinality boolean or flag attributes with no natural parent dimension. A junk dimension combines all permutations of these flags into one small dimension table with a surrogate key, replacing three separate foreign keys in the fact table with one and keeping the dimensional model clean.
Concept tested: Junk dimension design for low-cardinality flag attributes
Source: https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/data-warehousing
Topics
Community Discussion
No community discussion yet for this question.