Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

18 min

XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models

Manage models through the XMLA endpoint and build reusable templates, data source files and shared models.

By the end of this lesson you can

  • Turn on XMLA read write and connect a tool to a workspace
  • Say which XMLA tasks need read only and which need read write
  • Tell a .pbit template from a .pbids data source file
  • Choose between a shared model, a composite model and a template

From earlier lessons · 1 of 2

After a first deployment to Juniper's new Prod stage, the semantic model has no data. Why?

Pick one answer.

From earlier lessons · 2 of 2

Where does Notify contacts in the impact analysis pane send its email?

Pick one answer.

[306][312]

Juniper Coffee has 14 reports on store sales. Each has its own copy of the sales model, and their totals disagree. Finance also wants one month of history reloaded without a full refresh, and a calculation group added with Tabular Editor. Your job: one trusted model, managed with the right tools, reused everywhere.

[6]

The XMLA endpoint

Exam depth: know it cold.

A semantic model runs on the Analysis Services engine. XMLA (XML for Analysis) is that engine's protocol. The XMLA endpoint lets tools outside Power BI Desktop connect to models in a workspace on a Fabric, Premium or Premium Per User capacity.

  • Read only is the default. Tools can query data and read metadata.
  • Read write also lets tools change models: deploy, edit and refresh.

A capacity admin sets it per capacity: Admin portal > Capacity settings > capacity > Power BI workloads > XMLA Endpoint. The choices are Off, Read Only and Read Write. The setting applies to every workspace on that capacity.

The tenant setting Allow XMLA endpoints and Analyze in Excel with on-premises semantic models must also be on. It is by default.

[246][314]

Capacity workload settings with the XMLA Endpoint drop-down open, showing the options Off, Read Only and Read Write, with Read Write selected.
Microsoft Learn, CC BY 4.0

[246]

Exam depth: know it cold.

To connect, a tool needs the workspace's connection URL. Copy it from Workspace settings > Premium > Workspace Connection. Tools treat it like an Analysis Services server name. Each semantic model in the workspace appears as a database.

  • myorg stands for your own tenant.
  • Spaces in the workspace name are written as %20.
  • Some tools also need the model name, called the initial catalog.

Your workspace role decides what you can do. Contributor or higher can change models, like an Analysis Services database admin. Build permission alone gives read access, and RLS still filters the rows.

[246]

Workspace settings, Premium tab, showing the Workspace Connection box with the URL powerbi://api.powerbi.com/v1.0/myorg/Contoso Sales and a Copy button.
Microsoft Learn, CC BY 4.0

[246]

text
Server: powerbi://api.powerbi.com/v1.0/myorg/Juniper%20Finance
Database: Regional Sales
Juniper's finance workspace as an XMLA server name. The model you open is the database, or initial catalog.

[246]

What you do through the endpoint

Exam depth: recognize it. Know which tasks need read write; skip each tool's setup.

TaskExample toolsSetting needed
Query data, trace queries.DAX Studio, Excel, Power BI Report Builder, SQL Server Profiler.Read only.
Edit metadata: calculation groups, perspectives, OLS.Tabular Editor.Read write.
Refresh one table or partition.SQL Server Management Studio (SSMS), PowerShell.Read write.
Compare models, deploy metadata only.ALM Toolkit.Read write.
Deploy a model project.Visual Studio, Analysis Services Deployment Wizard.Read write.

XMLA refreshes skip the service's 48-per-day limit and its timeout. XMLA can't set data source credentials; set those in the model's settings in the service.

[246][315]

You are an analytics engineer at Juniper Coffee. The Sales model uses incremental refresh with five years of history. A source fix changed last March's rows. You need to reload only that month's partition, without reloading all history. What should you use?

Pick one answer.

[246]

[246][315][316][317]

Read only lets you look. Read write lets you change things. Your tool needs to know which one it got. Picture: Retro pixel art scene: a small pixel-art hero holding a wrench stands before a big castle gate labeled "MODEL".

.pbit: a Power BI template

Exam depth: know it cold.

A Power BI template (.pbit) is a report file with everything except the data:

  • Report pages and visuals.
  • The model definition: tables, relationships and measures.
  • All queries and query parameters.

Create one with File > Export > Power BI template, and add a description. Opening a .pbit asks for parameter values, then loads data with your own credentials. The result is a new, separate report file.

Use it when many reports need the same layout and model but point at different data, such as one per region. Filter values typed into visuals can stay in the template's metadata.

[318]

.pbids: a Power BI data source file

Exam depth: know it cold.

A Power BI data source file (.pbids) is a small JSON file that holds one connection. Opening it starts Power BI Desktop at that source. The user signs in, then picks tables in Navigator. No report, model or data is inside.

  • One data source per file. More than one is an error.
  • No credentials and no table list are stored.
  • mode can be Import or DirectQuery. If it's missing, Desktop asks.
  • Live connections aren't supported.

