Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data architect is designing a semantic model in Microsoft Fabric for a global retail company. The model contains a large 'Sales' fact table and several dimension tables. The architect wants to implement a solution that allows users to query sales data with high performance, even when performing complex aggregations and filtering over large date ranges, without impacting the source systems. Which approach should the architect prioritize?
- AImplement aggregations in the semantic model for key measures.
- BUse DirectQuery for all tables to ensure real-time data.
- CStore all data in a Data Lakehouse and connect via Direct Lake.
- DApply Row-Level Security (RLS) to all dimension tables.
Show answer & explanationAnswer & explanation
Correct answer: A. Implement aggregations in the semantic model for key measures.
Aggregations in the semantic model pre-calculate and store summarized data for common queries. This significantly improves performance for complex aggregations over large datasets, as the query engine can retrieve results from the pre-aggregated tables rather than processing raw data.
Why the other options are wrong
- B. DirectQuery would query the source system for every interaction, potentially impacting source performance and not inherently speeding up complex aggregations.
- C. While Data Lakehouse with Direct Lake offers performance benefits, aggregations directly within the semantic model are a more targeted solution for optimizing complex aggregations and filtering.
- D. RLS is for securing data access, not for improving query performance of aggregations.
Semantic Model Aggregations
Pre-calculated and stored summarized tables within a semantic model that are used to speed up queries by returning results from aggregated data when possible.
- Improves query performance for large datasets.
- Can be configured as Import, DirectQuery, or Dual storage.
- Requires careful design to cover common query patterns.
Memory trick: Aggregations speed up summaries, columns optimize storage, partitions refresh faster.