A data engineer is optimizing a large semantic model in Microsoft Fabric. The model contains a 'Sales' fact table with billions of rows, and users frequently query aggregated sales data (e.g., total sales by month, total sales by product category). However, drill-through to individual transaction details is also occasionally required. The current setup, using DirectQuery for the fact table, is too slow for aggregated queries. Which strategy offers the best balance between fast aggregated queries and access to granular data without importing the entire fact table?
- AImplement manual aggregations on the 'Sales' table.
- BApply Row-Level Security (RLS) to the 'Sales' table.
- CUse DirectQuery for the 'Sales' table and create a separate Import mode semantic model for aggregates.
- DSet the 'Sales' table to Import mode.
Show answer & explanationAnswer & explanation
Correct answer: A. Implement manual aggregations on the 'Sales' table.
Manual aggregations allow the creation of smaller, pre-summarized tables (in Import mode) that can be used by the semantic model engine to answer aggregated queries quickly. The original large 'Sales' table can remain in DirectQuery mode for drill-through to granular details when needed, providing the desired balance of performance and detail access.
Why the other options are wrong
- B. RLS is for security, not for performance optimization of aggregated queries.
- C. Creating a separate semantic model for aggregates adds complexity and doesn't allow seamless drill-through from the aggregated view to the granular DirectQuery data within a single model.
- D. Importing billions of rows would be memory-intensive, slow to refresh, and may exceed semantic model capacity limits, making it impractical.
Manual Aggregations
Manual aggregations in Microsoft Fabric semantic models involve creating pre-summarized tables (often in Import mode) that are used by the query engine to accelerate aggregated queries on large DirectQuery or Dual mode fact tables, while still allowing access to granular data.
- Optimizes performance for large fact tables.
- Combines benefits of Import (for aggregates) and DirectQuery (for detail).
- Requires explicit definition of summary tables and mapping rules.
- Query engine automatically rewrites queries to use aggregations.
Memory trick: Aggregates for Speed, Direct for Detail!