Power BI composite models: mixing storage modes in one model
Power BI composite models are semantic models whose tables use more than one storage mode or more than one DirectQuery source. So a single model can hold fast imported tables next to live tables that stay in the source. That flexibility solves real problems, but it also adds rules about relationships, security and speed. The guide below covers the modes, the good uses and the traps.
- Import plus DirectQuery
- Dual mode for dimensions
- Know the trade-offs
Last updated
What are Power BI composite models?
A plain model uses one approach. Either it imports everything into memory, or it queries one source live. A composite model combines approaches, so each table gets the mode that suits it.
Microsoft describes several kinds. One mixes import and DirectQuery tables. Another combines two or more DirectQuery sources. A third connects to a published Power BI semantic model by DirectQuery and adds local tables beside it.
In Desktop, the status bar shows a storage mode called Mixed when a file holds both kinds of table. Therefore you can tell at a glance what kind of model you have open.
Storage modes in a composite model compared
Each table has its own storage mode, which you set in the Properties pane of Model view.
| Storage mode | Where queries run | Typical use |
|---|---|---|
| Import | In memory, from a loaded copy | Small and medium tables that need speed |
| DirectQuery | In the source, at query time | Very large or fast-changing tables |
| Dual | Either way, as the query needs | Dimension tables shared by both kinds |
| Mixed model | A blend across tables | Large facts with imported summaries |
| Live connection | In a remote model, no local tables | Reports on a shared semantic model |
When does a composite design make sense?
The classic case is a huge fact table. Importing it would take too long, so it stays in DirectQuery. Meanwhile, the small dimension tables load into memory or use Dual mode. As a result, slicers respond quickly while detail stays current.
A second case is extension. A central team publishes a trusted model, and a business analyst needs one extra table, such as a budget sheet. With a composite model, the analyst adds it without copying the central data.
However, do not reach for this design by default. A simple import model is easier to build, test and tune. So choose the composite route only when a plain model cannot meet the need.
Risks to weigh with Power BI composite models
Microsoft documents these points, and each one deserves a test before go-live.
- Relationships across sources become limited relationships
- Values from one source can travel inside a query to another
- Visuals may send many queries to the DirectQuery source
- Chains of models have a documented maximum length
- Row-level security does not carry over from a source model
- Readers need permission on every model in the chain
How do composite models affect security and speed?
Security first. When a visual joins two sources, Power BI may send values from one inside the query to the other. So a confidential value could reach a database it was never meant to reach. Desktop shows a warning for this reason.
Speed needs thought as well. A relationship between two source groups is a limited relationship, which behaves differently from a regular one. Also, a visual that mixes modes can trigger several queries. Therefore test real pages with the performance analyzer.
Dual mode eases part of this. When a dimension can act as import or DirectQuery, the engine picks the cheaper path. Still, tenant settings control some composite features, so involve your admin early.
Power BI composite models: common questions
Are Power BI composite models slower than import models?
Often, yes. Imported data answers from memory, while DirectQuery tables wait on the source. A composite model sits in between, because only some queries leave memory. So speed depends on the source, the relationships and the visuals. Test with realistic data before launch, and consider aggregations if the large table is slow.
What is a limited relationship?
It is a relationship the engine cannot treat as a regular one, for example because its two tables come from different source groups. Microsoft documents that such relationships behave differently in joins and in some DAX functions. Therefore totals can surprise you. Read the relationship guidance on Microsoft Learn, and check results against a known figure.
Can I build a composite model on a published semantic model?
Yes. In Desktop you connect to the published model and then choose to make changes to it. That turns the live connection into DirectQuery and lets you add tables. However, tenant settings must allow it, and license needs depend on where each model lives. Check the documentation for the current conditions.
Does row-level security carry over from the source model?
Microsoft notes that row-level security definitions are not imported from the source model into the composite one. The source still applies its own rules to the person who queries it. So review security across the whole chain. Test with accounts that hold different roles before anyone outside the project sees the report.
Can someone review a composite model before it goes live?
Yes. Power BI Agency reviews storage modes, relationships and security in your own tenant. We are a small team within Sataware Technologies and independent of Microsoft. The review runs online, to a scope fixed in a written quote. Start with a free 30-minute assessment, or call (480) 485-7373.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.