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?
A.
Create two product tables, dimProduct and dimProductCategory. Connect
factSales to dimProduct and factProductTarget to dimProductCategory with foreign key
constraints. Direct the cube developer to use key granularity attributes.
B.
Create one product table, dimProduct, which contains product detail, category, and
subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct
the cube developer to use a non-key granularity attribute for factProductTarget.
C.
Create three product tables, dimProduct, dimProductCategory, and
dimProductSubcategory, and a fourth bridge table that joins products to their appropriate
category and subcategory table records with foreign key constraints. Direct the cube
developer to use key granularity attributes.
D.
Create three product tables, dimProduct, dimProductCategory, and
dimProductSubcategory. Connect factSales to all three product tables and connect
factProductTarget to dimProductCategory with foreign key constraints. Direct the cube
developer to use key granularity attributes.