70-467 Question #162: Real Exam Question with Answer & Explanation

Question

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.

Explanation

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.

Common mistakes.

• A. 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.
• C. 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.
• D. 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

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

No community discussion yet for this question.