You are designing a data warehouse for a fresh food distribution business that stores sales 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 categoryReports 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.