4.5Dataflows & composite models
The essentials in 30 seconds
- A dataflow = Power Query in the cloud: queries centralized in the Service, reusable by several reports — prepare once, consume everywhere.
- The live connection to a published semantic model installs a single source of truth: measures defined once, consumed by all.
- The composite model combines a published central model AND local tables (e.g. an Excel budget): powerful, to be reserved for real needs — and subject to Pro/Premium licenses.
Running mini-case — Zineb thinks through the team architecture
Zineb, a management controller at an agri-food SME in Algiers, notices that three of the team's reports redo exactly the same cleanup of the product reference and the date table. Instead of fixing each .pbix file one by one, she thinks in terms of architecture. She applies the section: this repeated cleanup becomes a dataflow candidate (Power Query in the Service, prepared once); the sales model, with its key measures, becomes the source-of-truth model published once and consumed by live connection; and a report that must add its local budget will use a composite model. She writes the roadmap in three lines: dataflows (preparation) → central model (measures) → connected reports. She notes the milestone: these building blocks require a Pro license — the concepts are mastered right now.
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 4.5.
1. Share the preparation
When several reports repeat the same Power Query cleanup, it's time to share it: dataflows move the preparation into the Service — a central query (the cleaned product reference, the date table) that all reports consume. Prepare once, reuse everywhere: when the rule changes, you change it in one place. Zineb spots each repeated cleanup as a dataflow candidate.
2. A single source of truth
The live connection to a published semantic model installs a single source of truth: key measures are defined once and consumed by all connected reports. No more "different revenue figures per report". Zineb therefore publishes her sales model once, and her reports connect to it rather than redefining each measure.
3. Composite models
When a report must combine a published central model AND local data, composite models allow it: connect to the source-of-truth model and add your Excel budget table. It's powerful but more complex, to be reserved for real needs. Zineb also notes that these team building blocks rely on Pro/Premium licenses — the concepts are learned now, the tooling will follow.
🔎 Concrete example — 🔎 Concrete example — The team roadmap
Zineb writes the target architecture in three lines:
Dataflows → prepare the references once (products, calendar)
Central model → key measures defined once, published
Reports → live connection to the model (ready-made measures)
+ Composite → a report adds its local budget table.Reading: each repeated cleanup is a dataflow candidate; each shared measure, a central-model candidate.
Lesson: these building blocks structure teamwork — their value arises with several reports/authors.
Section vocabulary
- Dataflow
- Power Query in the cloud: queries centralized in the Service, reusable by several reports — prepare once, consume everywhere.
Simple example: Zineb centralizes the product reference cleanup there.
What it is not: redoing the same cleanup in each .pbix.
Reflex: repeated cleanup → dataflow.
- Semantic model (dataset)
- The published model (tables, relationships, measures): other reports can connect to it live (live connection) without duplicating.
Simple example: Zineb publishes her sales model once.
What it is not: a model recreated in each report.
Reflex: publish once → reuse everywhere.
- Live connection
- The report consumes a published model as is: a single source of truth, several connected reports.
Simple example: Zineb's reports connect to it: the measures arrive ready-made.
What it is not: redefining the measures in each report.
Reflex: a source of truth → live connection.
- Composite model
- The mix: connection to a published model + added local tables (DirectQuery on a semantic model) — maximum flexibility, complexity in proportion.
Simple example: Zineb adds her local budget table to the central model.
What it is not: duplicating the whole model for a small table.
Reflex: central + local → composite (sparingly).
- A single source of truth
- The architecture principle: key measures defined ONCE, consumed by all — no more "different revenue figures per report".
Simple example: Zineb guarantees a single revenue for the whole team.
What it is not: figures that diverge from one report to the next.
Reflex: one definition, only once.
In practice — In practice — Thinking through the team architecture (design exercise)
- Take inventory: how many of your team's reports redo the SAME cleanup (references, calendar, mapping)? Each duplicate is a dataflow candidate.
- Identify the potential "source-of-truth" model: which model deserves to be published once and consumed by all (live connection)?
- If you have a Pro license: try connecting to a published model (Get data → Power BI semantic models) — the measures arrive ready-made.
- Write the target architecture in 3 lines: dataflows (preparation) → central model (measures) → connected reports. It's the team roadmap.
Step-by-step script — thinking architecture like Zineb (7 min)
1) List the repeated cleanups in your team (product reference, date table, mapping): each one is a dataflow candidate. 2) Choose the "source-of-truth" model: the one whose measures deserve to be defined once. 3) If you have Pro, Get data → Power BI semantic models and connect to that model: the measures arrive ready-made. 4) For a local data need, plan a composite model (central + Excel table). 5) Write the roadmap: dataflows → central model → connected reports.
These building blocks require a Pro/Premium license: the concepts are learned now, access will follow depending on your organization.
Copy-paste template — my team roadmap
Repeated cleanups: ___ → dataflow (preparation once)
Source-of-truth model: ___ → published, measures defined ONCE
Reports: live connection to the central model (ready-made measures)
Local need: ___ → composite model (+ local table, if a real need)
Chain: dataflows → central model → connected reports.
Licenses: Pro/Premium for these team building blocks.Dataflow
Zineb centralizes the product reference cleanup: a single dataflow for all reports.
Source-of-truth model
Zineb publishes her sales model once; the reports connect to it via live connection.
Composite
Zineb adds her local budget table to the central model, without duplicating it.
End of divergent revenues
Zineb defines the measures once: no more "different revenue figures per report".
✓ Best practices
- Turning each repeated cleanup into a shared dataflow.
- Publishing a source-of-truth model consumed by live connection.
- Reserving composite for real needs (central + local).
✗ Common pitfalls
- Duplicating the same cleanup across ten isolated .pbix files.
- Multiplying source models ("different revenue figures per report").
- Adopting composite without necessity (complexity in proportion).
Key takeaways
- Dataflow = shared Power Query in the cloud: prepare once, reuse everywhere.
- Live connection to a published model = a single source of truth, multiple reports.
- Composite combines a central model + local data: to be reserved for real needs.
- These building blocks structure teamwork: their value arises with several reports/authors.
48h challenge — apply it tomorrow
Like Zineb, list your team's repeated cleanups and designate the potential source-of-truth model, then write the target architecture in three lines: dataflows → central model → connected reports. Note in one sentence what the live connection will save you.
- I can explain what a dataflow is and when it is needed.
- I know what a live connection to a published model brings (a single source of truth).
- I understand what a composite model is and I reserve it for real needs.
Frequently asked questions
Dataflow or query in the .pbix: how to decide?
The criterion is reuse. One use = local query in the .pbix; several uses (several reports/authors) = dataflow. The benefit goes beyond time: when the rule changes, you change it once and all reports inherit it.
What licenses for all this?
Dataflows and live connections to published models require a Pro license; some advanced options fall under Premium/Fabric. It's an organizational matter: the concepts are mastered right now, for free.
What does a live connection to a published semantic model bring?