70-463 · Question #72
You are designing a data warehouse for a fresh food distribution business that stores sates by individual product. It stores sales targets by product category. Products are classified into subcategori
The correct answer is B. Create one product table, dimProduct, which contains product detail, category, and. In a star schema designed for Analysis Services, the best practice is to denormalize dimension hierarchies into a single dimension table. Option B - a single dimProduct table containing product detail, subcategory, and category columns - follows this principle. Both fact tables (
Question
You are designing a data warehouse for a fresh food distribution business that stores sates by individual product. It stores sales targets by product category. Products are classified into subcategories and categories. Each product is included in only a single product 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 product
- factProductTarget, used to record the monthly sales targets by
product category Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets. You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements. What should you do?
Options
- ACreate two product tables, dimProduct and dimProductCategory.
- BCreate one product table, dimProduct, which contains product detail, category, and
- CCreate three product tables, dimProduct, dimProductCategory, and dimProductSubcategory,
- DCreate three product tables, dimProduct, dimProductCategory, and dimProductSubcategory.
How the community answered
(50 responses)- A6% (3)
- B66% (33)
- C8% (4)
- D20% (10)
Explanation
In a star schema designed for Analysis Services, the best practice is to denormalize dimension hierarchies into a single dimension table. Option B - a single dimProduct table containing product detail, subcategory, and category columns - follows this principle. Both fact tables (factSales at product granularity and factProductTarget at category granularity) can reference this one table via their respective keys. This eliminates extra joins, simplifies the cube design, and allows Analysis Services to naturally define the product → subcategory → category hierarchy within a single dimension. Options C and D describe a snowflake schema (normalized, separate tables per hierarchy level), which adds joins and complexity without meaningful benefit when Analysis Services is the consumer. A single denormalized dimension is preferred for cube data sources.
Topics
Community Discussion
No community discussion yet for this question.