Introduction
Here's a scenario that plays out in enterprise data teams more often than anyone admits: a Power BI semantic model is sitting in a Premium workspace, humming along, serving reports to hundreds of users — and then someone needs to do something the Power BI interface simply won't let them do. Maybe you need to script a partial refresh of a single partition in a 50GB fact table without touching the rest of the model. Maybe you want to deploy a model through a CI/CD pipeline without clicking through a browser. Maybe your DBA needs to run a diagnostic query against the model's internal engine to figure out why a particular measure is slow. The Power BI service UI is nowhere near enough. You need direct access to the analytical engine underneath.
That direct access is exactly what XMLA endpoints provide. XMLA — XML for Analysis — is an industry-standard protocol that exposes the Analysis Services engine running underneath every Power BI Premium and Fabric capacity. When you connect to a Power BI workspace through an XMLA endpoint, you're talking directly to the same Tabular engine that powers Azure Analysis Services and SQL Server Analysis Services. You can query it with DAX and MDX, manage it with Tabular Model Scripting Language (TMSL), connect third-party tools like Tabular Editor, DAX Studio, and Excel, and treat it as a proper enterprise analytical database rather than a black box behind a web portal.
By the end of this lesson, you will have genuine, practical command over XMLA endpoints — not just the ability to flip a toggle and paste a connection string.
What you'll learn:
- How XMLA endpoints work architecturally and why they exist in the Power BI ecosystem
- How to configure workspace settings and connect external tools including DAX Studio, Tabular Editor, and SSMS
- How to write and execute TMSL scripts for model management tasks including selective partition refresh, role management, and metadata scripting
- How to query semantic models using DAX and MDX over XMLA connections, including performance considerations
- How to build a practical CI/CD workflow using the XMLA endpoint with the Analysis Services deployment tools
Prerequisites
Before working through this lesson, you should be comfortable with:
- Power BI Desktop and the Service at an intermediate-to-advanced level
- Basic DAX — you don't need to be an expert, but you should understand measures and calculated tables
- General familiarity with tabular model concepts (tables, relationships, partitions)
- A Power BI Premium Per User (PPU), Premium capacity (P-SKU), or Microsoft Fabric capacity workspace — XMLA endpoints are not available on Pro or free workspaces
- Administrator or Member role in the target workspace
If you're on a Power BI trial, PPU licenses include XMLA endpoint access, which makes it practical to follow along.
Understanding XMLA Endpoints Architecturally
Before you connect anything, you need a mental model of what's actually happening when you use an XMLA endpoint — because the mental model shapes every decision you make downstream.
What Powers BI is Actually Running
When you publish a .pbix file or a .pbip project to a Premium workspace, Power BI doesn't store it as a blob and re-render it on demand. It loads the semantic model into an instance of the Analysis Services Tabular engine. This is the same engine — literally the same codebase — that runs Azure Analysis Services and the on-premises SQL Server Analysis Services in Tabular mode. Power BI's branding of "semantic models" is, at the engine level, just a Tabular database.
The Analysis Services engine exposes two interface layers:
- XMLA — an XML-based protocol over HTTP for management operations (scripting, schema changes, process/refresh commands)
- MDX and DAX — query languages that ride on top of the same XMLA channel
When Power BI added XMLA endpoint support (generally available since 2020), it opened a port that lets external clients speak directly to this engine using the same protocols that AAS and SSAS have supported for years. The Power BI service sits in front and handles authentication, but once authenticated, you are talking to the Analysis Services engine.
Read vs. Read-Write: The Critical Distinction
XMLA endpoints have two modes:
Read-only: Allows query execution (DAX, MDX) and metadata browsing. Any client can connect and query the model, but cannot modify schema, refresh data, or change partitions.
Read-write: Allows full Analysis Services management operations — TMSL scripting, partition management, role modifications, incremental refresh policy overrides, and deployment of model changes.
Read-write is where most of the power lives, and it comes with commensurate risk. You can break a production model from a command line if you're not careful. We'll address governance patterns for this later.
The Authentication Layer
The XMLA endpoint authenticates through Azure Active Directory (now Microsoft Entra ID). It does not support SQL authentication. This means:
- Interactive OAuth — works for tools like SSMS and Tabular Editor when you authenticate through a browser popup
- Service principals — the correct approach for CI/CD pipelines and automated processes
- Personal access tokens — not supported; don't try
Service principal authentication requires that the workspace admin enable "Allow service principals to use Power BI APIs" in the tenant admin settings, and the service principal must be added as a workspace member with at least Contributor role.
Enabling XMLA Endpoints in Your Workspace
Tenant-Level Settings
Before workspace-level configuration works, a Power BI tenant administrator needs to enable XMLA endpoints at the tenant level. In the Power BI Admin Portal, navigate to Tenant settings, scroll to the Integration settings section, and find "Allow XMLA endpoints and Analyze in Excel with on-premises datasets." This setting needs to be enabled — either for the entire organization or for specific security groups.
Additionally, for read-write access, find the setting "Allow users to work with Power BI datasets in Excel using a live connection" — though read-write endpoint behavior is primarily governed by capacity-level settings.
Capacity-Level Settings
For Premium capacities (P-SKUs), navigate to the Admin Portal, select Capacity settings, choose your capacity, and scroll to the Power BI workloads section. Find the XMLA Endpoint dropdown and set it to either Read Only or Read Write. For Fabric capacities, this is configured through the Fabric Admin Portal under capacity settings.
Warning: Enabling read-write at the capacity level enables it for every workspace on that capacity. Think carefully about governance before doing this in a multi-team environment. You may want to use separate capacities for development/test (read-write) and production (read-only) as a control pattern.
Workspace-Level Settings
Individual workspaces don't have a separate XMLA toggle — the capacity setting governs all workspaces on that capacity. However, workspace access control (who has what role) is your primary governance lever. Only users with Admin, Member, or Contributor roles can use the XMLA endpoint for write operations. Viewers get read-only access.
Finding Your XMLA Endpoint URL
Once enabled, the connection string for a workspace is available in the workspace settings. In the Power BI service, navigate to your workspace, click the three-dot menu next to the workspace name, select Workspace settings, then go to the Premium tab. You'll see a field labeled Workspace Connection with a value that looks like:
powerbi://api.powerbi.com/v1.0/myorg/YourWorkspaceName
This is your XMLA connection string. The format is always powerbi://api.powerbi.com/v1.0/myorg/ followed by the URL-encoded workspace name. Spaces in workspace names are preserved (some tools handle this; others need the URL-encoded version with %20).
For Fabric workspaces, the endpoint format differs slightly:
powerbi://api.powerbi.com/v1.0/myorg/YourFabricWorkspace
The format is the same, but the underlying infrastructure is Fabric capacity rather than Premium.
Connecting DAX Studio
DAX Studio is the gold standard for DAX query execution and performance diagnostics over an XMLA connection. Download it from daxstudio.org — it's free and open source.
Launch DAX Studio and in the connection dialog, select "Power BI / SSAS Tabular" from the connection type dropdown. Enter the workspace XMLA connection string in the server field:
powerbi://api.powerbi.com/v1.0/myorg/SalesAnalytics
Click Connect. DAX Studio will prompt you for Azure AD credentials. After authentication, use the database dropdown at the top of the screen to select the specific semantic model (database) you want to connect to. Each published dataset in the workspace appears as a separate database.
Once connected, you can run DAX queries directly:
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Year],
'Date'[Month],
"Total Revenue", [Total Revenue],
"Units Sold", [Units Sold],
"Revenue per Unit", DIVIDE([Total Revenue], [Units Sold])
)
ORDER BY 'Date'[Year], 'Date'[Month]
DAX Studio also exposes the Server Timings pane, which shows you the actual time spent in the storage engine vs. formula engine — invaluable for performance diagnostics that you simply cannot do inside the Power BI service.
Connecting Tabular Editor 2 and 3
Tabular Editor is essential for model management over XMLA. Tabular Editor 2 is free and open source. Tabular Editor 3 is commercial and adds significant capabilities including a DAX editor, data refresh UI, and pivot grid.
In Tabular Editor 2, open the File menu and select Open > From DB (Read/Write). In the connection dialog, enter the XMLA endpoint as the server, authenticate, and select the model. You'll see the full tabular model object tree — tables, measures, columns, partitions, roles, perspectives, and relationships — all editable.
Critical pattern: Always work in a development workspace with a copy of the model before making XMLA-based edits to a production model. A dropped partition or a misconfigured measure applied directly to production can be catastrophic. Treat the XMLA endpoint like database write access — because that's exactly what it is.
Connecting SQL Server Management Studio (SSMS)
SSMS connects to XMLA endpoints through its Analysis Services connection dialog. Open SSMS, select Connect > Analysis Services from the Object Explorer menu. In the Server Name field, enter the XMLA endpoint URL. Set Authentication to Azure Active Directory - Universal with MFA (or Active Directory - Password for service principal connections). Click Connect.
SSMS will show the workspace as an Analysis Services instance and list each published dataset as a database. You can right-click databases to script them, execute TMSL in a new query window, and browse the model schema through the graphical interface.
Connecting Excel
Excel's "Get Data > From Analysis Services" dialog accepts XMLA endpoint URLs directly. After authentication, Excel presents the model's tables and perspectives for use in pivot tables. This is particularly useful when your business users need ad-hoc pivot analysis against a controlled, governed semantic model without needing Power BI Desktop.
The connection string in Excel's data source would look like:
Provider=MSOLAP.8;Data Source=powerbi://api.powerbi.com/v1.0/myorg/SalesAnalytics;Initial Catalog=SalesModel;Integrated Security=ClaimsToken;
MSOLAP.8 is the OLE DB provider for Analysis Services and is installed with most recent versions of Excel's Power Pivot add-in or Office Data Connectivity Components.
Querying Semantic Models with DAX and MDX
DAX Query Syntax for XMLA
When you query a Power BI semantic model through an XMLA endpoint using DAX, you're writing DAX in its query form — which is subtly different from the measure definition form you use in Power BI Desktop.
Every XMLA DAX query must return a table, and the outermost function must be EVALUATE. Here are progressively more complex patterns:
Basic table query:
EVALUATE
'Sales'
Filtered summary with measure reference:
EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Product'[Category],
'Product'[Subcategory],
"Revenue", [Total Revenue],
"Margin %", [Gross Margin Pct]
),
'Date'[FiscalYear] = 2024
)
ORDER BY [Revenue] DESC
Using DEFINE to create session-scoped measures:
DEFINE
MEASURE 'Sales'[YOY Growth] =
DIVIDE(
[Total Revenue] - CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date])),
CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
)
EVALUATE
SUMMARIZECOLUMNS(
'Date'[FiscalYear],
'Date'[Quarter],
"Revenue", [Total Revenue],
"YOY Growth", [YOY Growth]
)
ORDER BY 'Date'[FiscalYear], 'Date'[Quarter]
The DEFINE block lets you create temporary measures that exist only for the duration of the query session — this is a powerful pattern for exploratory analysis or testing new measure logic before committing it to the model.
MDX Queries
MDX (Multidimensional Expressions) is the older query language that predates DAX and targets the cube-style mental model of Analysis Services. Power BI's tabular engine supports MDX through an automatic translation layer, though the support is not complete — some MDX functions behave unexpectedly because the underlying model is tabular, not multidimensional.
MDX is primarily relevant when you're connecting tools that speak MDX by default (older Excel versions, certain third-party BI tools) or when you're migrating from a legacy SSAS Multidimensional solution.
A basic MDX query against a Power BI semantic model looks like:
SELECT
{[Measures].[Total Revenue], [Measures].[Units Sold]} ON COLUMNS,
{[Date].[FiscalYear].[FiscalYear].Members} ON ROWS
FROM [SalesModel]
WHERE ([Product].[Category].&[Electronics])
Tip: Prefer DAX over MDX for all new development against tabular models. DAX is designed for tabular architecture and will consistently outperform and out-behave MDX on Power BI semantic models. Use MDX only when forced to by legacy tool compatibility.
This is where XMLA connectivity pays for itself in performance engineering. When you run a query in DAX Studio with Server Timings enabled (click the Server Timings button in the toolbar before running), you get a breakdown of:
- Total Duration — wall clock time
- Formula Engine (FE) Duration — time spent in the DAX calculation engine
- Storage Engine (SE) Duration — time spent scanning and aggregating data in VertiPaq
- SE CPU — CPU time in the storage engine
- SE Queries — the number of internal storage engine queries generated by the formula engine
A healthy query is storage-engine dominated (high SE time, low FE time) and generates few SE queries. If you see high FE time and many SE queries, the DAX formula is making the formula engine do iterative work — often indicating a measure that could be rewritten to push more work to the storage engine.
For example, a measure written as:
Revenue YTD Slow =
SUMX(
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date]) && YEAR('Date'[Date]) = YEAR(MAX('Date'[Date]))
),
[Total Revenue]
)
...will generate many FE iterations. The equivalent using time intelligence:
Revenue YTD Fast = TOTALYTD([Total Revenue], 'Date'[Date])
...pushes the work to the storage engine and runs substantially faster. Without an XMLA connection and DAX Studio's Server Timings, you'd never be able to see this distinction.
Model Management with TMSL Scripts
TMSL (Tabular Model Scripting Language) is a JSON-based scripting language for managing Analysis Services databases. It's the primary tool for everything that's "management" rather than "query" — refresh operations, schema changes, partition management, and deployment scripting.
Understanding the TMSL Command Structure
Every TMSL script follows this basic envelope:
{
"command": {
"object": { },
"properties": { }
}
}
The outer object specifies what kind of command you're running. The major commands are:
- refresh — processes (refreshes) the model or specific objects
- createOrReplace — creates or replaces a database, table, or other object
- alter — modifies properties of an existing object
- delete — removes an object
- backup/restore — backup and restore operations (some limitations in Power BI)
Selective Partition Refresh
This is one of the most practically valuable TMSL operations. In a large data warehouse integration, you almost never want to refresh an entire table when only new data needs to be loaded. The TMSL refresh command lets you target specific partitions:
{
"refresh": {
"type": "full",
"objects": [
{
"database": "SalesModel",
"table": "FactSales",
"partition": "FactSales_2024_Q4"
}
]
}
}
The type field controls the refresh mode:
- full — drop all data and reload from source
- dataOnly — reload data without recalculating calculated columns (faster)
- calculate — recalculate calculated columns and row-level security without reloading data
- clearValues — clear all data without reloading (essentially empties the partition)
- defragment — reorganize internal storage for better compression
- automatic — let the engine decide based on object state
For an enterprise incremental refresh pattern where you want to manage partitions yourself (bypassing Power BI's built-in incremental refresh), the workflow looks like this:
- Define partitions upfront using TMSL createOrReplace
- Refresh only the "hot" partitions (recent data) on a daily schedule
- Periodically defragment the "cold" historical partitions
Here's a complete partition creation script:
{
"createOrReplace": {
"object": {
"database": "SalesModel",
"table": "FactSales",
"partition": "FactSales_2024_Q4"
},
"partition": {
"name": "FactSales_2024_Q4",
"source": {
"type": "m",
"expression": [
"let",
" Source = Sql.Database(\"prod-sql.database.windows.net\", \"SalesDW\"),",
" FactSales = Source{[Schema=\"dbo\",Item=\"FactSales\"]}[Data],",
" Filtered = Table.SelectRows(FactSales, each [SaleDateKey] >= 20241001 and [SaleDateKey] <= 20241231)",
"in",
" Filtered"
]
}
}
}
}
Warning: When you use XMLA to manage partitions on a model that was built with Power BI's built-in incremental refresh, you take over responsibility for the partition lifecycle. Power BI will no longer manage those partitions automatically. This is intentional when you want fine-grained control, but accidental interference with auto-managed partitions is a common source of production incidents.
Scripting Roles and Row-Level Security
Managing RLS roles through the XMLA endpoint is significantly more powerful than the Power BI Desktop interface — particularly when you need to script role deployments across environments or automate role assignment.
Here's a TMSL script to create a role with a table-level DAX filter:
{
"createOrReplace": {
"object": {
"database": "SalesModel",
"role": "RegionalSalesManagers"
},
"role": {
"name": "RegionalSalesManagers",
"modelPermission": "read",
"tablePermissions": [
{
"name": "FactSales",
"filterExpression": "'FactSales'[RegionCode] IN VALUES('UserRegionMapping'[RegionCode])"
},
{
"name": "DimCustomer",
"filterExpression": "'DimCustomer'[RegionCode] IN VALUES('UserRegionMapping'[RegionCode])"
}
],
"members": [
{
"memberName": "sg-regional-sales-managers@company.com",
"identityProvider": "AzureAD"
}
]
}
}
}
You can also script role members separately from role definitions — useful when your role structure is stable but membership changes frequently:
{
"alter": {
"object": {
"database": "SalesModel",
"role": "RegionalSalesManagers"
},
"role": {
"members": [
{
"memberName": "alice.johnson@company.com",
"identityProvider": "AzureAD"
},
{
"memberName": "bob.chen@company.com",
"identityProvider": "AzureAD"
}
]
}
}
}
One of the most useful diagnostic operations is extracting the full TMSL definition of an existing model. This lets you:
- Version-control the model schema
- Compare two model versions
- Understand what Power BI generated from a
.pbix file
- Use the output as the basis for deployment scripts
In SSMS or Tabular Editor, you can right-click a database and select Script > Script Database as > CREATE OR REPLACE To > New Query Window to get the full TMSL representation. Programmatically, you can use the Analysis Services Management Object (AMO) library or the TOM (Tabular Object Model) in .NET.
The output is a large JSON document representing every object in the model — tables, columns, measures, partitions, relationships, hierarchies, roles, and translation layers. A model with 30 tables and 200 measures will produce a TMSL document that's 15,000-40,000 lines of JSON.
Enterprise Model Management: CI/CD Patterns
The Problem with .pbix Files in Source Control
The default Power BI development workflow — editing a .pbix file in Power BI Desktop and publishing it — is fundamentally hostile to professional software development practices. A .pbix file is a binary format that can't be meaningfully diff'd, merged, or code-reviewed. Committing .pbix files to Git produces version history that's useless because you can't see what changed between versions.
The XMLA endpoint, combined with the newer .pbip (Power BI Project) format and Tabular Editor's deployment capabilities, enables a genuine code-based workflow.
The Model-as-Code Approach
The cleanest pattern for enterprise model management is to store the model definition as source-controlled JSON (the TMSL representation) and deploy it through a pipeline. Here's the architecture:
Development: Developers edit the model using Tabular Editor 2/3 connected to a development workspace via XMLA. Changes are saved locally as JSON files in the Tabular Model BIM format.
Source Control: The BIM file (or the .pbip format's JSON files) is committed to Git. Pull requests trigger automated validation — checking for broken measures, missing relationships, and model-level best practice violations using Tabular Editor's BPA (Best Practice Analyzer).
Test Deployment: A CI pipeline deploys the model to a test workspace using Analysis Services Deployment Utility or the Microsoft.AnalysisServices.Deployment command-line tool.
Production Deployment: After test validation, a CD pipeline deploys to the production workspace.
Using Analysis Services Deployment Utility
The Microsoft.AnalysisServices.Deployment.exe tool (available as part of SQL Server tools or as a standalone download) accepts a .asdatabase file (which is just a TMSL JSON rename) and deploys it to an XMLA endpoint:
Microsoft.AnalysisServices.Deployment.exe `
".\SalesModel.asdatabase" `
/s:"deployment_settings.deploymentoptions" `
/s:"deployment_settings.deploymenttargets"
The .deploymenttargets file specifies the XMLA endpoint:
<?xml version="1.0" encoding="utf-8"?>
<DeploymentTarget>
<Database>SalesModel</Database>
<Server>powerbi://api.powerbi.com/v1.0/myorg/ProductionWorkspace</Server>
<ConnectionString>Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/ProductionWorkspace;Initial Catalog=SalesModel;User ID=app:ClientId@TenantId;Password=ClientSecret</ConnectionString>
</DeploymentTarget>
The User ID and Password fields use the service principal format: app:{clientId}@{tenantId} with the client secret as the password.
Service Principal Authentication in Pipelines
For automated pipelines, service principal authentication is the correct approach. In Azure DevOps or GitHub Actions, store the service principal credentials as pipeline secrets and pass them through environment variables:
# GitHub Actions workflow snippet
- name: Deploy Power BI Semantic Model
env:
XMLA_SERVER: powerbi://api.powerbi.com/v1.0/myorg/ProductionWorkspace
SP_CLIENT_ID: ${{ secrets.PBI_SP_CLIENT_ID }}
SP_CLIENT_SECRET: ${{ secrets.PBI_SP_CLIENT_SECRET }}
SP_TENANT_ID: ${{ secrets.PBI_SP_TENANT_ID }}
run: |
# Using the tabular-editor CLI
TabularEditor.exe "SalesModel.bim" \
-S "$XMLA_SERVER" \
-D "${{ env.SP_CLIENT_ID }}@${{ env.SP_TENANT_ID }}" \
"${{ env.SP_CLIENT_SECRET }}" \
"SalesModel" \
-O -C -P -R -M -E -V
The -O -C -P -R -M -E -V flags for Tabular Editor's command-line interface control overwrite behavior, schema reconciliation, partition handling, role management, metadata, and verbosity.
Tip: Tabular Editor 2's command-line interface (TabularEditor.exe) is free, open source, and extremely well-suited for CI/CD integration. The -S flag for server, -D for database, and the authentication format for service principals are well-documented in the Tabular Editor GitHub wiki.
Governance Considerations at Scale
When you're managing 10+ semantic models across multiple workspaces with multiple teams using XMLA endpoints, you need governance controls beyond just "set the endpoint to read-only in production":
Workspace-per-environment pattern: Maintain separate workspaces for DEV, TEST, and PROD on separate capacities. DEV and TEST get read-write XMLA. PROD gets read-only XMLA with changes only arriving through the deployment pipeline.
Service principal per pipeline: Don't share a single service principal across all pipelines. Create one per semantic model or per team. This provides audit trail granularity and limits blast radius when a credential is compromised.
XMLA endpoint activity monitoring: The Power BI admin portal activity log captures XMLA connections. Export these logs to Azure Monitor or a Log Analytics workspace to track who's connecting from where, what operations they're running, and to detect anomalous patterns.
Model-level permissions through roles: Even with workspace contributor access, you can use Analysis Services roles with empty membership to create an additional authorization layer for specific model operations.
Advanced Patterns and Edge Cases
Connecting to Import vs. DirectQuery Models
The XMLA endpoint connects to the semantic model's tabular engine, which means its behavior differs slightly depending on the storage mode:
Import mode: The engine has full data in-memory. DAX queries run against cached data. TMSL refresh commands load data from the source. This is the richest XMLA experience — all operations work.
DirectQuery mode: The engine passes queries through to the underlying source. DAX queries executed through XMLA go through the DirectQuery translation layer. Performance depends on the source system. TMSL "refresh" operations are essentially no-ops for the data itself (there's no data to refresh — it's fetched live), though schema refresh and calculated table refresh still apply.
Composite mode: Partial import, partial DirectQuery. XMLA operations work on the import portions. You can refresh import partitions while DirectQuery tables remain live-connected. This is increasingly the architecture of choice for large enterprise models.
Power BI Desktop has had a setting called "Store datasets using enhanced metadata format" for several years, and it's been default-on since Power BI Desktop July 2021. If you're working with older .pbix files that were saved with legacy metadata, some XMLA write operations may fail or produce unexpected results — particularly around partition M expressions.
Enhanced metadata format stores the Power Query (M) expressions for partitions in a way that XMLA can read and modify. Legacy format stores them in a proprietary representation that XMLA cannot modify. You'll get an error like "The partition source type is not supported for this operation" when hitting this limitation.
The fix is to open the .pbix in a current Power BI Desktop, enable enhanced metadata (File > Options > Data Load > Store datasets using enhanced metadata format), and republish. Once the model is in enhanced metadata format, all XMLA partition management operations work as expected.
Handling Calculation Groups Through XMLA
Calculation groups — one of the most powerful tabular modeling features — can only be created and managed through external tools using the XMLA endpoint. The Power BI Desktop GUI does not expose a calculation group editor as of mid-2024. Tabular Editor is the standard tool for this.
A calculation group created through Tabular Editor is deployed to the model via XMLA and then immediately available to all Power BI reports connected to that model. The TMSL representation of a calculation group looks like:
{
"calculationGroups": [
{
"name": "Time Intelligence",
"calculationItems": [
{
"name": "Actual",
"expression": "SELECTEDMEASURE()"
},
{
"name": "YTD",
"expression": "CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))"
},
{
"name": "MTD",
"expression": "CALCULATE(SELECTEDMEASURE(), DATESMTD('Date'[Date]))"
},
{
"name": "PY",
"expression": "CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))"
},
{
"name": "YOY",
"expression": "SELECTEDMEASURE() - CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR('Date'[Date]))"
}
]
}
]
}
This pattern — five calculation items that any measure in the model can use — replaces what would otherwise require twenty-five separate time intelligence measures (five variants for each of five business measures). The calculation group is one of the biggest force multipliers in large-scale Power BI model design, and it's only accessible through XMLA.
Best Practice Analyzer Integration
Tabular Editor's Best Practice Analyzer (BPA) runs a set of configurable rules against the model metadata and flags violations. This is extremely powerful when integrated into a CI/CD pipeline — you can enforce model quality standards automatically.
The standard BPA ruleset (available on the Tabular Editor GitHub) includes rules like:
- Measures without descriptions
- Columns with cardinality above a threshold that aren't hidden
- Relationships using columns that aren't integers (performance impact)
- Measures that reference column values directly rather than through aggregation functions
- Unused columns that add to model size without benefit
Running BPA in a CI pipeline:
$result = TabularEditor.exe "SalesModel.bim" -B "BPARules.json" -V 2>&1
if ($LASTEXITCODE -ne 0) {
Write-Error "BPA violations found. Blocking deployment."
Write-Output $result
exit 1
}
This creates a quality gate that prevents non-compliant models from reaching production — exactly the kind of enforcement you'd apply to application code through linting and static analysis.
Hands-On Exercise
This exercise walks you through a realistic end-to-end scenario: connecting to a Power BI semantic model, diagnosing a performance issue with DAX Studio, making a model change through Tabular Editor, and scripting a selective partition refresh.
Setup: You'll need a Power BI Premium Per User workspace with at least one published semantic model. If you don't have a real model available, publish the sample "Contoso Sales" .pbix file, which is available on Microsoft's documentation site.
Step 1: Connect DAX Studio and Run a Diagnostic Query
- Open DAX Studio and connect to your workspace XMLA endpoint. Select the Contoso Sales model from the database dropdown.
- Enable Server Timings by clicking the Server Timings button in the ribbon.
- Run the following query:
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Calendar Year],
'Product'[Category],
"Total Sales", [Total Sales Amount],
"Total Cost", [Total Cost Amount],
"Gross Profit", [Total Sales Amount] - [Total Cost Amount]
)
ORDER BY 'Date'[Calendar Year], [Total Sales Amount] DESC
- Examine the Server Timings pane. Note the FE duration vs SE duration ratio. Note the number of SE queries.
- Add a
WHERE clause equivalent using CALCULATETABLE wrapping the SUMMARIZECOLUMNS to filter to a single year, and observe how the SE query count changes.
Step 2: Connect Tabular Editor and Inspect Partitions
- Open Tabular Editor 2 (or 3) and connect to the same XMLA endpoint in read-write mode.
- In the model tree, expand the Tables node, then expand a fact table (e.g., Internet Sales).
- Click on Partitions to see the existing partition structure.
- Note the M expression in the partition properties pane — this is the Power Query code that defines how data is loaded for that partition.
Step 3: Script a Selective Refresh Using TMSL in SSMS
- Open SSMS and connect to the XMLA endpoint using Analysis Services connection type.
- Open a new XMLA query window (right-click the database > New Query > XMLA).
- Enter and execute the following TMSL to perform a dataOnly refresh on a single table:
{
"refresh": {
"type": "dataOnly",
"objects": [
{
"database": "Contoso Sales",
"table": "Internet Sales"
}
]
}
}
- Monitor the execution. SSMS will show the command executing. Switch back to the Power BI service and check the dataset's refresh history — you'll see the XMLA-triggered refresh logged there.
- In SSMS, right-click the Contoso Sales database in Object Explorer.
- Select Script Database as > CREATE OR REPLACE To > Clipboard.
- Paste into a text editor. Examine the structure — find the measures, the relationships defined as
relationships arrays, and the partition M expressions.
- This JSON is the complete TMSL representation of the model — a deployable artifact you could use to recreate this model in any XMLA-accessible workspace.
Step 5: Query with Dynamic Measure Definition
Back in DAX Studio, use the DEFINE block to test a new measure without modifying the model:
DEFINE
MEASURE 'Internet Sales'[Revenue per Customer] =
DIVIDE([Total Sales Amount], DISTINCTCOUNT('Customer'[CustomerKey]))
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Calendar Year],
'Product'[Category],
"Revenue per Customer", [Revenue per Customer],
"Total Customers", DISTINCTCOUNT('Customer'[CustomerKey])
)
ORDER BY 'Date'[Calendar Year], 'Product'[Category]
This measure doesn't exist in the model — you've defined it only for this query. Verify the results make sense, then consider whether this measure should be added to the model permanently through Tabular Editor.
Common Mistakes and Troubleshooting
"Cannot connect to server" After Correct Configuration
The most common cause is the workspace name having special characters or mixed casing that doesn't match what's in the XMLA connection string. The workspace name in the XMLA URL must exactly match the workspace's display name as shown in the Power BI service — case sensitive, spaces included. Some tools URL-encode the spaces automatically; others require you to use %20 manually.
Second most common cause: the user doesn't have a Premium Per User license or isn't assigned to a PPU or Premium capacity workspace. The XMLA endpoint URL will return an authentication error that looks like a network error rather than an authorization error.
"Operation not supported for this type of dataset"
This error appears when you try a write operation (TMSL refresh, createOrReplace) on a model that either:
- Doesn't have enhanced metadata format enabled
- Is published from a
.pbix that wasn't built in a sufficiently recent Power BI Desktop version
- Has features that lock out XMLA modifications (rare, but certain sensitivity labels can do this)
Check the Power BI Desktop version used to create the model and verify enhanced metadata format is enabled.
Analysis Services serializes most management operations. If a scheduled refresh is running, your TMSL refresh command will fail with this error rather than queue behind it. You need to implement retry logic in any automated script:
$maxRetries = 3
$retryDelay = 60 # seconds
$attempt = 0
do {
try {
Invoke-ASCmd -Server $xmlaEndpoint -Database $modelName -Query $tmslScript
$success = $true
} catch {
if ($_.Exception.Message -like "*currently processing*") {
$attempt++
Write-Host "Model is processing. Retry $attempt of $maxRetries in $retryDelay seconds."
Start-Sleep -Seconds $retryDelay
} else {
throw
}
}
} while (-not $success -and $attempt -lt $maxRetries)
Calculation Group Measures Showing Blank Values
When you create a calculation group and apply it to a report, you may see blank values for certain measures. The most common cause is a precedence conflict — when a model has multiple calculation groups, the engine needs to know which one takes priority when both could apply. Set the precedence property on each calculation group (higher number = higher priority) through Tabular Editor.
Service Principal Can Connect But Gets "Access Denied" on Operations
The service principal needs both workspace access (Contributor or higher role in the Power BI service) AND the tenant-level setting "Allow service principals to use Power BI APIs" must be enabled for the security group containing the service principal. Many administrators enable the workspace role but forget the tenant-level toggle, resulting in authentication succeeding but operations failing.
Summary and Next Steps
You've moved from "XMLA is a toggle in workspace settings" to a genuine understanding of what the endpoint is, why it exists, and how to use it for real enterprise data engineering work. Let's consolidate the key ideas:
The XMLA endpoint is the Analysis Services engine. Power BI Premium's semantic models are tabular databases hosted on the same engine as Azure Analysis Services and SQL Server Analysis Services. The XMLA endpoint simply opens the door to that engine for external clients.
Read vs. read-write is an architectural decision, not just a setting. Read-write enables powerful management capabilities but requires the same governance rigor you'd apply to database write access. Separate development, test, and production environments with appropriate access controls.
TMSL is your primary tool for model management. Partition refresh, role management, schema changes, and model deployment are all TMSL operations. Understanding the command structure — refresh, createOrReplace, alter, delete — gives you programmatic control over every aspect of the model lifecycle.
DAX Studio and Tabular Editor are not optional extras. They're the professional tools that make Power BI development at scale possible. DAX Studio's Server Timings pane provides visibility into query execution that doesn't exist anywhere inside the Power BI service UI.
CI/CD through XMLA is achievable and worth the setup cost. Storing model definitions as source-controlled JSON and deploying through pipelines using service principal authentication is how enterprise teams eliminate the "who changed the production model?" problem permanently.
Where to Go Next
- Fabric Unified Analytics: Explore how XMLA endpoints integrate with Microsoft Fabric's Direct Lake storage mode, which adds a third semantic layer between import and DirectQuery
- Advanced Partition Strategies: Study how to implement custom incremental refresh using TMSL partition management for models that exceed Power BI's built-in incremental refresh capabilities
- Tabular Editor BPA: Build a custom BPA ruleset tailored to your organization's modeling standards and integrate it into your CI/CD quality gates
- AMO/TOM Programming: Move beyond TMSL scripting into programmatic model management using the Analysis Services Management Objects library in .NET — enabling dynamic model generation and fully automated semantic layer management
- Power BI Premium Metrics App: Learn to monitor capacity utilization and XMLA endpoint query load using the Premium Metrics app to right-size your capacity as XMLA workloads grow
The XMLA endpoint transforms Power BI from a self-service tool into a manageable, governable, enterprise-grade analytical platform. Once you've seen what's possible, going back to managing models through the browser UI feels like writing application code in Notepad.
