Home » Microsoft » 70-462 v.2 » What should you do?
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?
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/Reference:
Explanation:
With dimensionally modeled star schemas or snowflake schemas, decision support queries follow a typical pattern: the query selects several measures of interest from the fact table, joins the fact rows with one or several dimensions along the surrogate keys, places filter predicates on the business columns of the dimension tables, groups by one or several business columns, and aggregates the measures retrieved from the fact table over a period of time.
The following demonstrates this pattern, which is also sometimes referred to as a star join query:
• select ProductAlternateKey,
• CalendarYear,sum(SalesAmount)
• from FactInternetSales Fact
• join DimTime
• on Fact.OrderDateKey = TimeKey
• join DimProduct
• on DimProduct.ProductKey =
• Fact.ProductKey
• where CalendarYear between 2003 and 2004
• and ProductAlternateKey like ‘BK%’
• group by ProductAlternateKey,CalendarYear