A data modeler is optimizing a Power BI model with a large 'Sales' fact table and several dimension tables. The model includes a 'Geography' dimension table with 'Country', 'State', and 'City' columns. The modeler observes that queries involving 'City' are significantly slower than those involving 'Country' or 'State'. The 'City' column has a very high number of unique values. Which modeling technique is most likely to improve query performance related to the 'City' column without losing analytical capability?
- ACreate a hierarchy in the 'Geography' table: 'Country' -> 'State' -> 'City'.
- BMark the 'City' column as 'Hide in report view' to prevent direct use.
- CSplit the 'Geography' table into separate 'Country', 'State', and 'City' tables, linked by IDs.
- DImplement a bi-directional cross-filter on the relationship between 'Sales' and 'Geography' tables.
Show answer & explanationAnswer & explanation
Correct answer: A. Create a hierarchy in the 'Geography' table: 'Country' -> 'State' -> 'City'.
Creating a hierarchy allows Power BI to optimize how data is processed and presented. When users interact with the hierarchy, Power BI can aggregate data at higher levels (Country, State) first, then drill down to 'City' as needed, reducing the initial load on the highly cardinal 'City' column. This improves performance without losing the ability to analyze by city.
Why the other options are wrong
- B. Hiding the column prevents its use, which removes analytical capability, not just improves performance.
- C. Splitting a single dimension table into multiple tables for hierarchical attributes is generally not a best practice in a star schema. It adds complexity and doesn't inherently solve high cardinality performance issues better than a hierarchy within a single dimension.
- D. Bi-directional relationships can often *reduce* performance due to increased query complexity, especially with large tables, as they force filter propagation in both directions. They are typically used only when strictly necessary for specific filtering scenarios.
Dimension Hierarchies
A dimension hierarchy organizes related columns into a logical drill-down path. It optimizes how Power BI processes queries, especially for high-cardinality attributes, by allowing aggregation at higher levels first.
- Improves user experience for navigation.
- Enhances query performance for drill-down scenarios.
- Defined within the dimension table in the model view.
Memory trick: Too many unique things slow you down, organize them into a ladder.