A data modeler has developed a Power BI model with a complex star schema, including a 'Sales' fact table and several dimension tables like 'Product', 'Customer', and 'Date'. The 'Sales' table has millions of rows. The model is experiencing slow query performance, especially when users filter by attributes from multiple dimension tables simultaneously. Which of the following is the most likely cause of this performance issue?
- AUsing too many calculated columns instead of measures.
- BHigh cardinality columns used in filters or visuals.
- CLack of appropriate indexes on the underlying data source tables.
- DInefficient cross-filter direction in model relationships.
Show answer & explanationAnswer & explanation
Correct answer: B. High cardinality columns used in filters or visuals.
High cardinality columns (columns with many unique values) significantly impact query performance when used in filters or visuals. Each distinct value requires memory and processing to manage the filter context. When multiple high-cardinality columns are filtered simultaneously, the number of combinations can explode, leading to slow query execution as the VertiPaq engine struggles to process the numerous filter contexts.
Why the other options are wrong
- A. While using too many calculated columns can increase model size and refresh time, it doesn't directly explain slow query performance when filtering by attributes from dimension tables, unless those calculated columns are also high cardinality and used in filters.
- C. Lack of indexes primarily affects data refresh/load times for imported data or DirectQuery performance, not necessarily DAX query performance within the VertiPaq engine for an already loaded model.
- D. Inefficient cross-filter direction (e.g., bidirectional relationships used unnecessarily) can indeed cause performance issues and ambiguity, but high cardinality columns are a more direct and common cause of slow filtering, especially across multiple dimensions in a star schema.
High Cardinality Impact
High cardinality refers to columns with a large number of unique values. In Power BI, using high-cardinality columns in filters, slicers, or visual axes can significantly degrade query performance and increase model size due to the VertiPaq engine needing to process many distinct values and their combinations.
- Many unique values in a column.
- Impacts memory footprint and query performance.
- Slows down filtering and visual interactions.
- Consider reducing cardinality (e.g., binning) if possible.
- Often seen with IDs, timestamps, or text fields.
Memory trick: Cardinality is king; too many unique values makes queries sing a slow song.