Composite Models: Scale to Billions of Rows

Power BI composite models with aggregations enable enterprise organizations to query billions of rows with sub-second response times by intelligently routing queries between pre-computed Import-mode aggregation tables and DirectQuery detail tables. This architecture pattern is the standard approach for large-scale analytical workloads where full data import is impractical due to volume, and pure DirectQuery is unacceptable due to latency. Our Power BI architecture team implements aggregation patterns for organizations processing 10 billion+ row fact tables across financial services, healthcare, retail, and manufacturing.

This guide covers aggregation design methodology, multi-level aggregation hierarchies, query routing internals, performance tuning, and production monitoring for composite models at enterprise scale.

How Aggregations Work in Power BI

Aggregations in Power BI work through a query routing mechanism. When a user interacts with a report, the Power BI engine analyzes the query and determines whether a pre-computed aggregation table can answer it. If yes, the query hits the fast Import-mode table. If not, the query falls through to the DirectQuery detail table.

Query routing decision flow:

``` User clicks slicer or visual -> Power BI engine parses DAX query -> Check: Do aggregation mappings cover all required columns? -> YES: Route to Import aggregation table (sub-second) -> NO: Route to DirectQuery detail table (2-10 seconds) -> Return results to visual ```

This routing is completely transparent to users and report developers. No DAX changes are required. The engine handles routing automatically based on aggregation mappings configured in the model.

Aggregation Design Methodology

Designing effective aggregations requires understanding your query patterns, data distribution, and user behavior.

Step 1: Analyze Query Patterns

Before creating aggregation tables, profile which queries users actually run. This prevents building aggregations nobody uses.

Tools for query analysis:

What to look for: - Most common grouping dimensions (Date grain, Product category, Region) - Most common aggregation functions (SUM, COUNT, AVERAGE, DISTINCTCOUNT) - Queries that hit DirectQuery and take longer than 3 seconds - Percentage of queries that could be answered by pre-aggregated data

Step 2: Define Aggregation Grain

The grain of your aggregation table determines which queries it can answer.

Grain selection examples:

Rule of thumb: Choose the finest grain that still achieves a 10x+ size reduction. Finer grain = more queries covered. Coarser grain = smaller table, faster queries, but more fallthrough to DirectQuery.

Step 3: Build the Aggregation Table

Create the aggregation table in your data source (data warehouse, lakehouse) or as a Power Query computed table.

Aggregation table design principles: - Include all dimension keys needed for common queries - Pre-compute SUM, COUNT, MIN, MAX for each measure - Include DISTINCTCOUNT only when specifically needed (most expensive aggregation) - Add a row count column for COUNT aggregation support - Maintain the aggregation table through ETL alongside your detail table

Example aggregation table DDL:

```sql CREATE TABLE Agg_Sales_Daily AS SELECT DateKey, ProductCategoryKey, RegionKey, CustomerSegmentKey, SUM(Revenue) as TotalRevenue, SUM(Cost) as TotalCost, SUM(Quantity) as TotalQuantity, COUNT(*) as TransactionCount, COUNT(DISTINCT CustomerKey) as UniqueCustomers FROM Sales_Detail GROUP BY DateKey, ProductCategoryKey, RegionKey, CustomerSegmentKey ```

Step 4: Configure Aggregation Mappings

In Power BI Desktop, configure the aggregation table to map to the detail table.

Mapping configuration:

After configuring mappings: - Set the aggregation table storage mode to Import - Set the detail table storage mode to DirectQuery - Set all shared dimension tables to Dual mode - Hide the aggregation table from report view (users should never interact with it directly)

Multi-Level Aggregation Hierarchies

For very large datasets, a single aggregation level may not be sufficient. Multi-level hierarchies provide additional acceleration.

Three-level aggregation example:

``` Level 1: Agg_Sales_Yearly (Import) - ~10K rows Covers: Annual executive summaries, YoY comparisons | Level 2: Agg_Sales_Monthly (Import) - ~500K rows Covers: Monthly trends, quarterly analysis, MTD calculations | Level 3: Agg_Sales_Daily (Import) - ~10M rows Covers: Daily analysis, weekly rollups, WoW comparisons | Detail: Sales_Detail (DirectQuery) - ~5B rows Covers: Transaction-level drilldown, ad-hoc detail queries ```

How multi-level routing works: The engine checks from the coarsest aggregation first. If Level 1 can answer the query, it stops there. Otherwise, it tries Level 2, then Level 3, and finally falls through to the detail table. Each level reduces the probability of hitting DirectQuery.

Aggregation hit rate targets by level:

Performance Monitoring and Tuning

Measuring Aggregation Effectiveness

DAX Studio aggregation analysis:

Connect DAX Studio to your Power BI model and execute queries while monitoring the Server Timings pane. Look for:

