Microsoft Fabric Automatic Aggregations: Optimizing DirectQuery Semantic Models
Automatic aggregations are a Microsoft Fabric and Power BI capability for improving query performance in supported DirectQuery semantic models. The feature uses machine learning to analyze query patterns and automatically create an in-memory aggregations cache that can answer qualifying report queries without sending every request back to the underlying data source.
This matters because DirectQuery models keep data in the source system. Every report interaction can generate DAX queries that are translated into source queries. On large analytical models, repeated grouped queries against detailed fact tables can place heavy load on the source and increase visual response time.
Automatic aggregations reduce that pressure by allowing frequently used aggregated query results to be served from an automatically maintained in-memory cache. They do not replace good semantic model design, source tuning, DirectQuery guidance, or Direct Lake optimization. They are one performance feature for supported DirectQuery scenarios.
Scope correction: This article focuses on Automatic Aggregations for DirectQuery semantic models. Direct Lake is a separate Fabric storage mode with its own performance guidance, including Delta table health, V-Order optimization, file layout, semantic model design, and DAX efficiency.
- What Automatic Aggregations Are
- Where They Fit in Fabric and Power BI
- How Automatic Aggregations Work
- Requirements and Supported Scenarios
- Automatic vs User-Defined Aggregations
- Configure Automatic Aggregations
- Monitor and Manage the Model
1 What Automatic Aggregations Are Beginner
Automatic aggregations are system-managed aggregations created for supported DirectQuery semantic models. Microsoft describes the feature as self-training and self-optimizing. Instead of requiring a model designer to manually create aggregation tables and map them to detail tables, the service analyzes query activity and creates aggregation structures that can improve report query performance.
The purpose is straightforward: when a report visual asks for a summarized result that can be answered from the in-memory aggregations cache, the model can return that result without sending the full query back to the DirectQuery source.
Examples of query patterns that commonly benefit from aggregation concepts include:
- Sales amount by month
- Order count by product category
- Revenue by region
- Inventory quantity by warehouse
- Customer count by quarter
The exact aggregations created by the service are managed by Power BI. They are not manually designed tables that a report author names and maintains like standard model tables.
Important: Microsoft documentation states that automatic aggregations are supported for DirectQuery mode models. Composite models with both Import tables and DirectQuery connections are supported, but automatic aggregations apply to the DirectQuery connection only.
2 Where They Fit in Fabric and Power BI Beginner
Fabric and Power BI include multiple ways to improve semantic model performance. Automatic aggregations should be understood as one option, not the only option.
| Performance Area | Main Purpose | Where It Applies |
|---|---|---|
| Import mode | Loads data into memory for fast query performance | Power BI semantic models |
| DirectQuery | Queries the source system without importing detail data | Supported external and Fabric sources |
| Automatic aggregations | Creates and manages an in-memory aggregations cache | Supported DirectQuery semantic models |
| User-defined aggregations | Allows a model designer to manually create aggregation behavior | Power BI semantic models |
| Direct Lake | Loads Delta table data from OneLake into memory without a traditional import refresh | Microsoft Fabric semantic models |
Direct Lake is separate from automatic aggregations. Microsoft documents Direct Lake as a semantic model table storage mode in Fabric that is optimized for loading large volumes of data from Delta tables in OneLake into memory. Direct Lake performance guidance focuses on semantic model design, query complexity, Delta table layout, V-Order optimization, Parquet file size, row groups, and Delta log update patterns.
Practical rule: Use Automatic Aggregations when you are optimizing supported DirectQuery semantic models. Use Direct Lake guidance when your model uses Direct Lake tables over OneLake Delta data.
3 How Automatic Aggregations Work Intermediate
Automatic aggregations rely on query history, training, refresh, and ongoing optimization.
1. Query log
Power BI tracks model and report queries in a query log. Microsoft documentation states that Power BI maintains seven days of query log data for each model. This query log is secure and is not visible to users or through the XMLA endpoint.
2. Training
During the first scheduled model refresh operation for the selected frequency, Power BI initiates a training operation. The training operation evaluates the query log and determines which aggregations should be included in the in-memory aggregations cache.
3. In-memory aggregations cache
When the system identifies useful aggregation patterns, it creates, updates, or drops in-memory aggregation tables. Queries that can be answered from that cache can avoid a round trip to the DirectQuery data source.
4. Ongoing adjustment
As report usage changes, automatic aggregations can continue adjusting the aggregation strategy. Microsoft describes the feature as self-training and self-optimizing because the service uses query pattern analysis to determine which aggregations should be prioritized.
Do not document this as instant behavior. Automatic aggregations require query activity, training, and scheduled refresh operations. A newly created model with no meaningful query history is not the same as a trained model with established report usage.
4 Requirements and Supported Scenarios Intermediate
Automatic aggregations are not available for every model, license, data source, or storage mode.
| Area | Microsoft documented requirement or support boundary |
|---|---|
| Model mode | Supported for DirectQuery mode models. Composite models with Import and DirectQuery are supported for the DirectQuery connection only. |
| Plans | Supported for Microsoft Fabric capacity subscriptions (F-SKU, F2 and above), Power BI Premium per capacity (P-SKU, being retired in favour of F-SKUs), Power BI Premium per user (PPU), and Power BI Embedded (A-SKU). Microsoft is consolidating to F-SKUs and retiring P-SKUs. New deployments should target Fabric F-SKU capacity. |
| Permissions | The semantic model owner can enable and configure the feature. Workspace admins can take over as owner. |
| Configuration location | Configured in semantic model settings. |
| Power BI Desktop | Power BI Desktop does not create system-generated aggregation tables. |
Supported data sources documented by Microsoft
- Azure SQL Database
- Azure Synapse Dedicated SQL pool
- SQL Server 2019 or later
- Google BigQuery
- Snowflake
- Databricks
- Amazon Redshift
Do not enable Automatic Aggregations without checking the model and data source design. Microsoft documents specific limitations around single sign-on, hybrid tables, calculated columns, dynamic M query parameters, and enhanced metadata.
Capacity licensing update: Microsoft is retiring Power BI Premium per capacity P-SKUs in favour of Fabric F-SKUs. If planning a new deployment or renewal, target Fabric capacity (F2 or higher). Existing P-SKU customers continue to be supported during the transition period. Verify current licensing status with Microsoft before committing to a capacity plan.
5 Automatic vs User-Defined Aggregations Intermediate
Automatic aggregations and user-defined aggregations are related but not the same.
| Feature | Automatic Aggregations | User-Defined Aggregations |
|---|---|---|
| Design approach | Machine learning driven | Manually designed by model author |
| Skill requirement | Lower setup complexity | Requires modeling and query optimization knowledge |
| Management | System-managed | Designer-managed |
| Adaptation | Adjusts based on query patterns | Static unless manually changed |
| Best fit | Changing or hard-to-predict report workloads | Known business rollups and predictable query patterns |
Microsoft documentation states that automatic aggregations are built on the existing user-defined aggregations infrastructure. It is possible to use both user-defined and automatic aggregations together in the same model.
A skilled modeler may still use user-defined aggregations for known business patterns, while automatic aggregations can optimize additional DirectQuery patterns that do not hit those user-defined aggregation tables.
6 Configure Automatic Aggregations Intermediate
Microsoft documents configuration as a semantic model settings task.
- Open the semantic model settings.
- Expand Scheduled refresh and performance optimization.
- Switch Automatic aggregations training to On.
- Configure refresh frequency and time zone.
- Add one or more scheduled refresh times.
- Allow several training and refresh iterations to run.
- Return to semantic model settings to fine-tune the percentage of report queries that use the in-memory aggregations cache.
If the Automatic Aggregations training switch is unavailable, Microsoft documentation says to ensure data source credentials are configured and that the user is signed in. If refresh schedule controls are disabled, verify the data source configuration, gateway connection when required, and data source credentials.
Configuration is not just an on/off switch. The feature depends on scheduled refresh and training. The first training and refresh operations begin creating the aggregation framework, and the model can continue tuning over time.
7 Monitor and Manage the Model Intermediate
Monitoring should focus on whether report queries are being satisfied by the in-memory aggregations cache and whether source system load is reduced.
Microsoft documents Azure Log Analytics as a way to analyze automatic aggregation success rate when Power BI is configured with an Azure Log Analytics account. This can help determine whether report queries are answered from the in-memory cache.
| What to Monitor | Why It Matters |
|---|---|
| Report visual response time | Shows whether users experience faster reports |
| Query cache hit behavior for aggregations | Shows whether queries are satisfied from the aggregations cache |
| DirectQuery source workload | Shows whether backend query pressure is reduced |
| Refresh history | Shows training warnings, refresh timing, or skipped tables |
| Capacity load | Shows whether optimization helps or whether capacity sizing remains a bottleneck |
System-managed aggregation tables
Microsoft documentation states that models with automatic aggregations enabled contain unique system-generated aggregation tables. These tables are not visible to users in reporting tools. They are visible through the XMLA endpoint by using tools with Analysis Services client libraries version 19.22.5 or higher.
Automatic aggregation tables are identified by the SystemManaged table property in the Tabular Object Model. Microsoft also warns not to delete or modify system-generated aggregation tables because Power BI fully manages them.
Do not manually reference or modify system-generated aggregation tables. Microsoft fully manages these objects. They are not reporting tables with predictable user-visible names and should not be referenced in report design, model documentation, or downstream tooling. Deleting or modifying them can cause degraded performance or broken aggregation behavior.
8 Workshop: Enable Automatic Aggregations Advanced
This workshop is written as a guided lab for a supported DirectQuery semantic model. Use a development workspace and a test model before enabling the feature in production.
Prerequisites
- A supported Power BI or Fabric capacity scenario
- A supported DirectQuery semantic model
- Semantic model Owner permissions
- Configured data source credentials
- A report that generates repeatable query activity
Step 1: Confirm DirectQuery mode
Verify that the target semantic model uses DirectQuery for the tables you want to optimize. If the model is composite, confirm which tables are DirectQuery and which tables are Import.
Automatic aggregations apply to the DirectQuery connection only. Do not treat Import tables, Direct Lake tables, and DirectQuery tables as the same optimization target.
Step 2: Create baseline report interactions
Use existing reports or create a test report that produces common grouped analytical queries.
- Total Sales by Month
- Order Count by Product Category
- Revenue by Region
- Customer Count by Quarter
Record baseline visual load behavior and source system query load before enabling the feature.
Step 3: Enable Automatic Aggregations training
- Open semantic model settings.
- Expand Scheduled refresh and performance optimization.
- Set Automatic aggregations training to On.
- Configure scheduled refresh frequency, time zone, and one or more refresh times.
- Save the settings.
Step 4: Allow training and refresh iterations
Allow several scheduled training and refresh operations to run. Microsoft documentation states that after several iterations, you can return to semantic model settings and fine-tune the percentage of report queries that use the in-memory aggregations cache.
Step 5: Validate behavior
Compare the post-training behavior against the baseline.
| Validation Point | Expected Direction |
|---|---|
| Report visual response time | Should improve for qualifying query patterns |
| Backend DirectQuery workload | Should reduce when queries are served from cache |
| Refresh history | Review warnings and skipped table messages |
| Unsupported query patterns | May still go to the source system |
Step 6: Fine-tune
Use semantic model settings to adjust the percentage of report queries returned from the in-memory aggregations cache. Monitor whether the change improves report performance without introducing unacceptable refresh or capacity impact.
What this workshop teaches: Automatic aggregations are workload-driven. You need query history, training, refresh, validation, and tuning. The goal is not to prove every query hits an aggregation. The goal is to improve the percentage of qualifying report queries that can be answered from the in-memory aggregations cache.
9 Best Practices Intermediate
- Start with model design. Automatic aggregations do not fix poor relationships, weak measures, or inefficient report design.
- Use clear dimensional modeling. DirectQuery and aggregation scenarios benefit from models that are easy for the engine to reason about.
- Keep fact tables in DirectQuery where appropriate. Microsoft recommends setting dimension tables to Dual mode and leaving fact tables in DirectQuery mode to further optimize performance.
- Use scheduled refresh intentionally. Aggregation cache freshness depends on refresh behavior.
- Review refresh history. Training warnings can indicate skipped tables, unsupported patterns, or data source issues.
- Monitor the source system. The purpose is not only faster visuals, but also reduced load on backend data sources.
- Separate Direct Lake tuning from Automatic Aggregations. Direct Lake performance depends on Delta table optimization, V-Order, Parquet file layout, semantic model design, and query complexity.
10 Limitations and Cautions Intermediate
Microsoft documents several important limitations and cautions.
| Limitation or Caution | What It Means |
|---|---|
| Dynamic M Query Parameters | Aggregations do not support Dynamic M Query Parameters. |
| Training load | Initial training queries can generate significant load on the data warehouse. |
| Data freshness | Aggregations stored in the in-memory cache may not reflect the most recent source data until refresh occurs. |
| Power BI Pro | Automatic aggregations are not available with Power BI Pro. |
| Azure Analysis Services / SSAS | Automatic aggregations are not available with Azure Analysis Services or SQL Server Analysis Services. |
| PBIX download | Microsoft states that Power BI does not support downloading models with automatic aggregations enabled. |
| External tables in Azure Synapse Analytics | Automatic aggregations with external tables in Azure Synapse Analytics are not supported. |
| Enhanced metadata | Models must use enhanced metadata. |
| Single sign-on with security controls | Microsoft warns not to enable automatic aggregations when DirectQuery data sources use SSO with dynamic views or security controls that limit data per user. |
| Hybrid-only models | Microsoft says not to enable automatic aggregations if the model contains only hybrid tables. |
| Calculated columns | Calculated columns are not considered for automatic aggregations. |
| Power BI Desktop | Power BI Desktop does not create system-generated aggregation tables. |
| Model metadata changes | Query performance might degrade after model metadata changes until the next training process is triggered. |
Do not manually modify system-generated aggregation tables. Microsoft states that Power BI fully manages these objects and that deleting or modifying them can cause degraded performance.
What changed from older guidance
Older articles often described automatic aggregations as though they were a general Fabric optimization feature for both DirectQuery and Direct Lake models. That wording is too broad. Current Microsoft documentation positions Automatic Aggregations as a DirectQuery semantic model optimization feature.
Some earlier articles described automatic aggregations as producing tables with readable names that model authors could inspect and reference. This is inaccurate. Microsoft documents system-managed aggregation tables as internal objects that Power BI fully controls. They are not normal reporting tables, their internal naming is not documented for user reference, and they should not be modified.
The accurate modern position is:
- Automatic Aggregations optimize supported DirectQuery semantic models.
- The feature uses query logs, training, refresh, and an in-memory aggregations cache.
- System-generated aggregation tables are managed by Power BI.
- User-defined aggregations remain a separate supported design option.
- Direct Lake has separate performance guidance and should not be presented as the same feature.
References
- Microsoft Learn: Automatic aggregations overview
- Microsoft Learn: Configure automatic aggregations
- Microsoft Learn: User-defined aggregations in Power BI
- Microsoft Learn: DirectQuery model guidance in Power BI Desktop
- Microsoft Learn: DirectQuery in Power BI
- Microsoft Learn: Direct Lake overview
- Microsoft Learn: Understand Direct Lake query performance
Note on Fabric documentation: Microsoft Fabric features evolve rapidly. Supported data sources, capacity SKU requirements, and configuration steps can change between releases. Always verify against current Microsoft Learn documentation before implementing in production, particularly regarding F-SKU capacity requirements and any newly supported data sources.
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.


