Composite models explained

Power BI gives us several ways to get data into a semantic model, but two of the most familiar are Import and DirectQuery.

With Import, Power BI loads the data into its own storage engine. Reports query that cached data rather than repeatedly querying the original source. This generally provides excellent report performance and gives the modeller considerable flexibility.

With DirectQuery, the data remains in the source. When a report needs data from a DirectQuery table, Power BI generates queries against that source.

Neither is inherently "better". They solve different problems.

The difficulty starts when an organisation treats the choice as a technical preference rather than a business requirement. A 30 million row transaction table does not automatically need DirectQuery. A dashboard that needs data every five minutes does not automatically need it either.

The design should start with the reporting requirement, the volume and shape of the data, the acceptable latency, the capabilities of the source system and the expected user experience.

What is a composite model?

Composite model

A Power BI composite model is a semantic model containing tables with different storage modes, such as Import and DirectQuery. It can also involve multiple DirectQuery sources. The defining characteristic is that the model combines storage behaviours rather than using a single mode throughout.

Imagine a sales organisation with a large transactional database containing several years of orders.

Most management reporting does not need today's transactions. Yesterday's data is perfectly adequate for the majority of dashboards, and historical analysis benefits from highly responsive queries.

However, the operations team also wants to see today's orders as they arrive.

One option is to make the entire model DirectQuery. That might satisfy the freshness requirement, but now every report query potentially depends on the performance of the source database.

Another option is to import everything. That gives excellent performance, but the latest transactions are only available after a refresh.

A composite model provides another option.

The historical and analytical data can remain in Import storage while a suitable table or set of tables uses DirectQuery.

The model now has two different behaviours, deliberately chosen for two different requirements.

That is the real value of composite modelling.

How does a composite model actually work?

The important detail is that storage mode is configured at table level.

A model might therefore contain an imported Date table, imported reference data, DirectQuery transactional data and Dual dimension tables.

That last category is where composite models become particularly interesting.

A Dual table can behave as an imported table when Power BI can satisfy the query from its cache, but can participate in DirectQuery behaviour when it is queried alongside DirectQuery tables from the same source.

Microsoft recommends Dual storage particularly for dimensions that are used to filter or group DirectQuery fact tables from the same source.

This means you should not think of a composite model simply as:

Import + DirectQuery

A more realistic model is:

Import + DirectQuery + Dual

Each mode has a role.

Microsoft describes Hybrid tables as an incremental refresh pattern where imported partitions are combined with a DirectQuery partition for the latest data.

Why would you use a composite model?

There are several legitimate reasons.

The first is data volume.

Perhaps your business has ten years of transaction history containing hundreds of millions of rows. Importing all of it may be unnecessary. The business might only analyse the last two years regularly, while older data is accessed occasionally.

A DirectQuery approach for the entire dataset could avoid importing the data, but potentially at the expense of report performance.

A composite design can provide a middle ground.

The second is data freshness.

Some reports genuinely need information that is only minutes old. Operational dashboards, warehouse monitoring, order processing and certain financial or service-management scenarios can have requirements that a daily or hourly refresh cannot satisfy.

That does not mean every table in the model needs to be live.

The third is source constraints.

Sometimes the organisation already has a large, governed data source that should remain the authoritative system of record. Importing another complete copy into Power BI may create unnecessary duplication.

A carefully designed DirectQuery component can allow the semantic model to work against that source while keeping supporting analytical data imported.

The fourth is extending an existing semantic model.

Power BI supports composite models that build on Power BI semantic models or Analysis Services models and add additional data. This can be useful where a governed enterprise model exists but a particular reporting requirement needs additional information.

However, that last scenario deserves particular caution. Extending a semantic model can be useful. Creating layers of semantic models on top of one another simply because it is convenient can quickly become difficult to govern.

Consider a finance dashboard.

The organisation already has a well-designed semantic model containing customers, invoices, revenue and the financial calendar. Finance then asks for a small operational dataset containing today's payment processing status.

Rebuilding the entire enterprise model around that new source would be excessive.

A composite model could allow the existing analytical data to remain imported while bringing in the additional operational data using an appropriate storage mode.

The technology is useful because the business requirement is specific.

That is the pattern to look for.

Composite does not mean faster

This is one of the most important points to understand.

A common assumption is that moving a large table to DirectQuery will make a Power BI report faster because Power BI no longer has to store the data.

That confuses model size with query performance.

