For most of Power BI's life, connecting a model to data was a binary choice, and you lived with the consequences of whichever side you picked. Import loaded a copy into memory — gloriously fast, but a snapshot that went stale between refreshes and had to fit in memory, which caps how big it can get. DirectQuery left the data at the source and queried it live — always current and able to sit on enormous datasets, but slower, because every interaction is a round-trip to the source. You picked your poison per model and built around the downside. Composite models change the terms of that deal: they let you mix import and DirectQuery within a single model, so you can be fast where you can afford to be and live where you have to be. It's a genuinely useful capability, and like most flexibility, it's also a way to hurt yourself if you don't understand what you're doing.

What a composite model is

A composite model is one where different tables use different storage modes. You might import your dimension tables — the relatively small, slowly-changing things like products, customers, dates — because they're perfect for import: small enough to fit comfortably, and fast in memory. And you might leave your enormous, frequently-changing fact table in DirectQuery, because it's too big to import comfortably or needs to be current to the minute. One model, two storage modes, each table using the one that fits it.

There's a third mode that makes this really sing: Dual. A table in Dual mode can behave as either import or DirectQuery depending on how it's being used — imported (fast) when queried on its own or with other imported tables, and DirectQuery when joined to a DirectQuery table. Dimension tables are often best set to Dual, so they're fast for the common case but don't force an expensive mode-switch when joined to the live fact table.

The killer feature: aggregations

Where composite models become genuinely powerful is aggregations, and this is the pattern worth learning. Picture a fact table of a billion rows in DirectQuery — far too big to import. Most reports, though, don't need row-level detail; they ask for totals by month, by region, by product. So you build a small, pre-aggregated import table holding those summaries, and tell Power BI it's an aggregation of the big table. Now the magic: when a query can be answered from the fast, in-memory aggregation, Power BI silently uses it; when someone drills to detail the aggregation can't serve, it transparently falls through to DirectQuery against the full table. Users get import-speed performance for the common summary questions and full detail when they genuinely need it — the best of both, automatically, without them knowing which path served them.

Where it bites

The flexibility comes with real hazards, and knowing them beforehand saves you a bad afternoon:

  • Performance is only as good as the DirectQuery source. Any interaction touching a DirectQuery table is at the mercy of the underlying database. If that source is slow or under-optimised, your composite model inherits its sluggishness for those queries. A composite model does not rescue a slow source; it exposes it.
  • Relationships across storage modes have limits and costs. Joining an import table to a DirectQuery table is a "limited relationship" with different behaviour and performance characteristics. Understand which relationships in your model cross the boundary, because those are where surprises live.
  • Complexity you have to hold in your head. With everything in one storage mode, performance is at least uniform. Mix modes and you have to reason about which tables are where and how a given query will actually be resolved. That mental overhead is a real cost, and it's why composite models reward deliberate design over accidental sprawl.
  • Security and source considerations. Live queries mean the source is being hit continuously by report interactions, with implications for load and for how credentials and security flow. Plan for the source being an active participant, not a passive snapshot.

A concrete scenario

Make it real. Say you run retail analytics: three years of transactions, a billion-plus rows, growing daily. Importing that is a non-starter — too big for memory, too slow to refresh. But ninety per cent of what anyone asks is summary: sales by store by week, by category by month. So you leave the billion-row fact table in DirectQuery, build a small import aggregation of the common summaries, and set your product, store, and date dimensions to Dual. Now the regional manager's "how are my stores doing this quarter" dashboard answers instantly from the in-memory aggregation, while the analyst who occasionally drills into a single day's individual transactions falls through, transparently, to DirectQuery. One model serves both, each at the right speed, and neither user has to know — or care — which path answered their click.

How to use it well

The discipline is to reach for composite models deliberately, to solve a specific problem — a fact table too big to import, a need for real-time detail alongside fast summaries — not as a default. Import what you can; it's the fastest and simplest. Use DirectQuery only where import genuinely can't reach — data too large or too fresh. Set dimensions to Dual so the common case stays fast. And if you have a big DirectQuery table serving mostly summary questions, invest in aggregations, because that's where the pattern earns its keep. Used that way, composite models retire the old "speed or freshness" compromise for a whole class of reports. Used carelessly — modes mixed without a reason, relationships crossing the boundary everywhere — they just give you a model that's simultaneously slow and hard to reason about, which is the worst of both rather than the best. As ever, the tool rewards the person who understood the trade-off they were making.