A data modeler is optimizing a semantic model in Microsoft Fabric. The model contains a 'Sales' fact table and several dimension tables. The modeler observes that queries involving complex calculations across multiple dimensions are performing slowly. The underlying data is large, but the relationships between tables are correctly defined. Which optimization technique, if applied to the fact table, can significantly improve the performance of these complex multi-dimensional queries without altering the source data or redesigning the entire model?
- AImplement calculation groups for common measure patterns.
- BCreate manual aggregations on the fact table.
- CChange the cross-filter direction of all relationships to 'Both'.
- DAdd more columns to the dimension tables.
Show answer & explanationAnswer & explanation
Correct answer: B. Create manual aggregations on the fact table.
Manual aggregations (also known as user-defined aggregations) involve creating summarized tables within the semantic model and configuring them to be used by the query engine. For complex queries across multiple dimensions, pre-calculating and storing aggregated results in these smaller, faster tables can dramatically improve performance. The query engine will automatically redirect queries to the aggregate tables when possible, falling back to the detailed fact table for non-aggregated queries.
Why the other options are wrong
- A. Calculation groups optimize measures (e.g., time intelligence) but don't directly optimize the performance of queries involving complex joins and aggregations across large fact tables and multiple dimensions.
- C. Changing all relationships to 'Both' (bi-directional) can introduce ambiguity and performance issues, and is generally not an optimization for complex multi-dimensional queries in a large fact table.
- D. Adding more columns to dimension tables might improve usability but would likely increase model size and not directly address complex query performance on the fact table.
Manual Aggregations
Manual aggregations (or user-defined aggregations) in Microsoft Fabric semantic models involve creating and configuring summary tables to store pre-calculated, aggregated data. The query engine automatically uses these aggregate tables for faster query responses when applicable, transparently falling back to detailed tables for other queries.
- User-defined summary tables within the semantic model.
- Significantly improves performance for aggregated queries.
- Requires explicit configuration of aggregation rules.
- Provides a balance between performance and detail access.
Memory trick: For complex queries, manually aggregate the data, it's a performance upgrade.