Deploy and manage datasets using the XMLA endpoint

As you start scaling your Power BI journey from a beginner to a rockstar, you will inadvertently stumble upon the need to find ways to manage and handle your data more granularly. XMLA endpoint is one such feature that will expand your capabilities.

But why should you know about it?

Understanding and utilizing the XMLA endpoint can lead to enhanced data management often beyond the capability of the Power BI interface, creating more robust data models, and the ability to integrate Power BI with a variety of other tools and platforms, including advanced analytics in Python, Tabular Editor, SSMS, Azure Data Studio, SQL Server Data Tools, etc.

In this article, I will guide you through the intricacies of deploying and managing datasets using the XMLA endpoint in Power BI. This feature, available in Power BI Premium, Premium Per User and Embedded workspaces, is a game-changer for those looking to exert more control over their Power BI datasets.

Unfortunately...Despite its benefits, the XMLA endpoint is underutilized, primarily due to its perceived technical complexity.

Before we dive deeper, let's first understand where and how this fits in the Power BI universe.

Intro to XMLA

Many of you might already be familiar with XML (eXtensible Markup Language). Unlike HTML, which is also a markup language, XML is primarily used for storing and transporting data. It's crucial to understand that XML itself doesn't perform computational tasks; rather, it facilitates the exchange of information across different systems, such as websites, databases, and applications.

Now, let's delve deeper into XMLA or XML for Analysis which is a specialized form of XML. This protocol extends the capabilities of XML to cater specifically to data analysis needs. It enables efficient communication between client applications and an Analysis Services instance.

You may be thinking, what's an Analysis Service now?

In the context of Power BI, Analysis Services can be thought of as a combination of an OLAP (Online Analytical Processing) server and the VertiPaq analytical engine. This duo works behind the scenes to support what Power BI refers to as the semantic model, (previously known as a dataset).

The process becomes evident when you upload a .pbix file into the Power BI service. Have you noticed when you publish a .pbix file to the Power BI service, it essentially splits into two parts: the semantic model and the report? The semantic model is the powerhouse where all your data, along with its relationships and calculations, resides.

So, why does Power BI split the .pbix file?

The reason lies in how Power BI operates under the hood. The SQL Server Analysis Services (SSAS) process, running underneath Power BI Desktop, as shown below, is the custodian of your report data, actively maintaining it within your system memory. So when we publish to the Power BI service, the dataset will need to be managed by a version of Analysis Services installed on a cloud machine that you don’t see.

Have the lightbulbs started lighting up yet!! It becomes clear how Power BI workspaces are essentially Analysis Service instances and how Power BI semantic models (datasets) are databases within these instances.

Fitting the puzzle pieces together, it should be clear why XMLA endpoints are exclusive to Premium workspaces. In a shared server setup, revealing the XMLA endpoint can lead to inadvertent data breaches, as it could provide access to the client data using the same server. Thus, XMLA endpoints are exclusive to Premium environments, where your company is the sole owner of a server.

Summarizing it, the instance of Analysis Services (in our case the Premium workspace) “exposes” an XMLA endpoint, which can be then used by various client tools, such as Tabular Editor, DAX Studio, SSMS, Azure Data Studio, etc. to “talk” with Analysis Services instance. This enables you to manage the semantic model present in the Power BI service.

Enjoyed reading it this far!

You can subscribe to our newsletter to see the full post and also get future posts delivered to your inbox for free.

Subscribe

Email sent! Check your inbox to complete your signup.

No spam ever. Unsubscribe anytime. Pinky Promise!!

Step 1: Enable and Connect to the XMLA Endpoint

To use the XMLA endpoint with Power BI, you must first enable it in the Power BI service provided the dataset resides in a Premium or Premium Per User (PPU) workspace. This is done via the Admin portal, where you can select the XMLA Endpoint setting to 'Read Write' under Semantic model workload settings. This is essential for connecting to and managing your datasets through the endpoint.

• 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 provides more possibilities like dataset management, governance, advanced semantic modelling, debugging, and monitoring.

Importance and Benefits:
Activating the XMLA endpoint allows for connections to client tools like SQL Server Management Studio (SSMS), which can be used for more sophisticated data management tasks.

Step 2: Connecting to a Premium Workspace

Once the XMLA endpoint is enabled, the next step is to leverage client tools effectively. These tools can vary from SSMS for SQL-based operations to Python for advanced data analytics.

Each workspace assigned to a capacity has a unique connection URL, which is crucial for applications connecting to the workspace. This URL is formatted like powerbi://api.powerbi.com/v1.0/[tenant name]/[workspace name].

You’ll use this URL as the server name when connecting through client tools like SQL Server Management Studio.

Example:
In Python, for example, you can use certain libraries to connect to your Power BI datasets via the XMLA endpoint, allowing for data manipulation and analysis that leverages Python's robust ecosystem.

Step 3: Utilizing Client Tools for Enhanced Dataset Management

I'll open a client tool, in this screenshot DAX Studio, to show you the tip of the iceberg. Since my Premium workspace (which is the Analysis Service) had a single semantic model (database), we connected to the same as shown below.

You can use other tools like SSMS, Tabular Editor, Python and obtain the same results.

Let's recap that the XMLA endpoint simply facilitated access for other tools and services to the underlying Analysis Service model.

You can switch data sources for your Power BI report, or add an explicit data source definition, as described here. Some common uses of the XMLA endpoint in Power BI are:

• Refreshing individual components of a data model.
• Systematically exporting data from the data model.
• Automating the use of the Best Practice Analyzer.

Check out the following video to learn one such use case of refreshing the semantic model using the XMLA endpoint. For more such use cases, check out the Dive Deeper section.

Conclusion:

The XMLA endpoint in Power BI is a powerful tool that, when understood and utilized effectively, can greatly enhance your data management experience. Whether you're a data analyst, business intelligence professional, or just looking to level up, mastering the XMLA endpoint is a journey worth embarking on.

For more detailed insights and guidance, it's beneficial to explore various resources and examples provided by experts in the field. Check out the following websites that help demystify the XMLA endpoint and illustrate its practical applications in real-world scenarios.

THANK YOU FOR READING !!

If you have found this page useful, then please feel free to share it with your network and don't forget to comment.