Administering Microsoft SQL Server 2012/2014 Databases — Question 37

You are designing a data warehouse with two fact tables.
The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables.
What should you do?

Answer options

• A. Join the two fact tables.
• B. Merge the fact tables.
• C. Create a time dimension that can join to both fact tables at their respective granularity.
• D. Create a surrogate key for the time dimension.

Correct answer: D

Explanation

The correct answer is D because creating a surrogate key for the time dimension allows for a consistent way to link both fact tables, each with its own granularity. Options A and B are not effective as they do not maintain the necessary separation of the fact tables, and option C, while relevant, does not address the requirement for a surrogate key that ensures integrity.