Which Columns Belong in DimProduct?
You have a Fabric workspace that contains a lakehouse named Lakehouse1. Data is ingested into Lakehouse1 as one flat table. The table contains the following columns.
You plan to load the data into a dimensional model and implement a star schema. From the original flat table, you create two tables named FactSales and DimProduct. You will track changes in DimProduct.
You need to prepare the data.
Which three columns should you include in the DimProduct table? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
Community Insight
The question tests dimensional modeling: a dimension table carries the business key plus descriptive attributes, and the trap is pulling in fact-side columns like Date or SalesAmount, or excluding ProductID.
When converting a flat sales table into a star schema in Microsoft Fabric, dimension tables hold descriptive product attributes while fact tables hold measures and transactional keys. The correct DimProduct columns are ProductID, ProductName, and ProductColor, leaving Date, TransactionID, and SalesAmount in FactSales.
The most common wrong pick is BCE (swapping ProductID for SalesAmount); learners confuse a numeric measure in FactSales with a product descriptor, breaking the star schema's key relationship.
Community Discussion (18 comments)
- ProductID acts as a unique identifier for each product (primary key). - ProductName provides a descriptive name, useful for reporting and analysis. - ProductColor adds valuable categorical information, potentially useful in filtering and segmentation. Other columns like Date, TransactionID, and SalesAmount are better suited for fact tables (FactSales), since they contain transaction-specific details rather than product attributes.
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
ProductID is the business/primary key that joins DimProduct to FactSales, and ProductName and ProductColor are descriptive attributes about the product itself—the exact contents of a dimension table in a star schema. As Wipag noted, ProductID is the "Primary/business key of the dimension. Used to join with the fact table (FactSales)." Every value in these three columns describes a product, independent of any individual sale, which is precisely what dimensional modeling requires for a dimension.Why the Other Options Are Wrong
Date, TransactionID, and SalesAmount are all transaction-level facts: Date and TransactionID identify when and which sale occurred, and SalesAmount is a additive measure—these belong in FactSales, as AndrewsOtis noted, "The others are Fact items." Date specifically cannot seed change tracking here, since shmmini observed that with SCD tracking "you have to create your own Date Column" rather than reusing the transaction date. Option E, SalesAmount, is a classic distractor because it is numeric product-related data, yet it measures the sale rather than describing the product.Community Comment Notes
The comments converge on BCF, with the top-rated explanation from QAZdbarhate12345678 walking through ProductID as unique identifier, ProductName as descriptive attribute, and ProductColor as a further attribute. One commenter on the fence, fdsdfgxcvbdsfhshfg, worried that "it won't allow us to track changes" because a true SCD type 2 dimension needs surrogate keys and date-stamped versioning columns. That is a fair design caveat, but those SCD columns would be generated during transformation, not taken from the original flat table, so BCF remains the best selection among the given options.Official Reference
Exam Strategy
For star schema questions, sort every column into two buckets: things that describe an entity (dimension) versus things that describe an event's quantity, time, or ID (fact). If a column would stay the same for every row of a given product, it belongs in the dimension table.
Frequently Asked Questions
Why can't SalesAmount or Date be part of DimProduct?
SalesAmount is an additive measure and Date identifies the sale event, so both are fact-table columns. Dimension tables hold only attributes that describe the product itself.
How do I track changes in DimProduct if no date column is included?
SCD tracking uses surrogate keys and generated valid-from/valid-to dates created during transformation, not the transaction Date from the original flat table.