Quick Answer
Use composite models when you need real-time data on large facts and fast performance on dimensions. The reference pattern is Dual storage for dimensions, DirectQuery or Direct Lake for large facts, and aggregations for speeding common queries. This combination serves billion-row fact tables with sub-second query response for 80 to 95 percent of user interactions.
1. Storage Mode Cheat Sheet
| Mode | Speed | Freshness | Best For |
|---|---|---|---|
| Import | Fastest | Refresh-cycle | Dimensions, small facts |
| DirectQuery | Slower (depends on source) | Real-time | Large facts with fresh requirement |
| Direct Lake | Near-Import | Near real-time (OneLake) | Large facts in Fabric |
| Dual | Import speed | Import freshness | Dimensions joined to DirectQuery facts |
2. Aggregations: The Key Technique
Aggregations are pre-computed summary tables that Power BI automatically uses when a query can be satisfied at the aggregation grain. They are the primary technique for making billion-row DirectQuery fact tables usable.
Configuring an aggregation
- Build an imported summary table that pre-aggregates at a coarse grain (monthly, by region).
- In the model view, right-click the summary table and select Manage Aggregations.
- Map each column in the aggregation table to its detail column in the DirectQuery fact table and choose an aggregation type (Sum, Count, Min, Max).
- Hide the aggregation table from report view. Users should never reference it directly.
At query time, if a visual aggregates to the monthly/region grain covered by the aggregation, Power BI serves the result instantly from the imported table. If the visual drills to a finer grain (daily, by customer), it falls through to DirectQuery. Well-designed aggregations serve 80 to 95 percent of queries instantly while preserving drill-down capability.
3. Relationship Strength
Relationships between tables in different storage modes may be strong or weak.
- Strong relationships allow bi-directional cross-filtering, enforce referential integrity, and support full DAX semantics. They are the default when both tables are in the same source and storage mode.
- Weak relationships occur in cross-source joins and in certain DirectQuery scenarios. They do not support bi-directional filtering, and some DAX functions behave differently (for example, USERELATIONSHIP is not available).
To convert a weak relationship to strong, change the dimension table to Dual storage mode. Dual makes the table behave like Import for queries while preserving the ability to join to DirectQuery facts. For every cross-source relationship in your composite model, consider whether Dual storage resolves the weakness.
4. Chained Composite Models
Chained composite models let you extend a published semantic model with your own tables, measures, and relationships.
Typical enterprise pattern: the central data team publishes a certified Sales Analytics dataset in a Fabric workspace. Regional business units connect to this dataset as a remote source, then add local tables such as targets, commissions, or local-language reference data. The resulting chained model inherits the certified measures, relationships, and RLS from the upstream model.
This pattern enables self-service BI at enterprise scale without duplicating core data. The central data team owns the source of truth, and business units extend it safely. Governance is simpler because the upstream model can be updated without breaking downstream dependencies, as long as schema changes are communicated.
5. Performance Best Practices
- Always use Dual mode for dimensions joined to DirectQuery facts. The cost of imported storage is minimal; the performance gain is significant.
- Design aggregations for the most common query grains. Track query usage in DAX Studio and add aggregations to cover high-frequency patterns.
- Avoid bi-directional relationships crossing storage modes. They force weak relationships and degrade performance.
- Limit DirectQuery fact tables to the minimum columns needed. Unused columns still consume metadata and complicate folding.
- Index the underlying DirectQuery source appropriately. Columnstore indexes in SQL Server or clustered indexes in Azure SQL dramatically improve query performance.
- Monitor the million-row limit. If users hit it, add an aggregation at a coarser grain or implement an automatic drill-through pattern.
Frequently Asked Questions
What is a composite model in Power BI?
A composite model combines multiple storage modes (Import, DirectQuery, Direct Lake) and multiple data sources in a single semantic model. You can have dimension tables imported for fast filtering and fact tables in DirectQuery for real-time access. Composite models also support chained composite models where you can build on top of an existing published semantic model as a remote source. This enables pattern where an enterprise central model exposes certified data and business units extend it with their own departmental data.
What is Dual storage mode?
Dual is a hybrid storage mode available for dimension tables in composite models. A Dual-mode table is imported and cached in the engine, but can also serve as a DirectQuery source to maintain relationship integrity with DirectQuery fact tables. Dual lets you avoid the performance hit of joining imported dimensions to DirectQuery facts while also supporting fast filter propagation when the user filters within the model without touching the fact table.
How do aggregations work in composite models?
Aggregations are pre-computed summary tables imported alongside a DirectQuery fact table. When a user queries at the aggregation grain (for example monthly totals), Power BI serves the result from the cached aggregation and never touches the underlying source. When a query requires detail below the aggregation grain, Power BI falls through to the DirectQuery source. Aggregations are the primary technique for scaling DirectQuery to billion-row fact tables while keeping most queries fast.
What are weak relationships and when do they occur?
Weak relationships exist when Power BI cannot enforce referential integrity across the relationship. This happens for cross-source relationships (for example a relationship from an imported table to a DirectQuery table). Weak relationships work but impose restrictions: they cannot use bi-directional cross-filtering, certain DAX functions behave differently, and performance can be lower than strong relationships. Design composite models to minimize weak relationships by placing dimension tables in Dual mode where possible.
Can I mix Direct Lake and Import in a composite model?
Yes. A common pattern is Direct Lake for the primary fact table (large, updated frequently) and Import for lookup dimensions that rarely change. The Direct Lake fact table provides near-real-time freshness while Import dimensions give fast filter propagation and support calculated columns. Use this hybrid when you need both dimensions that change slowly and very large facts that refresh continuously.
What are the performance tradeoffs?
Import is fastest but requires scheduled refresh. DirectQuery provides real-time data but queries go to the source on every interaction. Direct Lake matches Import performance while reading directly from OneLake. Composite models let you match storage mode to data characteristics. The tradeoff is complexity: composite models have more moving parts, and optimization requires understanding relationship types, aggregation matches, and storage mode propagation.
What limits apply to DirectQuery in composite models?
DirectQuery queries must fold to the source database. Complex DAX that cannot fold will trigger Power BI to pull more rows than needed into the formula engine, degrading performance. The million-row default limit on query results applies to DirectQuery queries unless overridden. Additionally, some DAX functions are restricted in DirectQuery contexts. Always profile with DAX Studio to confirm fold behavior and query generation.
Can I chain composite models?
Yes. Chained composite models let you build a new dataset that uses a published semantic model as a source. The new dataset can add local tables, new measures, and new relationships while inheriting the original model. This is the enterprise pattern for central/self-service BI: a central data team publishes a certified shared semantic model, and business units build downstream models that extend it without duplicating the core data. Chained composite models respect RLS from the upstream model.
Need a Composite Model Designed?
Our consultants design composite models with aggregations, Dual storage, and chained architectures for billion-row datasets. Contact us for a design review.