Dataset connectivity with the XMLA endpoint
Power BI Premium, Premium Per User, and Power BI Embedded workspaces use an XMLA endpoint to support open-platform connectivity from Microsoft and third-party client applications and tools.
Workspaces use the XML for Analysis (XMLA) protocol for communications between client applications and the engine that manages your Power BI workspaces and datasets. These communications are through what are commonly called XMLA endpoints. XMLA is the communication protocol used by the Microsoft Analysis Services engine, which runs Power BI's semantic modeling, governance, lifecycle, and data management. Data sent over the XMLA protocol is fully encrypted.
By default, read-only connectivity using the endpoint is enabled for the Datasets workload in a capacity. With read-only, data visualization applications and tools can query dataset model data, metadata, events, and schema.
Read-write operations using the endpoint can be enabled. Read-write provides more dataset management, governance, advanced semantic modeling, debugging, and monitoring. When enabled, datasets have more parity with Azure Analysis Services and SQL Server Analysis Services enterprise grade tabular modeling tools and processes.
Using the XMLA endpoint is subject to:
Single-user application - The application uses a single user account or app identity to access a Power BI dataset through the XMLA endpoint. Examples of single-user applications include developer tools, admin scripts, and automated processes. These applications can perform tasks such as data modeling and administrative tasks that alter the metadata of a dataset, backup or restore operation, or trigger a data refresh. The user account or app identity that the client application uses to access a dataset must have a valid Premium Per User (PPU) license unless the dataset resides on a Premium capacity.
Multi-user application - The application provides multiple users with access to a Power BI dataset. For example, a middle-tier application integrating a dataset into a business solution and accessing the dataset on behalf of its business users.
- Premium Per User (PPU) workspaces - The application must require each user to sign in to Power BI. For each user, the application uses an access token to access the datasets. The application can't use a service account or other app identity to perform tasks on behalf of individual users. Each user must have their own Power BI account for opening reports, accessing datasets, and executing queries.
- For Premium workspaces, the application can use either a service account or app identity on behalf of end users without requiring each user to sign in to Power BI.
Client applications and tools
Common applications and tools used with Azure Analysis Services and SQL Server Analysis Services that are now supported by Power BI Premium datasets:
Microsoft Excel – Excel PivotTables are one of the most common tools used to summarize, analyze, explore, and present summary data from Power BI datasets. Read-only is required for query operations. Requires the Click-to-Run version of Office 16.0.13612.10000 or higher.
Visual Studio with Analysis Services projects – Known as SQL Server Data Tools(SSDT). SSDT is an enterprise grade model authoring tool for Analysis Services tabular models. All Visual Studio 2017 and later editions including the free Community edition support Analysis Services projects extensions. Requires extension version 2.9.14 or higher to deploy tabular models to a Premium workspace. The model must be at the 1500 or higher compatibility level to deploy. Requires XMLA read-write on the datasets workload. To learn more, see Tools for Analysis Services.
SQL Server Management Studio (SSMS) - Supports DAX, MDX, and XMLA queries. Perform fine-grain refresh operations and scripting of dataset metadata using the Tabular Model Scripting Language (TMSL). Requires read-only for query operations. Requires read-write for scripting metadata. Requires SSMS version 18.9 or higher. Download SSMS.
SQL Server Profiler – SQL Server Profiler installs with SSMS, it allows tracing and debugging of dataset events. Although officially deprecated for SQL Server, Profiler is still included in SSMS and remains supported for Analysis Services and Power BI. Requires SQL Server Profiler version 18.9 or higher. Users must specify the dataset (initial catalog) when connecting with the XMLA endpoint. To learn more, see SQL Server Profiler for Analysis Services.
Analysis Services Deployment Wizard – Installed with SSMS, this tool provides deployment of Visual Studio authored tabular model projects to Analysis Services and Premium workspaces. It can be run interactively or from the command line for automation. XMLA read-write is required. To learn more, see Analysis Services Deployment Wizard.
PowerShell cmdlets – Use Analysis Services cmdlets to automate dataset management tasks like refresh operations. Requires XMLA read-write. Requires version 21.1.18256 or higher of the SqlServer PowerShell module. Azure Analysis Services cmdlets in the Az.AnalysisServices module aren't supported for Power BI datasets. To learn more, see Analysis Services PowerShell Reference.
Power BI Report Builder - A tool for authoring paginated reports. Create a report definition that specifies the data to retrieve, where to get it, and how to display it. You can preview your report in Report Builder and then publish your report to the Power BI service. Requires XMLA read-only. To learn more, see Power BI Report Builder.
Tabular Editor - An open-source tool for creating, maintaining, and managing tabular models using an intuitive, lightweight editor. A hierarchical view shows all objects in your tabular model. Organizes objects by display folders with support for multi-select property editing and DAX syntax highlighting. Requires XMLA read-only for query operations. Requires read-write for metadata operations. To learn more, see tabulareditor.github.io.
DAX Studio – An open-source tool for DAX authoring, diagnosis, performance tuning, and analysis. Features include object browsing, integrated tracing, query execution breakdowns with detailed statistics, DAX syntax highlighting and formatting. Requires XMLA read-only for query operations. To learn more, see daxstudio.org.
ALM Toolkit - An open-source schema compare tool for Power BI datasets, most often used for application lifecycle management (ALM) scenarios. Perform deployment across environments and retain incremental refresh historical data. Diff and merge metadata files, branches, and repos. Reuse common definitions between datasets. Requires read-only for query operations. Requires read-write for metadata operations. To learn more, see alm-toolkit.com.
Third party - Includes client data visualization applications and tools that can connect to, query, and consume datasets in Premium workspaces. Most tools require the latest versions of MSOLAP client libraries, but some can use ADOMD. Read-only or read-write XMLA endpoint is dependent on the operations.
Client applications and tools don't communicate directly with the XMLA endpoint. Instead, they use client libraries as an abstraction layer. These are the same client libraries that applications use to connect to Azure Analysis Services and SQL Server Analysis Services. Microsoft applications like Excel, SQL Server Management Studio (SSMS), and Analysis Services projects extension for Visual Studio install all three client libraries and update them along with regular application and extension updates. Developers can use the client libraries to build custom applications. In some cases, particularly with third-party applications, if not installed with the application, it might be necessary to install newer versions of the client libraries. Client libraries are updated monthly. To learn more, see Client libraries for connecting to Analysis Services.
Optimize datasets for write operations by enabling large models
When using the XMLA endpoint for dataset management with write operations, it's recommended you enable the dataset for large models. This reduces the overhead of write operations, which can make them considerably faster. For datasets over 1 GB (after compression), the difference can be significant. To learn more, see Large models in Power BI Premium.
Enable XMLA read-write
By default, Premium capacity or Premium Per User dataset workloads have the XMLA endpoint property setting enabled for read-only. This means applications can only query a dataset. For applications to perform write operations, the XMLA Endpoint property must be enabled for read-write.
To enable read-write for a Premium capacity
Select Settings > Admin portal.
In the Admin portal, select Capacity settings > Power BI Premium > capacity name.
Expand Workloads. In the XMLA Endpoint setting, select Read Write. The XMLA Endpoint setting applies to all workspaces and datasets assigned to the capacity.
To enable read-write for Premium Per User
- Select Settings > Admin portal.
- In the Admin portal, select Premium Per User.
- Expand Dataset workload settings. In the XMLA Endpoint setting, select Read Write.
Connecting to a Premium workspace
Workspaces assigned to a capacity have a connection string in URL format. For example:
powerbi://api.powerbi.com/v1.0/[tenant name]/[workspace name].
Applications connecting to the workspace use the URL as if it were an Analysis Services server name. For example:
Users with UPNs in the same tenant (not B2B) can replace the tenant name with
myorg. For example:
B2B users must specify their organization UPN in tenant name. For example:
To determine the primary domain name and ID of a Power BI tenant, sign into the Azure portal, select Azure Active Directory from the main menu, and then note the information on the Azure Active Directory Overview page. For more information, see Find the Microsoft Azure AD tenant ID and primary domain name.
Connecting to a My Workspace by using the XMLA endpoint is currently not supported.
To get the workspace connection URL
In workspace Settings > Premium > Workspace Connection, select Copy.
With some tools, such as SQL Server Profiler, you must specify an Initial Catalog, which is the dataset (database) to connect to in your workspace. In the Connect to Server dialog, select Options > Connection Properties > Connect to database, enter the dataset name.
Duplicate workspace names
Workspaces in Power BI validation prevents the creation or renaming of workspaces with duplicate names. When connecting to a workspace with the same name as another workspace, you might get the following message:
Cannot connect to
powerbi://api.powerbi.com/v1.0/[tenant name]/[workspace name].
To work around, in addition to the workspace name specify the ObjectIDGuid. You can copy the ObjectIDGuid from the workspace objectID in the URL. Append the objectID to the connection URL. For example:
powerbi://api.powerbi.com/v1.0/myorg/Contoso Sales - 9d83d204-82a9-4b36-98f2-a40099093830.
Duplicate dataset name
To connect to a dataset with the same name as another dataset in the same workspace, append the dataset guid to the dataset name. You can get both dataset name and guid when connected to the workspace in SSMS.
Delay in datasets shown
When you connect to a workspace, changes from new, deleted, and renamed datasets can take up to a few minutes to appear.
The following datasets aren't accessible by using the XMLA endpoint. These datasets won't appear under the workspace in SSMS or in other tools:
- Datasets based on a live connection to an Azure Analysis Services or SQL Server Analysis Services model.
- Datasets based on a live connection to a Power BI dataset in another workspace. To learn more, see Intro to datasets across workspaces.
- Datasets with Push data by using the REST API.
- Datasets in My Workspace.
- Excel workbook datasets.
Server name aliases, supported in Azure Analysis Services aren't supported for Premium workspaces.
In addition to the XMLA Endpoint property being enabled read-write by the capacity admin, the tenant-level setting Allow XMLA endpoints and Analyze in Excel with on-premises datasets must be enabled in the admin portal. If you need to generate Analyze in Excel (AIXL) files that connect to the XMLA endpoint, the tenant-level setting Allow live connections should also be enabled. These settings are both enabled by default.
Allow XMLA endpoints and Analyze in Excel with on-premises datasets is an integration setting.
The following table describes the implications of the setting Export data for XMLA and Analyze in Excel (AIXL):
|Setting||Allow XMLA endpoints and Analyze in Excel with on-premises datasets = disabled||Allow XMLA endpoints and Analyze in Excel with on-premises datasets = enabled|
|Allow Live Connections toggle = disabled||XMLA disallowed, Analyze in Excel disallowed, AIXL for on-premises datasets disallowed||XMLA allowed, Analyze in Excel disallowed, AIXL for on-premises datasets allowed|
|Allow Live Connections toggle = enabled||XMLA disallowed, Analyze in Excel allowed, AIXL for on-premises datasets disallowed||XMLA allowed, Analyze in Excel allowed, AIXL for on-premises datasets allowed|
Allow live connections is an export and sharing setting.
Access through the XMLA endpoint will honor security group membership set at the workspace/app level.
Workspace contributors and above have Write dataset permissions, which are effectively the same as Analysis Services database admins. They can deploy new datasets from Visual Studio and execute TMSL scripts in SSMS.
Users with Build dataset permissions are equivalent to Analysis Services database readers. They can connect to and browse datasets for data consumption and visualization. Row-level security (RLS) rules are honored and they cannot see internal dataset metadata.
Operations that require Analysis Services server admin permissions (rather than database admin) in general are not supported.
User impersonation by using the EffectiveUserName connection string property is supported when connecting to Premium workspace datasets. The account specified in EffectiveUserName must be in the tenant's Azure Active Directory and must have both Read and Build permissions for the dataset being connected to. If the account doesn't have both Read and Build permissions, Power BI can't impersonate the user account. The connection will fail, and an error is returned.
You can also perform impersonation by specifying one or more workspace roles in the Roles connection string property. With the Roles property, you can test downgrading role members with Write permissions to Read permissions. The following Role permissions apply depending on the account of the user signed in:
If the user performing impersonation is a workspace admin, which is effectively the same as a server admin in Analysis Services, they do not need to be a member of any of the specified roles.
If the user performing impersonation is not a workspace admin, they must belong to one or more of the specified roles, otherwise a user not found or no permissions type error is returned.
With the XMLA endpoint, roles, role membership, row-level security (RLS), and object-level security (OLS) can be defined for users in the tenant's Azure Active Directory (Azure AD). Model roles in Power BI are used only for RLS and OLS. Use the Power BI security model to control permissions beyond RLS and OLS.
For tabular model projects authored in Visual Studio, roles can be defined by using Role Manager in the model designer. For datasets in Power BI, roles can be defined in Power BI Desktop prior to publishing to the service. Role membership is specified in the Power BI service. SSMS can also be used to create and manage roles. In most cases, role object definitions can be scripted by using TMSL to create or modify the Roles object. TMSL scripts can be executed in SSMS or with the Invoke-ASCmd PowerShell cmdlet.
The following limitations apply when working with roles through the XMLA endpoint:
- The only permission for a role that can be set for datasets is Read permission. Other permissions are granted using the Power BI security model.
- Service Principals do not work with RLS and OLS, and cannot be added as model role members.
- Build permission for a dataset is required for read access through the XMLA endpoint, regardless of the existence of dataset roles.
Setting data source credentials
Metadata specified through the XMLA endpoint can create connections to data sources, but cannot set data source credentials. Instead, credentials can be set in the dataset settings page in the Power BI Service.
Service principals are an Azure Active Directory app registration you create within your tenant to perform unattended resource and service level operations. They're a unique type of user identity with an app name, application ID, tenant ID, and client secret or certificate for a password. Power BI Premium uses the same service principal functionality as Power BI Embedded.
Service principals can be used with the XMLA endpoint to automate dataset management tasks such as provisioning workspaces, deploying models, and dataset refresh with:
- Azure Automation
- Azure Logic Apps
- Custom client applications
To learn more, see Automate Premium workspace and dataset tasks with service principals.
Deploy model projects from Visual Studio (SSDT)
Deploying a tabular model project in Visual Studio to a Premium workspace is much the same as deploying to an Azure or SQL Server Analysis Services server. The only differences are in the Deployment Server property specified for the project, and how data source credentials are specified so processing operations can import data from data sources into the new dataset on the workspace.
To deploy a tabular model project authored in Visual Studio, set the workspace connection URL in the project Deployment Server property. In Visual Studio, in Solution Explorer, right-click the project > Properties. In the Server property, paste the workspace connection URL.
When the Deployment Server property is specified, the project can be deployed.
When deployed the first time, a dataset is created in the workspace by using metadata from the model.bim. As part of the deployment operation, after the dataset is created in the workspace from model metadata, processing to load data into the dataset from data sources will fail.
Processing fails because unlike deploying to an Azure or SQL Server Analysis Server instance, where you are prompted for data source credentials as part of the deployment operation, when deploying to a Premium workspace data source credentials cannot be specified as part of the deployment operation. Instead, after metadata deployment has succeeded and the dataset is created, data source credentials are then specified in the Power BI Service in dataset settings. In the workspace, select Datasets > Settings > Data source credentials > Edit credentials.
When data source credentials are specified, you can then refresh the dataset in the Power BI service, configure schedule refresh, or process (refresh) from SQL Server Management Studio to load data into the dataset.
The deployment Processing Option property specified in the project in Visual Studio is observed. However, if a data source has not had credentials specified in the Power BI service, even if the metadata deployment succeeds, processing will fail. You can set the property to Do Not Process, preventing any attempts to process as part of the deployment. You might want to set the property back to Default because once the data source credentials are specified in the data source settings for the new dataset, processing as part of subsequent deployment operations will then succeed.
Connect with SSMS
Using SSMS to connect to a workspace is just like connecting to an Azure or SQL Server Analysis Services server. The only difference is you specify the workspace URL in server name, and you must use Active Directory - Universal with MFA authentication.
Connect to a workspace by using SSMS
In SQL Server Management Studio, select Connect > Connect to Server.
In Server Type, select Analysis Services. In Server name, enter the workspace URL. In Authentication, select Active Directory - Universal with MFA, and then in User name, enter your organizational user ID.
When connected, the workspace is shown as an Analysis Services server, and datasets in the workspace are shown as databases.
To learn more about using SSMS to script metadata, see:
The XMLA endpoint enables a wide range of scenarios for fine-grain refresh capabilities using SSMS, automation with PowerShell, Azure Automation, and Azure Functions using TOM. For example, you can refresh certain incremental refresh historical partitions without having to reload all historical data.
Unlike configuring refresh in the Power BI service, refresh operations through the XMLA endpoint are not limited to 48 refreshes per day, and the scheduled refresh timeout is not imposed.
Date, time, and status for dataset refresh operations that include a write transaction through the XMLA endpoint are recorded and shown in dataset Refresh history.
Refresh operations performed by the XMLA endpoint don't automatically refresh tile caches. Tile caches are only refreshed when a user accesses the report.
Dynamic Management Views (DMV)
Analysis Services DMVs provide visibility of dataset metadata, lineage, and resource usage. DMVs available for querying in Power BI through the XMLA endpoint are limited to, at most, those that require database-admin permissions. Some DMVs, for example, aren't accessible because they require Analysis Services server-admin permissions.
Power BI Desktop authored datasets
XMLA write operations on datasets authored in Power BI Desktop and published to a Premium workspace require enhanced metadata. To learn more, see Enhanced dataset metadata.
At this time, a write operation on a dataset authored in Power BI Desktop prevents it from being downloaded back as a PBIX file. Be sure to retain your original PBIX file.
data source declaration
When connecting to data sources and querying data, Power BI Desktop uses Power Query M expressions as inline data source declarations. While supported in Premium workspaces, Power Query M inline data source declaration isn't supported by Azure Analysis Services or SQL Server Analysis Services. Instead, Analysis Services data modeling tools like Visual Studio create metadata using structured or provider data source declarations. With the XMLA endpoint, Premium also supports structured and provider data sources, but not as part of Power Query M inline data source declarations in Power BI Desktop models. To learn more, see Understanding providers.
Power BI Desktop in live connect mode
Power BI Desktop can connect to a Power BI Premium dataset using a live connection. Using a live connection, data doesn't need to be replicated locally making it easier for users to consume semantic models. There are two ways users can connect:
Select Power BI datasets, and then select a dataset to create a report. This is the recommended way for users to connect live to datasets. This method provides an improved discover experience showing the endorsement level of datasets. Users don't need to find and keep track of workspace URLs. To find a dataset, users simply type in the dataset name or scroll to find the dataset they're looking for.
Using Get Data > Analysis Services, specify a Power BI Premium workspace name as a URL, select Connect live, and then in Navigator, select a dataset. In this case, Power BI Desktop uses the XMLA endpoint to connect live to the dataset as though it were an Analysis Services data model.
Organizations that have existing reports connected live to Analysis Services data models, and intend to migrate to Premium datasets only have to change the server name URL in Transform data > Data source settings.
When applications connect to a workspace, access through XMLA endpoints is logged in the Power BI audit logs with the following operations:
|Operation friendly name||Operation name|
|Connected to Power BI dataset from an external application||ConnectFromExternalApplication|
|Requested Power BI dataset refresh from an external application||RefreshDatasetFromExternalApplication|
|Created Power BI dataset from an external application||CreateDatasetFromExternalApplication|
|Edited Power BI dataset from an external application||EditDatasetFromExternalApplication|
|Deleted Power BI dataset from an external application||DeleteDatasetFromExternalApplication|
To learn more, see Auditing Power BI.
For more information related to this article, see: