Composite model (Power BI)

What is a composite model?

A composite model is a Power BI semantic model where not every table uses the same storage mode. One table may be imported into Power BI's in-memory engine, another may stay in the source and be queried through DirectQuery, and another may use Direct Lake in Microsoft Fabric.

The practical idea is simple: choose the right mode per table. A small product dimension can sit in memory. A very large transaction table can stay in the warehouse. A Fabric table can be read directly from OneLake. The report sees one model, but the tables behind it do not all live in the same place.

There is also a second common use: extending an existing published Power BI semantic model. You connect to the central model your organisation already maintains, then add your own local tables. Power BI turns that connection into a DirectQuery connection and creates a local model around it.

The storage modes in a composite model

Import
Power BI copies the data during refresh and stores it in memory. This is usually the fastest mode for interactive reports, but the data is only as fresh as the last refresh and the model must fit in capacity memory.

DirectQuery
The data stays in the source. Every visual sends a query back to that source when a user opens the page, changes a slicer, or drills into a number. This keeps data fresh, but report speed depends heavily on the source system.

Dual
A Dual table can behave as imported or DirectQuery depending on the query. It is often used for dimensions around a large DirectQuery fact table, so slicers can stay fast while detailed measures still come from the source.

Direct Lake
In Microsoft Fabric, Direct Lake reads Delta tables from OneLake for Power BI semantic models. It avoids a classic import refresh while still aiming for import-like interactive performance. Direct Lake can also be combined with Import tables in composite scenarios, with some editing limitations depending on whether you work in Desktop, web modeling, or XMLA.

When do you use a composite model?

1. A large fact table with small dimensions. Keep the huge sales, clickstream, or sensor table in DirectQuery, while customer, product, and date dimensions sit in Import or Dual mode.

2. A central model plus local context. Finance or IT owns the official semantic model, but a team needs to add a small Excel target table or mapping table for its own report.

3. Several DirectQuery sources in one report. You need a visual that compares warehouse data with another operational database without copying everything first.

4. Fabric reporting on Direct Lake plus extra imported data. A Fabric model reads gold tables from OneLake, while a small imported table adds manual targets, categories, or planning values.

Composite model versus all Import or all DirectQuery

All Import
This is still the default choice for many Power BI reports. It is fast, predictable, and gives the broadest DAX support. The trade-off is refresh latency and model size.

All DirectQuery
This keeps the data live and avoids copying it into Power BI, but every user interaction loads the source. DirectQuery models need careful source design, fewer visuals, and realistic performance testing.

The mixed model
A composite model gives you more control, but it also gives you more places to make a mistake. You must understand which table lives where, which relationships cross sources, which security rules apply, and what happens when a visual needs data from several source groups at once.

For that reason, start with one mode where you can. Move to a composite model when one mode no longer solves a real problem.

Aggregations as a speed layer

One common pattern is to keep a detailed fact table in DirectQuery and add an imported aggregation table. The aggregation table stores the same measures at a higher grain, for example revenue per day and product group instead of one row per order line.

If a visual asks for monthly revenue, Power BI can answer from the small imported table. If a user drills into individual transactions, Power BI can still query the detailed source. Microsoft recommends keeping aggregation tables much smaller than the underlying table, with a basic guideline of at least ten times fewer rows.

What to watch out for

Cross-source relationships are limited
When a relationship crosses source groups, Power BI cannot optimise it in the same way as a normal import relationship. Keep relationship columns low-cardinality where possible. Microsoft recommends fewer than 50,000 unique values for best performance in these scenarios.

One visual can hit several sources
A simple-looking chart may need an imported table, a DirectQuery table, and a remote semantic model. That can mean several queries, extra joins, and slower report pages.

Data can leak through queries
If a filter from an imported table is pushed into a query against a database, values from that imported table may appear in the database query logs. Think about privacy and governance before combining sensitive sources.

Chained models need ownership
Extending published semantic models is useful, but chains of derived models become hard to govern. Decide who may extend central models, which extensions belong back in the shared model, and who owns broken reports when the source model changes.

Row-level security has boundaries
Security defined in one model does not automatically protect every table you add locally. Test row-level security with real user accounts before relying on it.

A good rule of thumb

Use Import unless you have a clear reason not to. Use DirectQuery when freshness, scale, or governance makes copying data the wrong choice. Use Direct Lake when your data is already in Fabric and the scenario fits the Direct Lake rules. Use a composite model when different tables genuinely need different answers.