DirectQuery reduces the amount of data that Power BI needs to import and store. It does not make the underlying query disappear.

When a user opens a visual that needs DirectQuery data, Power BI has to generate a query, send it to the source, wait for the source to execute it and then process the result.

The source database therefore becomes part of the report's performance path.

If the source is poorly indexed, overloaded, badly modelled or simply not designed for analytical workloads, the report can suffer.

A composite model has not removed the performance problem. It has moved part of the problem.

There is another complication.

A visual may involve both imported and DirectQuery data. In that situation, Power BI may need to coordinate information between storage modes and, in some scenarios, between different source systems.

This can produce more complicated query plans than a straightforward Import model.

Cross-source queries also have security implications. Microsoft notes that data retrieved from one source can potentially be used as part of a query sent to another source. In other words, introducing a second source is not just a modelling decision. It can become a data security consideration.

This is particularly important when the sources have different security classifications, ownership models or data residency requirements.

Before building a composite model, ask:

Could data from Source A end up being passed to Source B as part of query processing?

If the answer could be yes, involve your security and data governance teams.

Relationships become more important

A well-designed star schema is important in any Power BI model. It becomes even more important when storage modes are mixed.

Suppose an imported Customer dimension filters a DirectQuery Sales table.

At a conceptual level, that looks simple.

At execution time, however, Power BI may need to use the imported values to construct or influence a query against the DirectQuery source.

The more complicated the model becomes, the more important it is to understand where the data lives and how filters travel through the model.

This is one reason Dual storage mode exists.

For dimensions that commonly work with DirectQuery fact tables from the same source, Dual can allow Power BI to use cached dimension data in some queries while retaining the ability to participate in DirectQuery queries when required.

It is not a magic setting, though.

Dual tables have DirectQuery-related constraints, and their cached values can become stale relative to the source. Microsoft specifically describes Dual as a performance optimisation rather than something that should be used indiscriminately.

This is also why I would be cautious about trying to connect fact tables directly to one another just because the tables happen to use different storage modes.

The underlying modelling principle does not change because the tables are DirectQuery.

A dimensional model remains the safer pattern.

Where you have Sales and Returns, for example, it is usually better to have shared dimensions such as Date, Customer and Product rather than creating complex relationships directly between the two fact tables.

Microsoft's guidance similarly recommends a star schema over directly relating fact tables with many-to-many relationships.

Composite modelling does not give you permission to abandon good data modelling.

It makes good modelling more important.

What about security?

Security needs to be considered at more than one level.

First, there is the security of the underlying source. DirectQuery means the source system remains part of the architecture, so its permissions, authentication, capacity and availability all matter.

Second, there is Power BI security such as row-level security.

Composite models have some additional considerations here. Microsoft notes that when row-level security involves DirectQuery tables, updates to those tables may not immediately affect cached results. A model refresh may be required for new source-side records to become available in the relevant cached query path.

Third, there is cross-source data movement.

If a model combines data from different sources, you need to understand whether values from one source could influence queries against another.

This is not an argument against composite models.

It is an argument against treating them as merely a Power BI Desktop setting.

Can you reverse a composite model?

This is a surprisingly important question.

The answer is: sometimes, but not always in the way you might expect.

You can change a DirectQuery table to Import. In Power BI Desktop, once a DirectQuery table has been converted to Import, it cannot normally be switched back to DirectQuery through the same storage mode property. Microsoft documents exceptions involving Power BI web modelling and live editing scenarios where version control can be used to reverse the change.

There is also an important distinction between changing an individual table and changing an entire model.

Power BI does not provide a simple "turn this Import model into DirectQuery" switch.

If you have an Import-only model and later decide that some tables should use DirectQuery, the normal approach is to add a DirectQuery connection and remodel or map the required tables accordingly. Microsoft explicitly recommends creating a composite model for scenarios where you need to retain Import tables while adding DirectQuery tables.

So before changing storage modes, treat the decision as architectural rather than experimental.

Do not assume you can flip a switch later if the design turns out badly.

• Identify the business requirement for using DirectQuery.
• Confirm which data genuinely needs near real-time access.
• Keep everything else in Import where practical.
• Check whether the source can handle analytical queries.
• Review indexes and query performance at the source.
• Identify which dimensions should use Dual.
• Validate relationships using a proper star schema.
• Test visuals that combine Import and DirectQuery data.
• Test report performance under realistic concurrent usage.
• Review security and cross-source data movement.
• Test row-level security behaviour.
• Document each table's storage mode and why it was chosen.
• Establish ownership for the DirectQuery source.
• Confirm refresh and caching expectations with report users.
• Test failure scenarios when the DirectQuery source is unavailable.

