Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data modeler is optimizing a Power BI model for an e-commerce platform. The 'Sales' fact table contains 'OrderID', 'ProductID', 'CustomerID', 'OrderDate', and 'SalesAmount'. The 'Products' dimension table contains 'ProductID', 'ProductName', 'Category', and 'SubCategory'. The model has a one-to-many relationship from 'Products' to 'Sales' on 'ProductID'. The modeler observes that filtering by 'Category' in a slicer is slow. Which action should the data modeler take to improve filtering performance?
- AChange the relationship to many-to-one from 'Sales' to 'Products'.
- BMark the 'Products' table as a date table.
- CCreate a composite key using 'Category' and 'SubCategory' in the 'Products' table.
- DAdd 'Category' and 'SubCategory' columns directly to the 'Sales' fact table.
Show answer & explanationAnswer & explanation
Correct answer: D. Add 'Category' and 'SubCategory' columns directly to the 'Sales' fact table.
Filtering on dimension columns often requires Power BI to traverse relationships. For frequently used and high-impact filters like 'Category' that are part of a dimension related to a large fact table, denormalizing these columns into the fact table can significantly improve query performance by reducing the need for relationship traversal during filtering operations.
Why the other options are wrong
- A. Changing the relationship direction would break the model's integrity and is incorrect for a standard star schema.
- B. Marking 'Products' as a date table is incorrect, as it is a product dimension, not a date dimension.
- C. Creating a composite key in the 'Products' table does not directly address the performance issue of filtering on existing columns across a large fact table.
Fact Table Denormalization for Performance
Adding frequently used dimension attributes directly to the fact table to reduce relationship traversals and improve query performance, especially with large fact tables.
- Reduces join operations during query execution.
- Can increase fact table size and storage, but often improves query speed.
- Best for highly used filter/grouping columns from small dimensions.
Memory trick: Optimize performance by reducing relationship jumps.