BenchMark Quizzes by MDFT Pro

Prepare Data for a Star Schema Dimension Table

You work for MDFT Pro, a well-known training agency that offers professional certification courses. Mark, a Business Intelligence Analyst at MDFT Pro, has been asked to design a data warehouse to analyze course material sales.

Currently, all sales data is stored in a flat table in a Fabric lakehouse called CourseMaterials. The table contains the following columns:

Column Name	Description
TransactionID	The unique transaction ID
Date	The date of the transaction
MaterialID	The unique ID of the purchased course material
MaterialColor	The type of the course material
MaterialName	The name of the course material
SalesAmount	The sales amount of the transaction

Mark plans to implement a star schema by creating two tables: FactSales (for transaction metrics) and DimMaterial (for material information). The DimMaterial table will track changes over time using slowly changing dimension techniques.

Which three columns should Mark include in the DimMaterial table?

Choose all correct answers from the options below.

Explanations for each answer:

Date is incorrect. The Date column is a transactional attribute that belongs in the fact table (FactSales), not the dimension table. Fact tables store measurements and events with their associated timestamps, while dimension tables contain descriptive attributes about entities.
MaterialName is correct. MaterialName is a descriptive attribute that characterizes materials and belongs in the DimMaterial dimension table. Dimension tables store descriptive information about business entities that can be used for grouping and filtering in reports.
MaterialType is correct. MaterialType is a descriptive attribute of materials and should be included in the DimMaterial dimension table. This allows analysts to filter and group sales data by material type in their reports.
TransactionID is incorrect. TransactionID is a transactional identifier that belongs in the fact table (FactSales). It identifies specific sales transactions and is not a descriptive attribute of materials.
SalesAmount is incorrect. SalesAmount is a numeric measure that belongs in the fact table (FactSales). Fact tables store measurements and metrics that can be aggregated, while dimension tables contain descriptive attributes.
MaterialID is correct. MaterialID is the unique identifier for materials and must be included in the DimMaterial dimension table as the primary key. This key is used to link the dimension table to the fact table in the star schema.

Learn more about dimensional modeling:

Dimensional Modeling

Practice Exam

Prepare Data for a Star Schema Dimension Table

Discuss this question on social media: