70-463 · Question #99
You are designing a data warehouse for a software distribution business that stores sales by software title. It stores sales targets by software category. Software titles are classified into…
The correct answer is C. Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory. A single denormalized dimension table that includes software title, subcategory, and category attributes allows both fact tables operating at different granularities to join to one conformed dimension.
Question
You are designing a data warehouse for a software distribution business that stores sales by software title. It stores sales targets by software category. Software titles are classified into subcategories and categories. Each software title is included in only a single software subcategory, and each subcategory is included in only a single category. The data warehouse will be a data source for an Analysis Services cube. The data warehouse contains two fact tables:
- factSales, used to record daily sales by software title
- factTarget, used to record the monthly sales targets by software
category Reports must be developed against the warehouse that reports sales by software title, category and subcategory, and sales targets. You need to design the software title dimension. The solution should use as few tables as possible while supporting all the requirements. What should you do?
Options
- ACreate three software tables, dimSoftware, dimSoftwareCategory. and
- BCreate three software tables, dimSoftware, dimSoftwareCategory, and
- CCreate one table, dimSoftware, which contains Software Detail, Category, and Subcategory
- DCreate two tables, dimSoftware and dimSoftwareCategory.
How the community answered
(27 responses)- A22% (6)
- B11% (3)
- C59% (16)
- D7% (2)
Why each option
A single denormalized dimension table that includes software title, subcategory, and category attributes allows both fact tables operating at different granularities to join to one conformed dimension.
Creating three separate tables introduces a snowflake schema, which adds join complexity in SSAS and can complicate the dimension relationship between the two fact tables at different granularities.
Like option A, three separate tables result in a snowflake structure that requires additional dimension relationships in Analysis Services and does not simplify joining both fact tables.
Consolidating Software Detail, Category, and Subcategory into one dimSoftware table creates a conformed dimension that both factSales (grain: title) and factTarget (grain: category) can join to at their respective levels. This avoids role-playing or multiple lookup joins and allows Analysis Services to browse the hierarchy within a single dimension. Denormalizing in this way is appropriate when the hierarchy is strict (each title belongs to exactly one subcategory and each subcategory to exactly one category).
Two tables still require a join and do not fully consolidate the hierarchy, making it harder to define a single conformed dimension that satisfies both factSales and factTarget granularities.
Concept tested: Conformed dimension design for multi-granularity fact tables
Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/dimensions-in-multidimensional-models
Topics
Community Discussion
No community discussion yet for this question.