• VertiPaq SE queries only = Aggregation hit (Import mode served the query)
• DirectQuery events present = Aggregation miss (query fell through to source)

Systematic aggregation coverage analysis:

1. Extract all DAX queries from Performance Analyzer for each report page
2. Run each query in DAX Studio and check for DirectQuery events
3. Calculate aggregation hit rate: (queries with no DQ events) / (total queries)
4. For queries with DQ events, analyze which columns caused the miss
5. Adjust aggregation grain or add columns to cover more queries

Common Aggregation Miss Causes

DirectQuery Source Optimization

When queries fall through to DirectQuery, source database performance becomes critical.

Source-side optimizations:

• Create covering indexes for common query patterns
• Maintain up-to-date statistics on aggregated columns
• Implement materialized views at the database level as an additional caching layer
• Use columnstore indexes for analytical query patterns
• Monitor and optimize the queries Power BI generates (capture with database profiling tools)

**For detailed DirectQuery optimization, see our DirectQuery optimization guide.**

Aggregation Patterns for Specific Industries

Financial Services

Healthcare

Retail

Integration with Other Patterns

Aggregations work best when combined with other enterprise patterns:

• **Incremental refresh** — Apply incremental refresh to both aggregation and detail tables for efficient data loading
• **Composite models** — Aggregations are a core composite model pattern; see our composite models guide for broader architecture context
• **Star schema design** — Aggregations work most effectively on properly designed star schemas where dimension relationships are clean
• **DAX optimization** — Ensure DAX measures are written to be aggregation-friendly (avoid patterns that prevent agg hits)
• **Monitoring** — Track aggregation hit rates as a key performance indicator

Frequently Asked Questions

How do I know if I need aggregations? If your DirectQuery queries consistently take more than 3 seconds and your users query at a grain coarser than the detail table, aggregations will help. If queries are already fast or users need transaction-level detail for most queries, aggregations provide minimal benefit.

Can aggregations work with Direct Lake mode in Fabric? Direct Lake mode does not currently support user-defined aggregations because the VertiPaq engine reads Parquet files directly. However, you can create materialized aggregation tables in the lakehouse that Direct Lake reads as separate tables with explicit measures.

**How often should aggregation tables refresh?** Match the refresh frequency to user expectations. If users expect data from today, daily aggregation refresh is minimum. For real-time dashboards, consider streaming patterns instead of aggregations.

What happens if my aggregation table has stale data? Users querying at the aggregation grain see stale data. Users drilling to detail see current DirectQuery data. This inconsistency confuses users — keep aggregation refresh aligned with DirectQuery source updates.

Next Steps

Aggregations are the highest-impact performance optimization for large-scale Power BI deployments. Properly designed aggregations deliver 10-100x query speed improvements for the majority of user interactions. Our Power BI architecture team conducts aggregation design workshops that analyze your query patterns, design optimal aggregation hierarchies, and implement production-ready composite models. Contact us to accelerate your aggregation implementation.

**Related resources:** - Composite Models Guide - DAX Performance Optimization - DirectQuery Optimization - Power BI Architecture Services

Enterprise Implementation Best Practices

Deploying composite models with aggregations in production enterprise environments introduces architectural complexity that requires careful planning, rigorous testing, and ongoing operational discipline. Having implemented aggregation patterns for organizations processing 10 billion+ row datasets across financial services, healthcare, and manufacturing, these practices determine whether aggregations deliver the promised performance gains or create new operational headaches.

• **Profile query patterns for at least two weeks before designing aggregations.** Use DAX Studio Server Timings and Log Analytics to capture real user query patterns. Design aggregation grain and columns based on actual usage, not assumptions about how users interact with reports. Aggregations built on assumptions typically achieve 30-40% hit rates. Data-driven aggregation designs achieve 70-90% hit rates.
• Implement multi-level aggregation hierarchies for datasets exceeding 1 billion rows. A single aggregation level leaves too many queries falling through to DirectQuery. Build at minimum yearly, monthly, and daily aggregation levels. The incremental storage cost of multi-level aggregations (typically 1-5% of detail table size) is insignificant compared to the DirectQuery cost savings and performance improvement.
• Set all dimension tables to Dual storage mode. This is the single most common configuration mistake in composite model implementations. If dimension tables remain in DirectQuery mode while aggregation tables are Import mode, aggregation hits fail because the engine cannot join Import aggregations with DirectQuery dimensions. Dual mode enables dimension tables to participate in both Import and DirectQuery query paths.
• Monitor aggregation hit rates weekly during the first quarter. Deploy a monitoring report that tracks hit rates per aggregation level using DAX Studio trace data or capacity metrics. Set an alert for hit rates dropping below 60% — this indicates that user query patterns have shifted or new visuals bypass aggregation coverage. Adjust aggregation design iteratively based on hit rate telemetry.
• **Maintain aggregation ETL alongside detail table ETL.** Aggregation tables must refresh whenever the underlying detail data changes. Build aggregation table refresh into your data pipeline as a dependent step that runs immediately after detail table loads. Stale aggregation data creates inconsistencies where summary views show different numbers than detail drilldowns — this destroys user trust.
• Test DirectQuery fallthrough performance independently. When queries miss aggregations and fall through to DirectQuery, the source database must handle them efficiently. Create covering indexes, maintain statistics, and implement materialized views on the source side for common fallthrough query patterns. Aggregations do not eliminate DirectQuery requirements — they reduce the volume.
• **Document aggregation mappings in your governance framework.** Every aggregation table should have documented: grain definition, column mappings, expected hit rate targets, refresh schedule, and the queries it was designed to cover. Without documentation, future developers cannot maintain or optimize the aggregation architecture.
• **Plan capacity for both Import and DirectQuery workloads.** Composite models consume capacity for Import mode storage and refresh (aggregation tables) plus DirectQuery query processing (fallthrough queries). Size your Fabric capacity for the combined workload, not just one side.

