Power BI Composite Models + Aggregations: Enterprise Pattern for 100B+ Row Semantic Models

TL;DR

• Power BI composite models combine multiple data sources and storage modes (Import + DirectQuery + DirectLake) within a single semantic model. For enterprise scale (100B+ rows or multi-source analytics), composite models are the architectural answer when pure Import or pure DirectQuery is insufficient.
• Aggregation tables complement composite models by providing pre-summarized data at common query grains. The Power BI engine automatically routes appropriate queries to the aggregation.
• The combination — composite model with Import-mode aggregations over DirectQuery/DirectLake detail — provides Import-mode-like performance on aggregate queries while preserving detail-level drill-through capability.
• For Fortune 500 deployments with very large fact tables, multi-source analytics, or hybrid hot/cold data patterns, composite models with aggregations are the standard architecture.
• This guide details the architecture, the implementation patterns, and the EPC Group framework for enterprise-scale composite models.

Executive Summary

A Fortune 500 financial-services enterprise we work with maintains a customer-transaction semantic model with 100 billion rows of transactional detail spanning 12 years of history. Pure Import mode is impractical (memory pressure, refresh time). Pure DirectQuery is too slow for executive dashboards. The architectural answer: composite model with Import-mode aggregations over DirectLake detail. Executives query the aggregations and get sub-second response; analysts drill through to the detail when needed.

This guide details the composite model + aggregations pattern for enterprise scale.

The Composite Model Pattern

A composite Power BI semantic model combines:

• Import-mode tables loaded into the Vertipaq in-memory engine.
• DirectQuery tables querying the source on each query.
• DirectLake tables reading directly from Delta files in OneLake.

The combinations work together. A query that touches multiple tables across storage modes is resolved by the Power BI engine, which orchestrates the appropriate data access for each table.

The Aggregation Pattern

An aggregation table is a pre-summarized version of a fact table at a defined grain (typically date × dimension):

• Aggregation table contains far fewer rows than the detail (50,000 rows aggregating 5 billion).
• Aggregation lives in the same semantic model.
• The Power BI engine recognizes when a query can be answered from the aggregation and routes accordingly.
• When the query requires detail-level granularity, the engine falls through to the detail table.

The Combination

The standard enterprise pattern combines composite mode with aggregations:

• Detail table: DirectLake mode over Delta tables in OneLake. Contains all detail rows. Sub-second access for individual queries.
• Aggregation table: Import mode. Pre-summarized at the executive-dashboard grain. Loaded entirely into memory for guaranteed sub-second response.
• Dimension tables: Import mode. Modest size, loaded once.
• Composite-model configuration: Aggregation precedence configured so the engine routes appropriately.

The result: Executive dashboards hit the aggregation (sub-second). Analyst drill-through hits the detail (also fast on DirectLake). Memory consumption is bounded by the aggregation + dimensions, not the full detail.

Implementation Patterns

Pattern A: Single-Source Enterprise Fact

For a single 100B+ row fact table with executive dashboards:

1. Fact table in DirectLake mode over a Delta lakehouse table.
2. Aggregation table in Import mode at executive-dashboard grain.
3. Standard dimensions in Import mode.
4. Aggregation precedence configured.

Pattern B: Multi-Source Composite

For multi-source analytics combining, e.g., on-premises SQL Server and Azure Data Lake:

1. SQL Server tables in DirectQuery (for fresh data from operational system).
2. Lake tables in DirectLake (for analytical data).
3. Reference data in Import (for performance).
4. Standard dimensions in Import.

Pattern C: Hot/Cold Hybrid

For workloads with substantial current-period activity and infrequent historical access:

1. Current-period detail in Import (entirely in memory).
2. Historical detail in DirectQuery or DirectLake (queried on demand).
3. Composite-model configuration handles the time-based routing.

Aggregation Design

Choosing aggregation grain

The aggregation grain should match the dominant query pattern:

• For executive dashboards filtering by year, month, region, product category: aggregation at date × region × category grain.
• For mid-management dashboards filtering by smaller dimensions: aggregation at finer grain.
• For multi-dashboard support: multiple aggregations at different grains, with precedence configured.

Aggregation refresh

Aggregation tables in Import mode refresh on schedule. The refresh aggregates the detail data to the aggregation grain. Common refresh cadences:

• Daily refresh for slow-moving analytical data.
• Hourly refresh for operational analytics.
• Sub-hourly with incremental refresh for time-sensitive use cases.

Aggregation validation

The Power BI Performance Analyzer shows whether queries are using the aggregation or falling through to detail. Periodic validation confirms aggregation use; queries falling through unexpectedly indicate the aggregation grain may need adjustment.

Capacity Sizing Considerations

Composite models with aggregations have specific capacity-sizing characteristics:

• Aggregation table memory is bounded; sizing the aggregation rows × column count determines memory footprint.
• Dimension memory is typically modest.
• DirectLake column-segment cache consumes capacity for queries that hit the detail.
• DirectQuery resource use depends on source-system performance.

