70-463 · Question #3
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 C. Create a conformed dimension for the geography dimension.. This question tests knowledge of conformed dimensions in data warehouse design, which are standardized shared dimensions used to integrate multiple data marts.
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 junk dimension for the geography dimension.
- BImplement change data capture.
- CCreate a conformed dimension for the geography dimension.
- DCreate three geography dimensions.
How the community answered
(58 responses)- A5% (3)
- B9% (5)
- C84% (49)
- D2% (1)
Why each option
This question tests knowledge of conformed dimensions in data warehouse design, which are standardized shared dimensions used to integrate multiple data marts.
A junk dimension is used to consolidate low-cardinality indicator and flag columns into one table; it is not designed to harmonize structural differences in a meaningful geographic dimension.
Change data capture is a mechanism for tracking DML changes in source tables for ETL purposes and is unrelated to dimensional modeling or consolidation strategy.
A conformed dimension is defined once with a unified, agreed-upon structure and shared across all data marts, making cross-mart reporting possible while centralizing maintenance. It resolves structural differences between the three geography dimensions into a single authoritative version that represents all original data.
Maintaining three separate geography dimensions defeats the consolidation goal, increases maintenance burden, and prevents unified cross-mart reporting.
Concept tested: Conformed dimension design for data mart consolidation
Source: https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/data-warehousing
Topics
Community Discussion
No community discussion yet for this question.