Power BI Composite Models - When to Use Them and How to Design Them Well

If you've been building Power BI semantic models for any length of time, you've probably hit that moment where import mode works perfectly for everything except that one massive fact table. Or maybe you've got a well-governed enterprise semantic model that almost does what a team needs - they just want to add a couple of extra tables from their own data source. That's where composite models come in.

We've deployed composite models for several Australian organisations now, and the pattern is consistent. When they're the right tool for the job, they're excellent. When they're forced into situations where a simpler approach would work, they create more problems than they solve. Microsoft's official composite model guidance covers the technical specification well, so here I'll focus on what we've actually seen work in practice.

What a Composite Model Actually Is

A composite model combines multiple source groups. That's Microsoft's term for it, and it's a useful concept. One source group might be imported data sitting in your model cache. Another might be a DirectQuery connection to a SQL database. A third could be a live connection to an existing Power BI semantic model or Analysis Services tabular model.

The thing that catches people off guard is that simply connecting to a remote tabular model doesn't make it a composite model. If you connect to an enterprise semantic model and don't add any additional data, you've got a DirectQuery model with a single source group. You're just pointing at the remote model. That's fine, and it's useful for things like renaming tables or changing column formatting without touching the source. But it's not a composite model.

A composite model starts when you bring in data from a second source. Maybe you chain your enterprise semantic model together with an imported Excel file. Maybe you combine a DirectQuery connection to your data warehouse with imported lookup tables from a SharePoint list. The moment you have two or more source groups, you're in composite model territory.

Three Situations Where Composite Models Make Sense

After working through enough of these engagements, I've boiled it down to three core use cases. Everything else is some variation.

Performance improvement for DirectQuery. You've got a DirectQuery model and some of the queries are slow. Composite models let you selectively import the tables where import performance matters while keeping the big fact tables on DirectQuery. This is by far the most common reason we build composite models. A client running reports against a 200 million row fact table in Azure SQL doesn't need that dimension table with 50 product categories living in DirectQuery too. Import those dimension tables and watch your report speed improve noticeably.

Extending an enterprise semantic model. A central BI team manages the organisation's semantic model. A department needs everything in that model, plus their own supplementary data - maybe a budget spreadsheet, a local database with department-specific metrics, or calculated tables derived from the central model. Instead of asking the central team to modify the enterprise model (which could take weeks or months through change control), they build a composite model that chains the enterprise model and imports their extra data.

Combining multiple DirectQuery sources. You need a unified report across data sitting in two different systems. Maybe your sales data is in Dynamics 365 and your support data is in a separate PostgreSQL database. A composite model lets you bring both into a single semantic model without building an ETL pipeline to consolidate them first.

When to Choose Something Simpler

Here's where I'll be blunt - composite models add complexity, and that complexity has a cost. Before you reach for one, ask yourself these questions.

Can you just use import mode? If your data fits in memory and doesn't need to be refreshed more than a few times a day, import mode is almost always better. It's faster, more flexible with DAX calculations, and far easier to reason about. We've seen teams jump to composite models because they assumed their data was too large for import, only to find that their 10 GB dataset compressed to 800 MB in the VertiPaq engine. Try import first.

Can you add the supplementary data to the existing source? If you need to extend an enterprise model with extra data, the cleanest solution is often to get that data into the source data warehouse. Yes, this involves the central team. Yes, it takes longer initially. But you avoid the cross-source-group relationship headaches that composite models introduce. If the data is genuinely departmental and changes frequently, a composite model is warranted. If it's stable reference data, push to get it into the source.

Is a single DirectQuery model sufficient? If your data lives in one source and you don't need to combine it with anything else, a straight DirectQuery model is simpler and avoids the composite model's cross-source-group relationship limitations.

Table Storage Mode - Getting This Right Matters

This is where composite model design gets practical. Every table in a composite model has a storage mode, and choosing the right one for each table affects both performance and calculation accuracy.

DirectQuery is for your large fact tables - the ones with millions of rows that would blow out your model size if imported. Sales transactions, log entries, telemetry data. These stay in the source database and get queried on demand.

Import is for tables that aren't related to DirectQuery facts from the same source, or that come from sources that don't support DirectQuery at all. It's also the only option for calculated tables.

