The bottom line
A composite model lets a single Power BI report combine Import and DirectQuery sources — and, through DirectQuery for Power BI semantic models, chain onto a governed enterprise model. It is the honest answer when you must combine SAP, Dynamics 365 and a spreadsheet before a warehouse exists. Used well it buys time; used as a permanent architecture it scatters business logic across report authors and becomes the governance problem it was meant to avoid.
In This Article
The Real Problem Composite Models Solve
It is not a reporting problem. It is a timing problem. The business needs one report that puts SAP financials, Dynamics 365 sales and a planning spreadsheet side by side — and it needs it this quarter, not after the data warehouse project finishes next year.
That gap between "the number the CFO wants now" and "the governed platform that does not exist yet" is exactly where composite models live. They let you combine sources in a single Power BI model without first landing everything in one place. Used with discipline, that is a genuinely useful bridge. Used without it, it is how business logic ends up scattered across forty report authors who each built their own version of margin.
The technique is sound. The failure is treating a bridge as a destination.
Composite models solve a timing problem, not a reporting problem: one report across SAP, D365 and a spreadsheet before the warehouse exists. The failure is treating the bridge as the destination.
What a Composite Model Actually Is
A composite model is a single Power BI model whose tables do not all share one storage mode. Some tables are Imported — a compressed in-memory copy, fast and self-contained. Others are DirectQuery — queried live from the source at report time. A composite model lets both coexist, with relationships defined across them.
That is what makes the SAP + D365 + Excel report possible without a warehouse: the large SAP fact table can be DirectQuery against the source, the Dynamics 365 tables can be Imported, and the planning spreadsheet is Imported from SharePoint — all in one model, one set of relationships, one report.
Microsoft supports many-to-many relationships and storage-mode control per table specifically so this kind of blended model can be built and governed rather than hacked together with report-level merges.
The SAP + D365 + Excel Pattern
The concrete shape we see most often in mid-market industrials: SAP S/4HANA or SAP ByDesign holds finance and logistics, Dynamics 365 holds sales and CRM, and a planning or budget spreadsheet holds the numbers finance maintains by hand. The board wants all three reconciled in one view.
The composite model that works keeps the large, slow-changing SAP data in DirectQuery so you are not copying millions of rows, brings the smaller Dynamics 365 dimensions into Import for speed, and imports the spreadsheet. The join key discipline is where it succeeds or fails — a shared, cleaned product and customer master across the three sources, not three different codes glued together with best-guess mapping.
Get the keys right and the report is genuinely useful within a sprint. Get them wrong and you have built a fast way to produce three contradictory numbers on one page.
Chaining Onto a Governed Model
The version of composite models that matters most for governance is DirectQuery for Power BI semantic models: a report author can take a governed enterprise model — the one IT owns, with its certified measures — and extend it with their own local tables, without copying or forking it.
This is the pattern that lets you say yes to the business without losing control. The central model keeps owning OEE, OTIF and margin; the analyst adds their local scenario spreadsheet on top; and the certified measures still resolve from the governed model. When IT fixes the margin definition centrally, every chained report inherits the fix.
It is the difference between "the analyst rebuilt margin their own way" and "the analyst extended the one true margin with a local assumption." The first scatters logic. The second contains it.
DirectQuery for Power BI semantic models lets an analyst extend a governed model with local tables without forking it — so certified measures still resolve centrally, and a central fix flows to every chained report.
Where It Quietly Becomes a Governance Problem
Composite models fail slowly, which is what makes them dangerous. The first blended report is a triumph. The tenth, built by a different author with a different join key and a locally redefined measure, is the start of the same metric meaning three different things in three different reports.
The other quiet cost is performance. DirectQuery tables in a composite model push queries to the source at report time; if the SAP layer is slow or the joins are wide, the report that looked fine on a demo dataset crawls in production. And row-level security across mixed storage modes needs deliberate design — it is easy to secure the Import tables and leave a DirectQuery table exposed.
None of this is an argument against composite models. It is an argument for treating them as a governed bridge with an owner and an expiry date, not as the permanent architecture. When the same blended logic is being rebuilt for the third time, that is the signal to promote it into a governed model on Microsoft Fabric — one gold layer, one definition, read in Direct Lake.
So What — The Honest Rule
Use a composite model when you must combine SAP, Dynamics 365 and a spreadsheet before the platform exists, and you have the join-key discipline to do it without producing contradictory numbers. Chain onto a governed model rather than forking it whenever the certified measures already exist.
Stop using composite models as the answer the moment the same blend is being rebuilt repeatedly, or the moment a DirectQuery source makes the report too slow to trust. That is when the timing problem has been solved and the real fix — a governed model over unified data in OneLake — has become worth the investment.
The technique buys you time. What you do with the time is the actual decision. Industrial businesses do not lack data; they lack a single version they can trust — and a composite model is a way to bridge to that, not a substitute for it.
Composite models buy time. When the same blend is rebuilt for the third time, or a DirectQuery source is too slow to trust, promote it into one governed model on Fabric — one definition, read in Direct Lake.
If you are stitching SAP, Dynamics 365 and spreadsheets into one report because the platform is not there yet, a composite model is a reasonable bridge — but it needs an owner and an expiry date. 30 minutes with Amit on where the bridge ends and a governed model on Microsoft Fabric begins. No slides. No pitch deck. No obligation to proceed.
Free Assessment
Where does your operation sit on the data maturity curve?
8 questions. 3 minutes. You get a scored breakdown across data infrastructure, analytics readiness, and automation potential — with a specific next step for your industry.