nerdexam
Microsoft

70-467 · Question #162

You need to design a data warehouse schema to support a multidimensional cube. The fact table will contain multiple columns representing order dates, shipping dates, and billing dates. The cube will…

The correct answer is B. In the database, create a time table containing a column that corresponds to each date. A single shared time dimension table used as a role-playing dimension is the best practice when a fact table contains multiple date columns referencing the same date domain.

Design a data warehousing solution

Question

You need to design a data warehouse schema to support a multidimensional cube. The fact table will contain multiple columns representing order dates, shipping dates, and billing dates. The cube will contain a measure group based on the fact table. What is the best design to achieve the goal? More than one answer choice may achieve the goal. Select the BEST answer.

Options

  • AIn the database, create a time table for each date column in the fact table.
  • BIn the database, create a time table containing a column that corresponds to each date
  • CIn the database, create a time table that uses an integer primary key and a datetime column.
  • DIn the database, create a time table that uses an integer primary key and a datetime column.

How the community answered

(24 responses)
  • A
    4% (1)
  • B
    83% (20)
  • C
    8% (2)
  • D
    4% (1)

Why each option

A single shared time dimension table used as a role-playing dimension is the best practice when a fact table contains multiple date columns referencing the same date domain.

AIn the database, create a time table for each date column in the fact table.

Creating a separate time table for each date column produces structurally identical redundant tables that must be maintained in sync, violating the role-playing dimension pattern and inflating schema complexity without any analytical benefit.

BIn the database, create a time table containing a column that corresponds to each dateCorrect

Creating one time table that contains date attribute columns (year, month, quarter, day-of-week, etc.) allows the fact table to hold multiple foreign keys - one for order date, one for shipping date, one for billing date - all referencing the same dimension, implementing the role-playing dimension pattern. This avoids schema duplication, keeps the dimension data consistent across all date roles, and allows Analysis Services to define separate dimension relationships for each role on the same measure group. The Kimball data warehousing methodology formally recommends this approach as the standard design for multi-date fact tables.

CIn the database, create a time table that uses an integer primary key and a datetime column.

A time table with only an integer primary key and a single datetime column lacks the rich date attributes - year, month, quarter, fiscal period, day name - required for meaningful time-based slicing, aggregation, and browsing in an Analysis Services multidimensional cube.

DIn the database, create a time table that uses an integer primary key and a datetime column.

This choice is identical to choice C - a table with only an integer key and a datetime column is too sparse to support the date hierarchy levels and attribute relationships needed for effective OLAP time analysis.

Concept tested: Role-playing date dimension design for multidimensional cube fact tables

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/define-a-regular-relationship-and-regular-relationship-properties

Topics

#time dimension#role-playing dimensions#star schema#date table design

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice