PL-300 Model the data Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  Create a separate dimension table for transactions

  Why it's wrong here

  Creating a separate dimension table for transactions misidentifies TransactionID as a dimension with descriptive attributes. TransactionID is a classic degenerate dimension: it exists only to uniquely identify a fact row and has no related fields to populate a dimension table. Adding such a table would force an unnecessary relationship, extra joins, and additional model complexity with zero analytical benefit. The standard star schema practice is to keep degenerate dimensions in the fact table.

• ✓

  Keep TransactionID in the fact table

  Why this is correct

  Keeping TransactionID in the fact table is correct because it acts as the natural key for each transaction fact row, preserving row-level identity without creating a separate dimension. As a degenerate dimension, it supports direct row references and enables critical operations like incremental refresh, audit trails, and error reconciliation. It also lets you uniquely identify rows when the fact table has no other unique key, even if you need to combine it with a line number at a lower grain. This approach follows Microsoft's star schema guidance and avoids unnecessary joins or duplicated data.

• ✗

  Move TransactionID to the Product dimension

  Why it's wrong here

  Moving TransactionID to the Product dimension would violate the grain of both tables. A single product can appear in thousands of transactions, and a single transaction can contain many products, so placing TransactionID there would create a many-to-many relationship or force duplicated product rows. It would also imply that TransactionID is a product attribute when it is really a transactional event identifier. This destroys the referential integrity needed for accurate reporting and makes DAX measures like transaction counts unreliable.

• ✗

  Remove the TransactionID column to reduce model size

  Why it's wrong here

  Removing the TransactionID column to reduce model size sacrifices row-level traceability for negligible storage savings. TransactionID is often an integer or string of a few bytes, so the impact on overall model size is minimal, while the loss prevents you from linking back to source systems, identifying duplicate or missing records, and performing row-level debugging. Without a unique or semi-unique identifier in the fact table, even straightforward aggregations can mask data quality issues. In a properly modeled fact table, a degenerate key such as TransactionID should never be deleted purely to shrink the model.

Go deeper

Related to this question