Microsoft Certified: Power BI Data Analyst AssociateModel the dataHard
A data modeler is optimizing a Power BI model for a large enterprise. The 'Sales' fact table contains millions of rows and includes columns like 'SalesOrderID', 'SalesAmount', 'OrderDate', and 'CustomerID'. The model has a 'Customers' dimension table with 'CustomerID', 'CustomerName', 'Region', and 'Segment'. The modeler notices that reports filtering by 'CustomerName' are particularly slow. What is the most effective way to improve the performance of filters based on 'CustomerName'?
- AChange the relationship between 'Customers' and 'Sales' to 'Both' cross-filter direction.
- BAdd the 'CustomerName' column directly to the 'Sales' fact table.
- CCreate an index on 'CustomerName' in the 'Customers' table.
- DReduce the number of columns in the 'Customers' dimension table.
Show answer & explanationAnswer & explanation
Correct answer: B. Add the 'CustomerName' column directly to the 'Sales' fact table.
For very large fact tables and frequently used dimension attributes like 'CustomerName', denormalizing the 'CustomerName' directly into the 'Sales' fact table can drastically improve filter performance. This eliminates the need for Power BI to traverse the relationship between 'Sales' and 'Customers' for every filter operation, which can be expensive with millions of rows.
Why the other options are wrong
- A. Changing to 'Both' cross-filter direction can negatively impact performance and create ambiguity, especially with large fact tables, and is generally not recommended for performance improvement in this scenario.
- C. Power BI's VertiPaq engine handles internal indexing; creating an explicit index in the source database might help source queries but not Power BI's model performance directly.
- D. Reducing columns in the 'Customers' table might slightly reduce model size but won't address the core performance bottleneck of filtering a large fact table through a relationship.
Fact Table Denormalization
The process of adding attributes from a dimension table directly into a fact table to reduce the need for joins and relationship traversals during query execution, thereby improving performance.
- Reduces query execution time by eliminating joins.
- Increases the size of the fact table.
- Best used for frequently filtered or grouped dimension attributes, especially with large fact tables.
Memory trick: For fast fact filters, put dimension details right in the fact.