Which Columns Belong in DimProduct?

DP-700 By islever Editorial Last updated

Design and implement loading patterns

Short answer

BCF Include ProductID, ProductName, and ProductColor in DimProduct as the product key and descriptive attributes.

Question

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.
Exhibit: Which Columns Belong in DimProduct?
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.
  1. Date
  2. ProductName Correct Answer
  3. ProductColor Correct Answer
  4. TransactionID
  5. SalesAmount
  6. ProductID Correct Answer

Key Takeaways

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.

Common mistake: 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.

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.

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.

More DP-700 FAQ →

Discussion 18 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 18

  1. QAZdbarhate12345678 👍 13
    Columns to Include in DimProduct ProductID - This is the unique identifier for each product and acts as the primary key in the DimProduct table. ProductName - Contains the name of the product, which is a descriptive attribute. ProductColor - Contains the color description of the product, which is also a descriptive attribute.
  2. Tuki93 👍 4
    I believe the provided answer is correct
  3. shmmini 👍 3
    in this case, the transaction date is referenced. you cannot use it as it is. If you have to track SCD, you have to create your own Date Column, and populate it differently, not from this table
  4. fdsdfgxcvbdsfhshfg 👍 3
    BCF seem to be correct, but it won't allow us to track changes so on a fence with these answer, some additional logic coming from Date column is required
  5. Wipag 👍 2
    ✔ F. ProductID Primary/business key of the dimension. Used to join with the fact table (FactSales). ✔ B. ProductName Descriptive attribute of the product. Typically shown in reports. ✔ C. ProductColor Another descriptive attribute. Useful for slicing data and analysis.
Show 13 more comments
  1. maham 👍 2
    The dimension table has to track the changes also. Then date field is important for SCD. So Product color takes second priority in this list
  2. renan_moraes37 👍 2
    For dimension needs to be every information about the products like Color and name, also the key which is productID
  3. Madhu2023 👍 2
    BCF are the attributes of Product. TranId , Date and SalesAmt quaify for fact table instead
  4. AndrewsOtis 👍 2
    Correct BCF It is relation with a Dimension table of Products. The others are Fact items
  5. DarioReymago 👍 2
    ProductID ProductName ProductColor
  6. balanceBananes 👍 2
    Everything about the products
  7. smanzana 👍 2
    Correct BCF
  8. Kiracloud 👍 1
    - 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.
  9. robertlavigne 👍 1
    Date: Should be in the FactSales table ProductName: Attribute of the product and belongs in DimProduct ProductColour: Attribute of the product and belongs in DimProduct TransactionID: Belongs in the FactSales Table Sales Amount: Belongs in the FactSales Table ProductID: Will be the primary key for DimProduct
  10. yue_young 👍 1
    Please also note that the Date column may not be the product modified date.
  11. alqazqzy.asseel 👍 1
    All product information without transaction, sales, or date columns
  12. oose 👍 1
    The trabsactio the date and the sumbshould be the other table
  13. Josekonoor 👍 1
    dimProduct -> Unique Product

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

A 500-million-row fact table exceeds the F32 Direct Lake guardrail, so increase the capacity to F64

S-grade

Shortcut caching supports only S3/GCS shortcuts and excludes files larger than 1 GB

S-grade

Updating warehouse tables requires at least the Contributor workspace role because Write is not shareable to a Viewer

S-grade

Run T-SQL in a KQL database only by prefixing the query with a -- comment line

S-grade

← Back to DP-700 Study Guide