BenchMark Quizzes by MDFT Pro

Design Dimension Table For Star Schema

You work as a Data Modeling Specialist for Mark at MDFT Pro, a well-known training agency. MDFT Pro operates a Fabric workspace that contains a lakehouse named Lakehouse1 where sales transaction data is ingested as a single flat table. This table contains comprehensive information about course and training material purchases made by students and corporate clients. Mark has tasked you with transforming this flat structure into a dimensional model using a star schema to support efficient analytical queries and reporting. You will be creating two tables from the original flat table: FactSales for transaction metrics and DimProduct for product attributes. The requirement specifies that you need to track changes in DimProduct over time, implementing slowly changing dimension techniques to maintain historical product information.

The original flat table contains the following columns:

Name	Description
TransactionID	The unique identifier of a transaction
Date	The date of a transaction
ProductID	The unique identifier of a product purchased in a transaction
ProductName	The name of a product purchased in a transaction
ProductColor	The color of a product purchased in a transaction
SalesAmount	The sales amount of a transaction

Which three columns should you include in the DimProduct table?

Choose all correct answers from the options below.

Explanations for each answer:

TransactionID is incorrect. TransactionID is a fact-level identifier that represents individual transactions and should be included in the FactSales table, not in the DimProduct dimension table which describes product attributes.
Date is incorrect. Date is a transaction-level attribute that belongs in the FactSales table (or potentially a separate DimDate dimension). It is not an attribute of the product itself and should not be included in DimProduct.
ProductID is correct. ProductID is the unique identifier for products and serves as the primary key for the DimProduct dimension table. It's essential for linking the dimension table to the fact table.
ProductName is correct. ProductName is a descriptive attribute of the product that belongs in the DimProduct dimension table. Since you're tracking changes in DimProduct, changes to product names will be captured through slowly changing dimension techniques.
ProductColor is correct. ProductColor is a product attribute that belongs in the DimProduct dimension table. As you're tracking changes in DimProduct, changes to product colors can be captured and historized.
SalesAmount is incorrect. SalesAmount is a measurable fact that represents the transaction value and should be stored in the FactSales table, not in the dimension table which stores descriptive attributes.

Learn more about star schema design:

Star Schema Design

Practice Exam

Design Dimension Table For Star Schema

Discuss this question on social media: