70-463 · Question #74
You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure. Most of the dimensions have the same structure a
The correct answer is A. Create a conformed dimension for the geography dimension.. A conformed dimension is a dimension that is standardized and shared across multiple fact tables or data marts, carrying a consistent definition of attributes. When consolidating three data marts that have slightly different geography dimensions, creating a single conformed geogr
Question
You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure. Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart. You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented. What should you do?
Options
- ACreate a conformed dimension for the geography dimension.
- BImplement change tracking.
- CCreate a degenerate dimension for the geography dimension.
- DCreate a Type 2 slowly changing dimension for the geography dimension.
How the community answered
(44 responses)- A68% (30)
- B5% (2)
- C9% (4)
- D18% (8)
Explanation
A conformed dimension is a dimension that is standardized and shared across multiple fact tables or data marts, carrying a consistent definition of attributes. When consolidating three data marts that have slightly different geography dimensions, creating a single conformed geography dimension ensures all original dimensional data is represented in one maintainable structure that all fact tables can reference uniformly. This enables cross-mart reporting and reduces maintenance overhead. Option B (change tracking) monitors data modifications but does not address dimensional consolidation. Option C (degenerate dimension) has no separate dimension table - it stores the key directly in the fact table, appropriate for attributes like order numbers, not geography. Option D (Type 2 SCD) tracks historical attribute changes over time, which does not solve the problem of merging structurally different dimensions from separate systems.
Topics
Community Discussion
No community discussion yet for this question.