EPC Group's sizing approach: estimate the working-set for aggregations + dimensions in memory; estimate the DirectLake/DirectQuery resource consumption based on detail-query frequency; combine for the F-SKU capacity recommendation.

Common Pitfalls

1. Skipping the aggregation table. Pure DirectQuery on a 100B-row fact is slow for executive use.
2. Wrong aggregation grain. An aggregation that doesn't match the query pattern doesn't help.
3. Over-engineered composite mode. Pure Import on a moderate-size fact may be simpler than composite.
4. Refresh strategy gaps. Aggregation refresh failures break executive dashboards; monitor aggressively.
5. Mixing storage modes without reason. Each storage mode has a purpose; combinations should be deliberate.
6. Ignoring DirectQuery query reduction settings. DirectQuery has settings that affect query generation; default settings are often suboptimal.

Frequently Asked Questions

What is a Power BI composite model?

A Power BI composite model combines multiple data sources and storage modes (Import + DirectQuery + DirectLake) within a single semantic model. The Power BI engine orchestrates query execution across the modes.

What are Power BI aggregations?

Aggregations are pre-summarized versions of fact tables at defined grains. The Power BI engine automatically routes appropriate queries to the aggregation, providing fast query performance for the aggregation grain while preserving detail-level access for drill-through.

When should I use composite models?

For very large fact tables where Import mode is impractical, for multi-source analytics combining different data sources, or for hot/cold hybrid patterns where current-period data is in memory and historical data is queried on demand.

How do aggregations interact with composite models?

Aggregations and composite models complement each other. The standard enterprise pattern combines them: aggregation in Import mode for fast executive queries; detail in DirectLake or DirectQuery for drill-through.

How big can a Power BI semantic model be?

With composite models and DirectLake, semantic models can address 100+ billion rows of detail. The aggregation pattern keeps memory consumption bounded.

What is the difference between DirectLake and DirectQuery in composite models?

DirectLake reads Delta files in OneLake directly with column-segment caching. DirectQuery sends each query to the source system. DirectLake performance is closer to Import; DirectQuery performance is bound by the source.

How do I size capacity for a composite model with aggregations?

Estimate aggregation + dimension memory for the Import portion. Estimate DirectLake column-segment working-set for the detail portion. Estimate DirectQuery resource consumption based on detail-query frequency. Combine for capacity sizing. Validate during pilot.

Can composite models support real-time data?

Composite models with DirectQuery over a real-time-updated source can serve near-real-time queries. For sub-minute latency, Fabric Real-Time Intelligence with Eventhouse is typically a better architectural fit.

How do I troubleshoot when queries don't use the aggregation?

Power BI Performance Analyzer shows whether the aggregation was used. If queries fall through unexpectedly, common causes include: filter context doesn't match aggregation columns, measures reference columns not in the aggregation, or the aggregation precedence is misconfigured.

Do composite models work in Power BI Pro (non-Premium)?

Composite models work in Power BI Pro, Power BI Premium Per User, Power BI Premium Per Capacity, and Microsoft Fabric F-SKU. Aggregations are available across the same tiers. Some advanced patterns (very large datasets, DirectLake) require Premium or F-SKU.

How do I migrate from Import-only models to composite models?

The migration is typically: identify the detail table appropriate for DirectLake/DirectQuery, restructure the model to keep dimensions and aggregations in Import, and switch the detail to the appropriate non-Import mode. Validate performance after each step.

Can composite models include Power BI Datamarts or Datasets?

Composite models can reference other Power BI datasets (now semantic models). The pattern is sometimes called "thin reports over composite models." Multi-source composition extends naturally to multiple Power BI semantic models.

How does EPC Group support composite model implementations?

EPC Group works with Fortune 500 enterprises on composite model implementations for very large semantic models. The standard engagement is 12 weeks for a substantial composite model build with aggregations. Our consultants — including Microsoft Press bestselling author Errin O'Connor — bring direct enterprise composite model experience.

What is the role of V-Order in composite models with DirectLake?

V-Order optimization applies to Delta files in OneLake that back DirectLake tables. Composite models using DirectLake benefit from V-Order on the underlying Delta tables.

Can composite models support RLS across storage modes?

Yes. Row-Level Security applies across composite models. RLS rules can reference columns in Import tables, DirectQuery tables, or DirectLake tables. The engine enforces RLS during query execution regardless of storage mode.

Next Steps

If your enterprise has semantic models exceeding pure-Import scale:

1. Inventory current large semantic models.
2. Identify candidates for composite model architecture.
3. Design aggregation grains based on query patterns.
4. Pilot the composite + aggregation pattern.
5. Engage a partner with deep enterprise semantic-model experience.

EPC Group has been delivering enterprise Microsoft consulting since 1997 — in its 30th year — and is Microsoft Solutions Partner with the core designations. We were historically the oldest continuous Microsoft Gold Partner in North America through the program's retirement. Our consultants — including Microsoft Press bestselling author Errin O'Connor — bring direct enterprise composite model experience across Fortune 500 deployments. To discuss your semantic model architecture, contact EPC Group for a 30-minute discovery call.