Microsoft Certified: Power BI Data Analyst AssociateModel the dataHard
A data modeler is optimizing a Power BI model for a large e-commerce platform. The model contains a 'Sales' fact table with millions of rows and a 'Customers' dimension table. There is a one-to-many relationship from 'Customers[CustomerID]' to 'Sales[CustomerID]'. The modeler observes that queries involving filtering by customer demographics are slow. Upon inspection, the 'Customers' table contains several columns with very high cardinality (e.g., 'EmailAddress', 'PhoneNumber') that are rarely used for direct filtering in reports but are included in the table. What is the most effective strategy to improve query performance related to customer demographics?
- AAdd an index to the 'CustomerID' column in both 'Customers' and 'Sales' tables.
- BConvert the 'Customers' table into a calculated table to pre-aggregate data.
- CChange the relationship between 'Customers' and 'Sales' to a many-to-many relationship.
- DRemove high-cardinality, rarely used columns from the 'Customers' table.
Show answer & explanationAnswer & explanation
Correct answer: D. Remove high-cardinality, rarely used columns from the 'Customers' table.
High-cardinality columns, especially if not frequently used for filtering or visual display, consume significant memory and can negatively impact query performance due to increased processing during filter propagation and data compression inefficiencies. Removing them reduces model size and improves query speed.
Why the other options are wrong
- A. Power BI's VertiPaq engine automatically handles indexing. Explicitly 'adding an index' is not a direct action available or typically needed in Power BI Desktop for performance optimization in this context.
- B. Converting to a calculated table would not address the cardinality issue and might introduce refresh overhead without performance gains for this specific problem.
- C. Changing to a many-to-many relationship would complicate the model and likely worsen performance, as it introduces ambiguity in filter propagation.
High Cardinality Column Optimization
Strategies to mitigate the performance impact of columns with many unique values in a Power BI data model.
- High cardinality increases memory consumption.
- Can slow down filter propagation and query execution.
- Remove unused high-cardinality columns.
- Consider archiving or moving to separate tables if needed for drill-through.
Memory trick: Cardinality too high? Prune and simplify.