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.
Create a time mapping table.
B.
Change the level of granularity in both fact tables to be the same.
C.
Merge the fact tables.
D.
Create a view on the sales table.
Is this correct? Shouldn’t it be A?
None of the answers are correct.
Create a time dimension that can join to both fact tables at their respective granularity.
agree
I would say B O_o
D obviously is wrong. How could a view on the Sales table help?
C, merging the fact tables also doesn’t make sense. The fact tables do not have the same granularity, so how can we merge them in a useful way?
B might sound alright, but what if you only have sales figures per month. Also, storing sales figures per day in your data warehouse if you only need them per month isn’t a sensible thing to do.
This reasoning might lead you to A, but what is a ‘time mapping table’? I know what a snowflake-schema is and what a normalized dimension is, but the term ‘time mapping table’ is new to me. I would create a time dimension table per day with a parent dimension with month information. Is this the same as what dbo proposes?
You need to design a solution that can join a single time dimension to both fact tables
Merge the fact tables is the single time dimention, that can join to both fact tables.
In that case merge = union (by time).