nerdexam
Microsoft

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.

Design a data warehousing solution

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)
  • A
    10% (3)
  • B
    13% (4)
  • C
    3% (1)
  • D
    73% (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.

ACreate one linked dimension for each column.

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.

BCreate one degenerate dimension.

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.

CCreate one role-playing dimension.

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.

DCreate one junk dimension.Correct

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

#junk dimension#low-cardinality flags#dimension design#data warehousing

Community Discussion

No community discussion yet for this question.

Full 70-467 Practice