A data engineer is designing a semantic model in Microsoft Fabric. The model needs to contain a complete history of sales transactions, which is a very large dataset. However, reports primarily focus on aggregated sales metrics (e.g., total sales by month, average daily sales). Users occasionally need to drill down to individual transactions, but this is less frequent. The goal is to optimize query performance for common aggregated reports while still allowing drill-down to detailed data. Which feature should the data engineer implement?
- AUse Row-Level Security (RLS) to restrict detailed access.
- BImplement automatic aggregations.
- CCreate a separate, smaller semantic model for aggregated data.
- DSet all tables to DirectQuery mode.
Show answer & explanationAnswer & explanation
Correct answer: B. Implement automatic aggregations.
Automatic aggregations in Microsoft Fabric (and Power BI) allow you to pre-calculate summarized data and store it, often in Import mode, within the same semantic model. When a query comes in, if it can be answered by the aggregation, it uses the faster aggregated data. If a drill-down to detail is required, the query falls back to the underlying detailed data (which can be in DirectQuery or Direct Lake mode). This provides optimized performance for common aggregated queries while retaining access to detail.
Why the other options are wrong
- A. RLS is for security, not performance optimization for aggregated queries. It filters rows, not pre-calculates summaries.
- C. Creating a separate model adds complexity, maintenance overhead, and data duplication, which is less efficient than using automatic aggregations within a single model.
- D. DirectQuery mode would push all queries to the source, potentially making aggregated queries slower than using cached aggregations and not providing the desired performance optimization for summaries.
Automatic Aggregations
Automatic aggregations in Microsoft Fabric semantic models optimize query performance by automatically creating and managing in-memory aggregate tables. Queries that can be answered by these aggregates are resolved faster, while drill-down queries seamlessly use the underlying detailed data.
- Improves query performance for aggregated data.
- Automatically generated and optimized by Fabric/Power BI.
- Seamlessly combines aggregated and detailed data access.
- Reduces load on the underlying data source.
Memory trick: Optimize queries by aggregating automatically, then drill down when needed, magically.