Dual is the interesting one, and it's what you should use for dimension tables that relate to DirectQuery fact tables from the same source. A dual table stores data in the import cache but can also be queried as part of a DirectQuery query. When Power BI sends a query that only involves dual tables, it uses the fast import cache. When the query also involves DirectQuery fact tables from the same source, it can fold the dual table into the native query sent to the database.

This distinction matters more than you might think. We had a client who set all their dimension tables to Import mode instead of Dual. Their queries that combined dimension filters with DirectQuery facts were running as cross-source-group queries - Power BI was pulling the dimension data from the cache and joining it with the DirectQuery results locally. Switching those dimensions to Dual mode let Power BI send a single query to the database with the dimension joins included. Query times dropped from 8 seconds to under 2.

Hybrid mode is newer and worth knowing about. It lets you partition a fact table so that recent data comes through DirectQuery (giving you real-time freshness) while historical data is imported (giving you fast query performance). Think of it as "import for last year's data, DirectQuery for today." It's well-suited for operational dashboards that need current data but also show historical trends.

Cross-Source-Group Relationships - The Gotcha

Here's the thing that trips up most composite model designers, and where I've spent the most time debugging with clients.

When a query spans tables from the same source group - say, a dual dimension table and a DirectQuery fact table from the same SQL database - Power BI can send a single efficient query to the source. The relationship behaves like a regular relationship. Good.

When a query spans tables from different source groups - say, an imported Excel table joined to a DirectQuery fact table - Power BI has to pull data from both sources and join them locally. These are called limited relationships, and they behave differently. Many-to-many semantics get applied regardless of the actual cardinality you've defined. Referential integrity can't be guaranteed. Some DAX functions may return unexpected results because related tables might contain values with no match on the other side.

The practical impact is that you need to think carefully about which tables go into which source groups, and design your model to minimise cross-source-group relationships. Put your related dimensions and facts in the same source group wherever possible. Use Dual mode for dimensions that bridge between import and DirectQuery sources.

If you must have cross-source-group relationships, test your DAX calculations thoroughly. Functions like RELATED and RELATEDTABLE may behave differently than you expect. Blank rows can appear in query results that wouldn't show up in a pure import model.

Aggregations - The Performance Layer

If your composite model has DirectQuery fact tables and performance is still not where you need it, user-defined aggregations are worth exploring. The concept is straightforward - you create an import table that holds pre-aggregated summaries of your DirectQuery fact table. When a query can be satisfied by the aggregation, Power BI uses the fast import cache. When it can't (say, a drill-through to detail level), it falls back to DirectQuery.

The design work is in choosing the right grain for your aggregation tables. Too coarse and most queries still hit DirectQuery. Too fine and you're basically importing the whole fact table. We typically start with the grain that matches 80% of the report visuals - usually something like daily totals by product category and region - and iterate from there.

This requires understanding which reports and visuals are most used. Power BI's query diagnostics and Performance Analyzer can tell you which queries are slowest, and those are your candidates for aggregation coverage.

Practical Recommendations

After enough composite model deployments, here's what I tell teams at the start of a project.

Start simple. Build an import model first. If performance or data volume forces you toward DirectQuery for specific tables, convert to a composite model at that point. Don't start with a composite model "just in case."

Set dimension tables to Dual mode. If a dimension table relates to a DirectQuery fact table from the same source, make it Dual. There's very little downside and significant performance benefit.

Test your calculations. Write your DAX and test it before and after switching to composite mode. Pay particular attention to calculations that use RELATED, CALCULATE with relationship navigation, or ISBLANK checks.

Monitor query performance in production. Composite models can develop performance issues that only appear under concurrent user load, when multiple users trigger different combinations of import and DirectQuery queries simultaneously.

If you're working with Power BI semantic models and need help designing composite models that actually perform well, our Power BI consultants can help you get the model architecture right the first time. We also do broader business intelligence work for organisations that need reporting across multiple data sources. And if you're running into performance issues with existing Power BI deployments, our Microsoft data consultants can assess whether a composite model redesign would help or whether a different approach is more appropriate.

Composite models aren't magic, but they're a genuinely useful tool for specific problems. Get the storage modes right, keep cross-source-group relationships to a minimum, and test your DAX thoroughly. Do that and you'll avoid most of the issues we've had to debug for clients who didn't.