Power BI Composite Models Explained

Composite models are one of Power BI's most powerful features for enterprise deployments. They allow a single semantic model to combine Import mode (fast, cached data) with DirectQuery mode (real-time data from the source) and even connect to other published Power BI datasets. This flexibility lets organizations balance performance, data freshness, and model complexity.

Understanding Storage Modes

Power BI offers three storage modes for tables, and composite models let you mix them within a single dataset:

Import Mode: Data is loaded into the Power BI in-memory engine (VertiPaq). Queries are extremely fast because data is compressed and cached locally. The tradeoff is that data is only as fresh as the last scheduled refresh.

DirectQuery Mode: Queries are sent directly to the source database in real time. Data is always current, but query performance depends on the source system's speed and the complexity of the query.

Dual Mode: Tables are stored in both Import and DirectQuery mode. Power BI's query engine automatically chooses the optimal mode based on the query context. Dual mode provides performance benefits of Import with the flexibility to fall back to DirectQuery when joining with DirectQuery tables.

When to Use Composite Models

Composite models solve specific architectural challenges:

• Large fact tables with small dimensions: Import your dimension tables (products, customers, dates - typically thousands of rows) for fast filtering and slicing, while keeping multi-billion-row fact tables in DirectQuery to avoid memory limits
• Real-time requirements on specific data: Historical data can be imported for fast analysis while current-day data comes through DirectQuery for up-to-the-minute accuracy
• Multiple source systems: Combine data from Azure SQL, Synapse, Dataverse, and other sources in a single model without building a centralized warehouse
• Extending published datasets: Connect to an IT-managed certified dataset and add your own tables, measures, and calculations without duplicating the core model

Designing a Composite Model

Step 1: Identify Table Storage Modes

For each table in your model, choose the appropriate storage mode:

| Table Type | Recommended Mode | Rationale | |-----------|-----------------|-----------| | Date dimension | Import | Small table, used in every query | | Product/Customer dimensions | Import | Fast filtering, typically < 1M rows | | Large fact tables (> 10M rows) | DirectQuery | Avoids memory limits, current data | | Aggregation tables | Import | Pre-computed summaries for speed | | Lookup tables | Dual | Flexible joining with either mode |

Step 2: Configure Relationships

Relationships between Import and DirectQuery tables have specific rules:

• Relationships from Import to DirectQuery are "limited" - they cannot enforce referential integrity on the DirectQuery side
• Many-to-many relationships are supported but require careful design
• Cross-source group relationships allow joining tables from different data sources

Step 3: Add Aggregation Tables

Aggregations are the secret to making composite models perform at enterprise scale. Create pre-aggregated summary tables stored in Import mode that automatically answer high-level queries, while detailed queries fall through to DirectQuery.

For example, create a monthly sales summary (Import) alongside the detailed daily transactions (DirectQuery). When a user views monthly revenue by region, Power BI serves the answer from the fast aggregation table. When they drill to daily detail, the query goes to DirectQuery.

Configure aggregation mappings in the table properties to tell Power BI which aggregation table answers which query patterns. The query engine handles routing automatically and transparently.

Performance Optimization

Aggregation Coverage: Aim for 80%+ of user queries to be answered by aggregation tables. Use Performance Analyzer in Power BI Desktop to identify which queries use aggregations vs. DirectQuery.

DirectQuery Performance: Ensure your source database has proper indexes on columns used in filters, relationships, and grouping. Create database views that pre-join tables to reduce query complexity.

Relationship Cardinality: Use one-to-many relationships wherever possible. Many-to-many relationships across storage modes require extra processing and can be slow.

Measure Design: DAX measures in composite models should be designed to work efficiently with both storage modes. Avoid iterator functions (SUMX, FILTER) on large DirectQuery tables when possible.

Common Patterns

Hub-and-Spoke: A central certified dataset (the hub) published by IT, with departmental composite models (spokes) that extend it with local data. Marketing adds campaign data, Finance adds budget data, Operations adds facility data - all connected to the same core dataset.

Hot/Warm/Cold Architecture: Current day's data via DirectQuery (hot), recent months in Import aggregations (warm), historical years in detailed Import tables (cold). Queries are automatically routed to the most efficient tier.

Multi-Source Federation: Combine Dynamics 365 (Dataverse), Azure SQL (data warehouse), and Excel (budget files) in a single model without ETL. Each source uses its native DirectQuery connector.

Related Resources

• Composite Models Advanced Patterns
• Star Schema Data Modeling
• Direct Lake Mode Guide
• Power BI Consulting Services