Microsoft Certified: Power BI Data Analyst AssociateModel the dataMedium
A data modeler is optimizing a Power BI model for a large manufacturing company. The model contains a 'Production' fact table with 'ProductionBatchID', 'ProductID', 'QuantityProduced', and 'ProductionDate'. The 'Products' dimension table contains 'ProductID', 'ProductName', 'Category', and 'WeightKg'. The relationship between 'Products' and 'Production' is one-to-many on 'ProductID'. The modeler notices that the 'WeightKg' column in the 'Products' table has many unique values (high cardinality), and reports filtering or aggregating by 'WeightKg' are slow. Which action should the data modeler take to improve performance related to 'WeightKg'?
- ASet the cross-filter direction of the relationship to 'Both'.
- BAdd 'WeightKg' as a calculated column to the 'Production' fact table.
- CCreate a new 'Weight Range' dimension table and relate it to 'Products'.
- DChange the data type of 'WeightKg' to text.
Show answer & explanationAnswer & explanation
Correct answer: C. Create a new 'Weight Range' dimension table and relate it to 'Products'.
High cardinality columns in dimension tables can lead to performance issues, especially if they are frequently used for filtering or grouping. Creating a 'Weight Range' dimension table (e.g., '0-5kg', '5-10kg') and relating it to the 'Products' table effectively reduces the cardinality of the 'Weight' attribute when used for analysis, improving performance.
Why the other options are wrong
- A. Setting the cross-filter direction to 'Both' generally harms performance and can introduce ambiguity; it doesn't address the high cardinality of the 'WeightKg' column.
- B. Adding 'WeightKg' to the fact table would simply push the high cardinality issue to the fact table, potentially increasing its size and not solving the core problem.
- D. Changing 'WeightKg' to a text data type would worsen performance and prevent numerical operations, as text columns consume more memory and are slower to process.
High Cardinality Dimension Optimization
Strategies to improve performance when a dimension column has many unique values. This often involves reducing the effective cardinality for analytical purposes, such as creating bins or ranges.
- High cardinality columns can increase model size and slow down queries.
- Binning or grouping into ranges reduces unique values for analytical use.
- Creating a new dimension table for ranges is a common solution.
Memory trick: For high cardinality, create ranges to simplify.