Measuring Success and ROI

Aggregation implementations should deliver quantifiable performance improvements and cost savings. Track these metrics to validate the investment and identify optimization opportunities.

Performance metrics: - Aggregation hit rate: The primary success metric. Measure the percentage of user queries served by Import-mode aggregation tables versus DirectQuery fallthrough. Target 70-90% for a well-designed aggregation architecture. Each percentage point improvement reduces both query latency and source database load. - Average query response time: Compare P50 and P90 query response times before and after aggregation deployment. Properly designed aggregations deliver 10-100x improvement for covered queries — sub-second response for queries that previously took 5-15 seconds through DirectQuery. - DirectQuery source load reduction: Monitor query volume and CPU utilization on your source database. Effective aggregations reduce DirectQuery queries by 60-80%, freeing source database capacity for other workloads and potentially enabling downsizing. - Capacity cost efficiency: Calculate the cost per query before and after aggregations. Import-mode queries consume less capacity CU than DirectQuery queries. For organizations on Fabric F64+ capacity, aggregations typically reduce per-query cost by 40-70%, translating to either direct savings through capacity downsizing or the ability to support more users on existing capacity. - User adoption and engagement: Track report usage metrics through the Activity Log. Reports that respond in under 2 seconds consistently show 30-50% higher adoption rates than reports with 5+ second response times. Aggregations directly enable the sub-second response threshold that drives executive engagement.

For expert help implementing composite models and aggregations in your enterprise, contact our consulting team for a free assessment.``` User clicks slicer or visual -> Power BI engine parses DAX query -> Check: Do aggregation mappings cover all required columns? -> YES: Route to Import aggregation table (sub-second) -> NO: Route to DirectQuery detail table (2-10 seconds) -> Return results to visual ```

This routing is completely transparent to users and report developers. No DAX changes are required. The engine handles routing automatically based on aggregation mappings configured in the model.

Aggregation Design Methodology

Designing effective aggregations requires understanding your query patterns, data distribution, and user behavior.

Step 1: Analyze Query Patterns

Before creating aggregation tables, profile which queries users actually run. This prevents building aggregations nobody uses.

Tools for query analysis:

What to look for: - Most common grouping dimensions (Date grain, Product category, Region) - Most common aggregation functions (SUM, COUNT, AVERAGE, DISTINCTCOUNT) - Queries that hit DirectQuery and take longer than 3 seconds - Percentage of queries that could be answered by pre-aggregated data

Step 2: Define Aggregation Grain

The grain of your aggregation table determines which queries it can answer.

Grain selection examples:

Rule of thumb: Choose the finest grain that still achieves a 10x+ size reduction. Finer grain = more queries covered. Coarser grain = smaller table, faster queries, but more fallthrough to DirectQuery.

Step 3: Build the Aggregation Table

Create the aggregation table in your data source (data warehouse, lakehouse) or as a Power Query computed table.

Aggregation table design principles: - Include all dimension keys needed for common queries - Pre-compute SUM, COUNT, MIN, MAX for each measure - Include DISTINCTCOUNT only when specifically needed (most expensive aggregation) - Add a row count column for COUNT aggregation support - Maintain the aggregation table through ETL alongside your detail table

Example aggregation table DDL:

```sql CREATE TABLE Agg_Sales_Daily AS SELECT DateKey, ProductCategoryKey, RegionKey, CustomerSegmentKey, SUM(Revenue) as TotalRevenue, SUM(Cost) as TotalCost, SUM(Quantity) as TotalQuantity, COUNT(*) as TransactionCount, COUNT(DISTINCT CustomerKey) as UniqueCustomers FROM Sales_Detail GROUP BY DateKey, ProductCategoryKey, RegionKey, CustomerSegmentKey ```

