All 35 lessons
Start here: cloud, data, Fabric and Power BI basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
18 min
Composite models and large semantic model storage format
Combine storage modes and sources in a composite model and know when to turn on large model format.
By the end of this lesson you can
- Design a composite model and predict which relationships become limited
- Extend a shared semantic model with your own tables, using DirectQuery to the model
- Decide when to enable large semantic model storage format, and how
From earlier lessons · 1 of 2
A model has a DirectQuery Sales table and an Import Targets table from the same SQL source. Both relate to Date. Which storage mode for Date lets slicers read the cache and joins to Sales still run at the source?
Pick one answer.
From earlier lessons · 2 of 2
Which storage mode needs a Fabric capacity, and can't run with Power BI Pro licenses alone?
Pick one answer.
Juniper Coffee's central BI team owns the endorsed Sales semantic model. Finance wants budget against actuals, and the budget lives in an Excel file the BI team won't add. Finance can't edit the shared model. Meanwhile the loyalty model, an Import model, just failed a refresh at its 1 GB size limit. Two problems, both about how a model is built and stored.
What a composite model is
Exam depth: know it cold.
A composite model has tables in more than one storage mode, or DirectQuery tables from more than one source. Import tables from many sources alone aren't composite. Neither are Direct Lake tables alone.
| Combination | Where you can build it |
|---|---|
| Import plus DirectQuery. | Power BI Desktop and the service. |
| DirectQuery from two or more sources. | Power BI Desktop and the service. |
| DirectQuery to a Power BI semantic model, plus your own tables. | Power BI Desktop only. |
| Direct Lake plus Import. | Power BI web modeling only. |
| Direct Lake plus DirectQuery. | XMLA tools only. |
Source groups and limited relationships
Exam depth: know it cold.
A source group is the set of tables from one source. Each DirectQuery source is a group. All Import tables form one group, and Direct Lake tables join that group too.
A relationship is regular when the engine knows which side is the unique "one" side. It's limited when there's no guaranteed one side:
- it crosses two source groups, or
- it's many-to-many.
What changes across a limited relationship:
- Joins use inner join rules. Rows with no match disappear, with no blank row.
RELATEDcan't fetch the one-side value.- Row-level security has topology limits.
Model view marks a limited relationship with ( ) after the cardinality signs.
Predict, then reveal
Store 117 opened last week. It has sales in the remote Sales model but no row in finance's imported Budget table, and the two are related across source groups. A table visual shows Store and Budget. Does store 117 appear?
You are an analytics engineer at Juniper Coffee. A calculated column in the imported Budget table uses RELATED to fetch Store[Region]. Store comes from the shared Sales model through DirectQuery. The column fails. Why?
Pick one answer.
Keep relationships regular with Dual
Exam depth: know it cold.
When Import and DirectQuery tables come from the same source, Dual mode keeps their relationships regular.
| Many side | One side that stays regular |
|---|---|
| Import. | Import or Dual. |
| DirectQuery. | DirectQuery or Dual. |
| Dual. | Dual. |
A cross-source relationship is regular only when both tables are Import. Many-to-many is always limited. So set dimensions such as Store and Date to Dual when DirectQuery fact tables from the same source use them.
Extend a shared model with DirectQuery
Exam depth: know it cold.
You can build on a published semantic model without copying its data or editing it. This also works for Azure Analysis Services and SQL Server 2022 Analysis Services models.
- In Power BI Desktop, connect to the Power BI semantic model. This starts as a live connection, with no local model.
- Select Make changes to this model. The live connection becomes DirectQuery to the semantic model, and Desktop creates a local model.
- Add your own Import or DirectQuery tables, such as the Excel budget, plus local measures.
You need Build permission on the source model, and report viewers need Read on every model in the chain. A chain can be at most three models long.
The tenant setting Allow DirectQuery connections to Power BI semantic models must stay on to publish. It's on by default. On Premium or PPU, the XMLA endpoint must be Read Only or Read Write.
You are a BI developer in Juniper Coffee's finance team. You need budget against actuals. Actuals are in the central Sales semantic model, which you can't edit. You must not copy the sales data. What should you do in Power BI Desktop?
Pick one answer.
Direct Lake in a composite model
Exam depth: know it cold.
Direct Lake has two flavors, covered in lesson 24. For composite models, only one matters:
- Direct Lake on OneLake tables can mix with Import tables from any source. You combine them in Power BI web modeling. Adding DirectQuery tables needs XMLA tools.
- Direct Lake on SQL can't share a model with Import, DirectQuery or Dual tables. You can still build a separate composite model on top of it, with DirectQuery to that semantic model.
Direct Lake and Import tables sit in the same source group, so relationships between them can stay regular.
Aggregation tables
Exam depth: recognize it.
A user-defined aggregation is a small, pre-summarized Import table, such as sales by store by day. It sits beside a huge DirectQuery detail table. You map it in Manage aggregations.
- Queries at a grain it covers are answered from memory. Others go to the source.
- The detail table must be DirectQuery. The aggregation table is hidden from report users.
- Hits based on relationships need regular relationships, so related dimensions are usually Dual.
- Aim for at least 10 times fewer rows than the detail table.
Direct Lake models don't support user-defined aggregations.
Large semantic model storage format
Exam depth: know it cold.
By default a semantic model is stored in the small format, with a 1 GB size limit. Large semantic model storage format lifts that limit up to the capacity's per-model memory, or a lower maximum that the capacity admin sets.
Turn it on in three cases.
- The model will grow past 10 GB. Here it's required.
- Incremental refresh will grow it in the service. Desktop uploads are capped at 10 GB.
- You'll write to the model through the XMLA endpoint. It improves XMLA write performance, even for small models.
It's available on Fabric F SKUs, Premium P SKUs, Embedded A SKUs and Premium Per User (up to 100 GB). Pro workspaces stay at 1 GB.
Exam depth: know it cold.
Where to turn it on:
- One model: in the service, open the model's Settings. Expand Large semantic model storage format, set it On and select Apply. Older screens call it Large dataset storage format.
- Every new model in a workspace: workspace Settings > Premium > Default storage format > Large semantic model storage format.
- A model also converts automatically the first time you open it for editing in the web, or live edit a Direct Lake model in Desktop.
Otherwise new models start in the small format.
| Fabric SKU | Max memory per model |
|---|---|
| F2, F4, F8. | 3 GB. |
| F16. | 5 GB. |
| F32. | 10 GB. |
| F64. | 25 GB. |
| F128. | 50 GB. |
| F256. | 100 GB. |
These are upper bounds. Refreshes and queries need headroom too.
You are an analytics engineer at Juniper Coffee. The loyalty Import model will hold five years of history through incremental refresh, about 18 GB, on an F64 capacity. You'll publish it from Power BI Desktop. What should you do before the first refresh in the service?
Pick one answer.
Decision rules for this lesson
Exam depth: know it cold.
Match the phrase in the question to the move.
| The question says | Do this |
|---|---|
| Extend a shared model you can't edit, no copy. | DirectQuery to the semantic model, then add local tables. |
| Live DirectQuery facts, fast slicers, same source. | Dual dimensions. |
| Rows vanish, or RELATED fails, across sources. | It's a limited relationship. |
| Direct Lake model plus an Excel table. | Direct Lake on OneLake plus Import, in web modeling. |
| Model over 10 GB, or XMLA writes. | Large semantic model storage format. |
Exam-style practice
You are a BI developer at a grocery chain. Your 400 MB model sits on an F32 capacity. The team will deploy changes with an XMLA-based tool, writing to the model. You need the best XMLA write performance. What should you do?
Pick one answer.
You are an analytics engineer at Juniper Coffee. A Direct Lake on SQL model covers the gold Lakehouse. Finance wants a small Excel target table in the same model, related to Store. Which approach works?
Pick one answer.
Worth keeping
- A composite model mixes storage modes or DirectQuery sources; each DirectQuery source is its own source group.
- Cross-group and many-to-many relationships are limited: unmatched rows vanish and RELATED fails.
- Extend a shared model with Make changes to this model; only Direct Lake on OneLake mixes with Import.
- Large format lifts the 1 GB limit to the SKU's memory: required past 10 GB, wise for XMLA writes.
On the job
Juniper Coffee's finance analyst builds the budget report on the shared Sales model with DirectQuery to the model. The central team keeps one trusted set of measures. Finance owns only its budget table and two local measures.
The analytics engineer reviews it first. Does every store have a budget row, so nothing vanishes across the limited relationship? Is every Sales table protected by RLS? The loyalty model, meanwhile, moves to large format before its five-year history loads. Neither change is visible to report users, and that's the point.
Direct Lake keeps showing up with two flavors and a fallback warning. Next lesson opens the box: framing, automatic updates, DirectLakeBehavior and when each flavor is the right call.
Leaderboard
Loading…
50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.
Sources for this lesson (14)
Each fact in the lesson is marked with the number of the page it comes from.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [45]Microsoft Learn: Direct Lake overview
- [60]Microsoft Learn: Model relationships in Power BI Desktop
- [200]Microsoft Learn: Table storage mode in Power BI semantic models
- [202]Microsoft Learn: Power BI Premium Per User
- [203]Microsoft Learn: What is Power BI Premium?
- [204]Microsoft Learn: Large semantic models in Power BI Premium
- [240]Microsoft Learn: Use composite models in Power BI
- [241]Microsoft Learn: RELATED
- [242]Microsoft Learn: User-defined aggregations
- [243]Microsoft Learn: Composite model guidance in Power BI Desktop
- [244]Microsoft Learn: Use semantic model version history
- [245]Microsoft Learn: Configure incremental refresh and real-time data for Power BI semantic models
- [246]Microsoft Learn: Semantic model connectivity with the XMLA endpoint