nerdexam
Microsoft

70-467 · Question #95

You need to design the dimCustomers table. Which design approach should you use?

The correct answer is B. Type 2 slowly changing dimension. A Type 2 SCD preserves the full history of dimension attribute changes by inserting a new row for each change, making it essential when tracking how customer attributes have evolved over time.

Design a data warehousing solution

Question

You need to design the dimCustomers table. Which design approach should you use?

Options

  • AReference dimension
  • BType 2 slowly changing dimension
  • CJunk dimension
  • DConformed dimension
  • EType 1 slowly changing dimension

How the community answered

(29 responses)
  • A
    3% (1)
  • B
    72% (21)
  • C
    14% (4)
  • D
    3% (1)
  • E
    7% (2)

Why each option

A Type 2 SCD preserves the full history of dimension attribute changes by inserting a new row for each change, making it essential when tracking how customer attributes have evolved over time.

AReference dimension

A reference dimension is a dimension linked to a fact table indirectly through another dimension, which addresses relationship design rather than handling attribute change history.

BType 2 slowly changing dimensionCorrect

Type 2 Slowly Changing Dimension adds a new record each time a tracked attribute (such as a customer's address or status) changes, preserving the historical value alongside the new one using surrogate keys, effective dates, or current-row flags. This allows fact table rows to be accurately associated with the customer's attributes as they existed at transaction time. It is the standard approach for dimCustomers when historical accuracy is a business requirement.

CJunk dimension

A junk dimension consolidates miscellaneous low-cardinality flags and indicators into a single table to reduce fact table width, and has no mechanism for tracking customer attribute history.

DConformed dimension

A conformed dimension is a dimension shared and reused across multiple fact tables or data marts to ensure consistent reporting, but it does not inherently address how historical changes to attributes are stored.

EType 1 slowly changing dimension

Type 1 SCD simply overwrites the existing attribute value with the new one, destroying historical data and making it impossible to report on what a customer's attributes were at a prior point in time.

Concept tested: Slowly changing dimension Type 2 design for customer history

Source: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/slowly-changing-dimension-transformation

Topics

#SCD Type 2#slowly changing dimension#customer history#dimension design

Community Discussion

No community discussion yet for this question.

Full 70-467 Practice