Step 4: Configure Aggregation Mappings

In Power BI Desktop, configure the aggregation table to map to the detail table.

Mapping configuration:

After configuring mappings: - Set the aggregation table storage mode to Import - Set the detail table storage mode to DirectQuery - Set all shared dimension tables to Dual mode - Hide the aggregation table from report view (users should never interact with it directly)

Multi-Level Aggregation Hierarchies

For very large datasets, a single aggregation level may not be sufficient. Multi-level hierarchies provide additional acceleration.

Three-level aggregation example:

``` Level 1: Agg_Sales_Yearly (Import) - ~10K rows Covers: Annual executive summaries, YoY comparisons | Level 2: Agg_Sales_Monthly (Import) - ~500K rows Covers: Monthly trends, quarterly analysis, MTD calculations | Level 3: Agg_Sales_Daily (Import) - ~10M rows Covers: Daily analysis, weekly rollups, WoW comparisons | Detail: Sales_Detail (DirectQuery) - ~5B rows Covers: Transaction-level drilldown, ad-hoc detail queries ```

How multi-level routing works: The engine checks from the coarsest aggregation first. If Level 1 can answer the query, it stops there. Otherwise, it tries Level 2, then Level 3, and finally falls through to the detail table. Each level reduces the probability of hitting DirectQuery.

Aggregation hit rate targets by level:

Performance Monitoring and Tuning

Measuring Aggregation Effectiveness

DAX Studio aggregation analysis:

Connect DAX Studio to your Power BI model and execute queries while monitoring the Server Timings pane. Look for:

• VertiPaq SE queries only = Aggregation hit (Import mode served the query)
• DirectQuery events present = Aggregation miss (query fell through to source)

Systematic aggregation coverage analysis:

1. Extract all DAX queries from Performance Analyzer for each report page
2. Run each query in DAX Studio and check for DirectQuery events
3. Calculate aggregation hit rate: (queries with no DQ events) / (total queries)
4. For queries with DQ events, analyze which columns caused the miss
5. Adjust aggregation grain or add columns to cover more queries

Common Aggregation Miss Causes

DirectQuery Source Optimization

When queries fall through to DirectQuery, source database performance becomes critical.

Source-side optimizations:

• Create covering indexes for common query patterns
• Maintain up-to-date statistics on aggregated columns
• Implement materialized views at the database level as an additional caching layer
• Use columnstore indexes for analytical query patterns
• Monitor and optimize the queries Power BI generates (capture with database profiling tools)

**For detailed DirectQuery optimization, see our DirectQuery optimization guide.**

Aggregation Patterns for Specific Industries

Financial Services

Healthcare

Retail

Integration with Other Patterns

Aggregations work best when combined with other enterprise patterns:

• **Incremental refresh** — Apply incremental refresh to both aggregation and detail tables for efficient data loading
• **Composite models** — Aggregations are a core composite model pattern; see our composite models guide for broader architecture context
• **Star schema design** — Aggregations work most effectively on properly designed star schemas where dimension relationships are clean
• **DAX optimization** — Ensure DAX measures are written to be aggregation-friendly (avoid patterns that prevent agg hits)
• **Monitoring** — Track aggregation hit rates as a key performance indicator

Frequently Asked Questions

How do I know if I need aggregations? If your DirectQuery queries consistently take more than 3 seconds and your users query at a grain coarser than the detail table, aggregations will help. If queries are already fast or users need transaction-level detail for most queries, aggregations provide minimal benefit.

Can aggregations work with Direct Lake mode in Fabric? Direct Lake mode does not currently support user-defined aggregations because the VertiPaq engine reads Parquet files directly. However, you can create materialized aggregation tables in the lakehouse that Direct Lake reads as separate tables with explicit measures.

**How often should aggregation tables refresh?** Match the refresh frequency to user expectations. If users expect data from today, daily aggregation refresh is minimum. For real-time dashboards, consider streaming patterns instead of aggregations.

What happens if my aggregation table has stale data? Users querying at the aggregation grain see stale data. Users drilling to detail see current DirectQuery data. This inconsistency confuses users — keep aggregation refresh aligned with DirectQuery source updates.

Next Steps

Aggregations are the highest-impact performance optimization for large-scale Power BI deployments. Properly designed aggregations deliver 10-100x query speed improvements for the majority of user interactions. Our Power BI architecture team conducts aggregation design workshops that analyze your query patterns, design optimal aggregation hierarchies, and implement production-ready composite models. Contact us to accelerate your aggregation implementation.

**Related resources:** - Composite Models Guide - DAX Performance Optimization - DirectQuery Optimization - Power BI Architecture Services