Common composite model mistakes

The first mistake is using DirectQuery because the dataset is "too big".

Large datasets need investigation, not an automatic storage mode decision.

Can the data be reduced? Can historical data be aggregated? Can the model use incremental refresh? Can the source be improved? Can an appropriate Fabric architecture provide a better serving layer?

DirectQuery is one answer, not the answer.

The second mistake is using DirectQuery to solve a refresh problem.

If the real issue is that a model takes six hours to refresh, the answer may be incremental refresh, better Power Query design, source optimisation or a better data platform.

Replacing refresh time with query time is not necessarily an improvement.

The third mistake is mixing sources without understanding the consequences.

A model connecting a warehouse, an operational database and several spreadsheets might technically work. That does not mean it is a good architecture.

The fourth mistake is forgetting that DirectQuery affects the entire user experience.

A report with a few slow visuals is annoying.

A report where every slicer interaction triggers expensive database queries can become practically unusable.

The fifth mistake is creating a composite model because an existing semantic model is inconvenient.

If the enterprise semantic model is missing a legitimate business requirement, the better answer may be to improve the enterprise model.

A local workaround can be useful.

Twenty local workarounds are architecture debt.

Composite models are not the only option

Before committing to a composite model, consider whether another architecture solves the problem more cleanly.

Import should remain the default where the data volume, refresh requirements and business latency allow it. Microsoft recommends Import where possible because it provides the greatest design flexibility and generally the best performance.

DirectQuery can make sense where the data is too large to import efficiently or where near real-time access is genuinely required. But the source must be capable of serving the workload.

Hybrid tables can be useful where most historical data can be imported but the latest data needs to remain queryable at the source. Microsoft supports this through incremental refresh and a DirectQuery partition.

Direct Lake is another consideration in Microsoft Fabric environments. It provides a different approach to accessing data stored in OneLake and should be evaluated before automatically reaching for a traditional Import and DirectQuery combination.

The architectural question is therefore broader than:

"Should I use a composite model?"

It is:

"What is the most appropriate way to serve this analytical requirement?"

That might be Import.

It might be DirectQuery.

It might be Hybrid.

It might be Direct Lake.

And sometimes it might genuinely be a composite model.

The architecture matters more than the feature

Composite models are a useful Power BI capability because they acknowledge something that happens in real organisations: not all data has the same requirements.

One table might contain billions of historical transactions.

Another might contain today's operational events.

A third might be a small reference table.

Trying to force all three into exactly the same storage pattern can be less sensible than designing around their actual characteristics.

But flexibility comes at a cost.

Once a model contains different storage modes, you have created different paths through the architecture. Some queries may be answered from Power BI's in-memory engine. Others may go to a database. Some dimensions may behave differently depending on the query. Cross-source queries can introduce additional considerations.

The model therefore becomes more than a collection of tables.

It becomes a small distributed query architecture.

That is why composite models deserve architectural thinking rather than simply a tick-box approach in Power BI Desktop.

A practical decision framework

When someone asks whether a Power BI model should be composite, I would work through the following sequence.

1. What decision does the report support?

Start with the business outcome. If nobody needs the data to be current within minutes, there may be no reason to introduce DirectQuery.

2. How fresh does the data actually need to be?

"Real time" is often used when the actual requirement is hourly, every 15 minutes or even daily.

Put a number against the requirement.

3. How much data genuinely needs to be queried?

Do not confuse total source volume with reporting volume.

A database containing 500 million rows does not necessarily mean the report needs access to all 500 million.

4. Can Import satisfy the requirement?

If it can, it is usually the simpler answer.

5. If not, what specifically prevents Import?

Volume? Latency? Refresh duration? Source restrictions? Something else?

6. Can the source handle DirectQuery?

Run realistic queries. Do not test only a simple SELECT against a single table.

7. What happens when users interact with the report?

Test slicers, cross-filtering, drillthrough, bookmarks and high-cardinality visuals.

8. What happens under concurrency?

A query that performs well for one developer may behave very differently when hundreds of users open the report.

9. What happens when the source is unavailable?

Import has a major architectural advantage here: the report can continue to query its cached data even when the original source is temporarily unavailable.

10. Can the design be explained six months from now?

If nobody can explain why each table uses its particular storage mode, the model is already becoming a governance problem.