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 has millions of rows and includes 'ProductID', 'CustomerID', and 'OrderDate'. The 'Product' dimension table contains 'ProductID', 'ProductName', 'Category', and 'SubCategory'. The 'Customer' dimension table contains 'CustomerID', 'CustomerName', 'Region', and 'Country'. The model currently uses a standard star schema with direct relationships. The modeler observes slow performance when filtering sales data by 'Category' and 'SubCategory' combined. To improve performance, the modeler decides to denormalize the 'Sales' fact table. Which columns should the modeler consider adding to the 'Sales' fact table during denormalization to directly address the observed performance issue?
- AAdd 'OrderYear' and 'OrderMonth' extracted from 'OrderDate' to the 'Sales' table.
- BAdd 'CustomerName' and 'Region' from the 'Customer' table to the 'Sales' table.
- CAdd 'ProductName' and 'Country' from the 'Product' and 'Customer' tables, respectively, to the 'Sales' table.
- DAdd 'Category' and 'SubCategory' from the 'Product' table to the 'Sales' table.
Show answer & explanationAnswer & explanation
Correct answer: D. Add 'Category' and 'SubCategory' from the 'Product' table to the 'Sales' table.
Denormalizing by adding 'Category' and 'SubCategory' directly to the 'Sales' fact table eliminates the need for Power BI to traverse the relationship to the 'Product' dimension table when filtering or grouping by these columns, significantly improving query performance for these specific filters.
Why the other options are wrong
- A. Adding 'OrderYear' and 'OrderMonth' might improve time intelligence queries but does not address the performance issue specifically related to 'Category' and 'SubCategory' filters.
- B. Adding 'CustomerName' and 'Region' would help with customer-related filtering but not with the observed slow performance related to 'Category' and 'SubCategory'.
- C. Adding 'ProductName' is generally not recommended for denormalization due to high cardinality, and 'Country' is not the source of the performance issue.
Fact Table Denormalization for Performance
The process of adding dimension attributes directly to a fact table to reduce join operations and improve query performance for frequently used filters.
- Reduces the number of joins required during query execution.
- Increases the size of the fact table.
- Best for low-cardinality dimension attributes that are frequently used for filtering or grouping.
Memory trick: Dimension's Best Attributes, Directly to Fact, Performance Shoots.