Create one from File > Options and settings > Data source settings > Export PBIDS. Use it to give new report authors the right source without typing server names.

[319]

json
{
"version": "0.1",
"connections": [
{
"details": {
"protocol": "tds",
"address": { "server": "juniper-pos.database.windows.net", "database": "Till" }
},
"mode": "Import"
}
]
}
A .pbids file that opens Desktop connected to Juniper's till database in Import mode. The tds protocol means SQL Server.

[319]

Shared semantic models

Exam depth: know it cold.

A shared semantic model is one published model that many reports use. Each report holds visuals only and connects to it with a live connection. These are sometimes called thin reports.

  1. Publish the model, then endorse it, ideally as Certified.
  2. Give report authors Build permission. Contributors and higher already have it.
  3. Authors pick Get data > Power BI semantic models in Desktop, then the model.

A live-connected author can't add tables or change the model. They can add report-level measures. Fix the model once, and every report sees the change. RLS still applies to them.

[53][264]

Exam depth: recognize it.

What if an author needs to add their own table to the shared model? From a live connection, select Make changes to this model. Desktop adds a local model that reaches the shared model with DirectQuery. The author can then add tables, such as an Excel budget, and new columns.

The result is a composite model that extends the shared one without copying its data. The tenant setting Allow DirectQuery connections to Power BI semantic models must be on to publish it.

[240]

You are a data analyst at Juniper Coffee. Your report is live connected to the certified Regional Sales model. You need to add your own Excel table of store targets to it. The shared model's data must not be copied. What should you do?

Pick one answer.

[53][240]

Which reusable asset?

Exam depth: know it cold.

You needUseWhat it carries
One trusted model for many reports.Shared semantic model.The published model; reports connect live.
Same pages, model and queries, new data each time..pbit template.Report and model definition, no data.
Authors start at the right source..pbids file.One connection, no credentials.
Shared model plus your own tables.Composite model.Local model with DirectQuery to the shared one.
Source control for a report and model..pbip project.Text definitions for Git.

Decision rule: reuse the data and logic, share a model. Reuse the design, use a .pbit. Reuse just the connection, use a .pbids.

[53][240][318][319]

Exam-style practice

You are an analytics engineer at Juniper Coffee. You connect Tabular Editor to the finance workspace and add a calculation group. Saving fails with an error that XMLA read/write is disabled. Queries from DAX Studio work fine. What should the capacity admin do?

Pick one answer.

[246]

You are a BI developer at Juniper Coffee. Each region builds its own budget report. All four must have the same pages, measures and a Region parameter, but load only their region's data. You need to hand regional analysts one reusable file. What should you create?

Pick one answer.

[318][319]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 14

[240][246][264][318][319]

Worth keeping

  1. XMLA is read only by default; a capacity admin sets Read Write in the capacity's Power BI workloads.
  2. Connect with powerbi://api.powerbi.com/v1.0/myorg/Workspace from Workspace settings.
  3. Writes such as partition refresh or calculation groups need read write; queries don't.
  4. .pbit reuses design without data; .pbids reuses one connection; a shared model reuses data and logic.
  5. Extend a shared model with Make changes to this model, which builds a composite model.

On the job

At Juniper Coffee, the analytics engineer retires the 14 private sales models. She certifies one Regional Sales model and gives the store-manager group Build permission. Each manager's report becomes a thin report on that model. For finance, she uses Tabular Editor over the XMLA endpoint to add a time-intelligence calculation group. When last March's tills are corrected, she refreshes just that partition from SSMS. New analysts get a .pbids file for the till database, and regional budget reports start from one .pbit. Totals now match everywhere.

[315]

You've now covered every skill on the DP-600 outline. Next: Mock exam 1, a full-length timed test across all three areas, with a score per area and the lessons to revisit.

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 (13)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [53]Microsoft Learn: Connect to semantic models in the Power BI service from Power BI Desktop
  3. [240]Microsoft Learn: Use composite models in Power BI
  4. [246]Microsoft Learn: Semantic model connectivity with the XMLA endpoint
  5. [264]Microsoft Learn: Build permission for shared semantic models
  6. [306]Microsoft Learn: The deployment pipelines process
  7. [312]Microsoft Learn: Impact analysis
  8. [314]Microsoft Learn: Manage your capacities in the Admin portal
  9. [315]Microsoft Learn: Power BI usage scenarios: Advanced data model management
  10. [316]Microsoft Learn: Previous monthly updates to Power BI Desktop and the Power BI service
  11. [317]Microsoft Learn: Download a report from the Power BI service to Power BI Desktop
  12. [318]Microsoft Learn: Create and use report templates in Power BI Desktop
  13. [319]Microsoft Learn: Get data in Power BI Desktop