Microsoft Certified: Power BI Data Analyst AssociateModel the dataHard
A data modeler is optimizing a Power BI model with a complex star schema. The 'Sales' fact table has millions of rows and is related to several dimension tables including 'Products', 'Customers', and 'Date'. To improve query performance, the modeler wants to reduce the cardinality where possible. Which of the following columns, if present in the 'Sales' fact table, would be the most impactful to remove or move to a dimension table for performance optimization?
- ASaleAmount (numeric value of the sale)
- BProductCategory (text description, derived from ProductID)
- CDateKey (integer for the transaction date)
- DOrderID (unique identifier for each transaction)
Show answer & explanationAnswer & explanation
Correct answer: B. ProductCategory (text description, derived from ProductID)
ProductCategory, being a text column and a descriptive attribute derived from ProductID, has high cardinality and is best stored in the 'Products' dimension table. Keeping it in the fact table unnecessarily increases size and impacts performance.
Why the other options are wrong
- A. SaleAmount is a measure, which is a core component of a fact table and should remain there.
- C. DateKey is a foreign key to the 'Date' dimension and is essential for joining and time intelligence; it should remain in the fact table.
- D. OrderID is a high cardinality column but is often a necessary primary key in the fact table for transaction identification.
Fact Table Column Optimization
Optimizing fact table columns involves ensuring only essential foreign keys and measures are present, minimizing cardinality and data types that consume excessive memory.
- Fact tables should be 'skinny' – few columns, many rows.
- Avoid descriptive text columns in fact tables; move them to dimensions.
- High cardinality columns increase model size and query time.
- Foreign keys (integer IDs) and measures (numeric values) are appropriate for fact tables.
Memory trick: Fact tables are like lean machines: only essential parts, no extra fluff.