What is the Power BI XMLA endpoint?
Power BI XMLA endpoint is a communication channel between the client application and the Microsoft Analysis Services engine that manages the Power BI workspaces and semantic models. The engine supports Power BI’s semantic modeling and uses XMLA as the communication protocol. It ensures that the data transmitted over the XMLA protocol is completely encrypted end-to-end.
Power BI XMLA endpoint supports open-platform connectivity from Microsoft and other third-party client applications and tools. It is available for any datasets hosted in Power BI Service. It comes with 2 types of connection i.e. Read-only and Read-write. Using the read-only connectivity, you can enable the data visualization applications and tools to query semantic model model data, metadata, events, and schema. To use the read-write connectivity, you have to configure the Power BI XMLA endpoint setting for both Premium capacity and Premium per user. Using XMLA endpoint, the Power BI Premium service now includes the capabilities of SQL Server Analysis & Azure Analysis Services combined with newer Power BI data modeling capabilities.
Table of contents
Key Takeaways
- The Power BI XMLA endpoint is a channel of communication between the client application and the Microsoft Analysis Services engine that manages the Power BI workspaces and semantic models.
- It comes with two types of connection, i.e., Read-only and Read-write.
- Power BI XMLA endpoint connection string URL is required to connect with the workspaces.
- It supports a range of client applications and tools for connectivity, including Microsoft tools and third-party applications.
- To use the Power BI XMLA endpoint, a Power BI Premium license is required.
- You can set up XMLA endpoint configurations using Power BI Service and other applications.
How to use the Power BI XMLA endpoint?
Power BI XMLA endpoint is supported for data hosted in Premium licensing such as Power BI Premium, Premium Per User, and Power BI Embedded capacities. Hence, it’s a pre-requisite to have a Power BI Premium license to use the XMLA endpoint. To use the Power BI XMLA endpoint, follow the instructions outlined below:
Step 1: Enable XMLA read-write in Power BI Service for your semantic model depending on what Power BI Premium capacity you are using. You can configure the Power BI XMLA endpoint setting as below:
- For Power BI Premium Capacity, you can navigate to Settings – Admin portal – Capacity settings – Power BI Premium – capacity name. Configure the XMLA Endpoint setting to Read Write by expanding the Workloads.
- For Premium Per User capacity, navigate to Settings – Admin portal – Premium Per User in the Power BI Service portal. Expand the Dataset workload settings and choose Read Write from the dropdown for XMLA Endpoint.
Step 2: Navigate to Tenant settings in the Admin portal and enable the Allow XMLA endpoints and Analyse in Excel with on-premises semantic models option for your organization.
Step 3: Connect to a Premium workspace to fetch the workspace connection URL by navigating to
Workspace settings – Premium – Workspace Connection and then selecting Copy.
Workspaces assigned to a capacity have a Power BI XMLA endpoint connection string in a URL format that looks like powerbi://api.powerbi.com/v1.0/[tenant name]/[workspace name].
Note: Depending on what tools you use to connect with XMLA endpoints, you may need to specify the Initial catalog to facilitate the connection of the semantic model (database) to My Workspace in Power BI Service.
Step 4: Use client applications and tools supported by XMLA endpoint connectivity to Power BI Premium semantic models. These tools can be Microsoft Excel, SQL Server applications, Power BI XMLA endpoint Powershell, Tabular Editor, DAX Studio, and ALM toolkit, to name a few.
Step 5: Set up model roles, role membership, row-level security, and object-level security for end users.
Step 6: Set up data source credentials in the semantic model settings page in the Power BI Service.
Step 7: Deploy a tabular model project in Visual Studio (SSDT) to Power BI Premium workspace using the workspace connection URL. If needed, edit the authentication method, user credentials, and any privacy level settings for your semantic model.
Step 8: Use SQL Server Management Studio (SSMS) to connect the My workspace using the server name, authentication, and user credentials. Once the connection is successfully established, you will be able to view the semantic models available in the My workspaces as Databases.
Step 9: Refresh the dataset using SSMS through the XMLA endpoint if required. Keep in mind that Power BI XMLA endpoint refresh doesn’t automatically refresh the tile cache.
Examples
In this section, we will go through some use cases of using Power BI XMLA.
Example #1
In this example, we will understand the semantic model refresh through the XMLA endpoint using the SSMS. You can use SSMS to connect with the semantic model by providing details such as server name, authentication, and credentials. After a successful connection, you can view the semantic models along with the partitions for those tables. You can configure the refresh policies and frequencies to apply incremental data refresh to your dataset in Power BI Service.
Example #2
In this example, we will go through the use case of using the DAX Studio tool to connect with the dataset in Power BI and optimize the query to enhance the query performance. DAX Studio tool can be used to author and execute DAX expressions, trace server events, and apply them to finetune the query plans. You can launch DAX Studio on your machine, choose Power BI or SSAS as your data source type, and Power BI XMLA endpoint connection string URL as your server name. Post successful connection, use Query Editor to author DAX queries and perform query execution breakdowns along with query statistics and diagnosis of queries.
Example #3
Another example of using XMLA endpoint can be using SQL Server Data Tool (SSDT) to connect with Power BI semantic models to perform data modeling, creation of table partitions and model roles, role membership, etc. You can achieve this by creating a new Analysis Services Tabular project and importing a semantic model from the Analysis Server. Use the Power BI XMLA endpoint connection string URL to connect to the database. Once the connection is successfully established, you can perform data modeling, partition model roles, etc., to the database.
Important Things to Note
- Power BI XMLA endpoint is supported only in Power BI Premium license, i.e., Power BI Premium and Power BI Premium Per User (PPU) capacity.
- Tile caches are not auto-refreshed by Power BI XMLA endpoint refresh and require user intervention to perform data refresh.
- It is always recommended that you test connectivity to a workspace on the capacity post enablement of the XMLA endpoint.
- Be aware of the limitations associated with the XMLA endpoint before you recommend using these communication protocol channels.
- XMLA endpoint doesn’t support all the semantic models. You will only see the semantic models that are supported in the XMLA endpoint.
- Depending on the query complexity and volume of the dataset, the performance of the XMLA endpoint may vary.
Frequently Asked Questions (FAQs)
Tools that support connecting to Power BI using the XMLA endpoint include:
If you are using Premium capacity or Premium Per User semantic model workloads, by default they come with a read-only setup for the Power BI XMLA endpoint. With read-only access, you can only perform data querying on your semantic model. If you need to perform write operations, ensure that the XMLA endpoint property is enabled for read-write.
Some of the key advantages of using are highlighted below:
• Integration with a wide range of external and third-party tools such as SQL Server, Power BI XMLA endpoint Powershell, DAX Studio, etc. to support advanced analytics and build capabilities
• Enhanced semantic model management, governance, advanced semantic modeling, debugging, and monitoring
• Allows accessibility and query capabilities on the Power BI datasets using client applications and libraries supporting XMLA endpoint
Yes, there are certain limitations as outlined below:
• XMLA endpoint doesn’t support all the semantic models that include:
• semantic models based on live connection to Azure Analysis Service, SQL Server Analysis Services Model.
• semantic models based on a live connection to the Power BI semantic model in another workspace.
• semantic models in My workspace or with Push data by using REST APIs
• Excel workbook semantic models.
• XMLA endpoint works only in Power BI Premium, Premium Per User (PPU), or Power BI Embedded capacities.
• You need to build permission for a semantic model to have read access using the XMLA endpoint irrespective of whether the semantic model roles exist or not.
You can perform read-only operations using the XMLA endpoint. Editing the semantic model directly is not supported.
• You can’t trigger an email subscription for semantic models that use Power BI XMLA endpoint refresh.
• If you are performing refresh operations using the XMLA endpoint, consider reducing the number of partitions being processed in parallel.
Recommended Articles
This has been a guide to Power BI XMLA Endpoint. Here we explain how to use XMLA endpoint in power bi, with examples, and points to remember. You may learn more from the following articles –
Leave a Reply