Exam 70-462 topic 2 question 235 discussion

Actual exam question from Microsoft's 70-462

Question #: 235
Topic #: 2

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.

Show Suggested Answer

Suggested Answer: D 🗳️
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

by davidkuz at April 16, 2020, 6:03 p.m.

Comments

Submit Cancel

mickeygeorge

4 years, 5 months ago

Think the answer is C creating a single time dimension to join both tables at their respective granularity.

upvoted 1 times

...

davidkuz

5 years, 2 months ago

does this really belong in the administration of a SQL server. Data warehousing maybe

upvoted 1 times

...