Microsoft
70-463 · Question #99
70-463 Question #99: Real Exam Question with Answer & Explanation
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.
Design and implement a data warehouse
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.
Explanation
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.
Common mistakes.
- A. 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.
- B. 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.
- D. 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
Topics
#dimension design#conformed dimensions#hierarchy#snowflake schema
Community Discussion
No community discussion yet for this question.