The news about XMLA endpoint connectivity to Power BI datasets is now all around the internet after the public preview announcement of that last week. However, I realized that the XMLA endpoint term is still too technical for many Power BI report developers. Many people come to me asking what exactly is XMLA endpoint, and what is the benefits of it? what it can do? or most importantly; as a report developer, why should I care? Well, I’m going to answer all of these questions in this article. Let’s dig in. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Behind the Scene of a Power BI Dataset
To understand what an XMLA endpoint is and what it does for you, first you need to understand the backstage of a Power BI dataset. By backstage I mean what is behind the beautiful Power BI report that you see, Or let’s say, what are things that you don’t see!
A Power BI report is a visualization element connected to an in-memory dataset behind the scene (I am talking about the most common method of using Power BI; Import Data). the in-memory dataset behind the scene, has all the data loaded into the memory, with all calculations, relationships and the logic to connect to the data source. When you open a *.PBIX file, behind the scene, there are two elements; a report (visualization part), and a dataset (data model). This separation is visible in the Power BI Desktop app resource details in the Task Manager;
As you can see in the above screenshot, there is a Microsoft SQL Server Analysis Services task running under the Power BI Desktop list. A Power BI report stores its data into the memory which is managed by the Microsoft SQL Server Analysis Services (Let’s call it as its shorter familiar name SSAS) engine. Even if you run Power BI Desktop on a machine that doesn’t have SSAS installed, you will see this service, because Power BI Desktop automatically installs a version of SSAS with it.
A Power BI dataset is hosted through a SQL Server Analysis Services Engine.
When you publish your *.pbix file to the website, you will also see this separation of the data model (dataset), and the visualization (report);
When you host your report (or publish it) in the Power BI service, the dataset will be managed by a version of SSAS installed on a cloud machine that you don’t see it.
SSAS is More Than What You See
So now that you know a Power BI dataset is an SSAS model behind the scene, the next question is, what is the point? Or what is the benefit of it for me? To understand that, let’s see what SSAS is actually.
SSAS is a modeling engine in Microsoft SQL Server, and it is now more than 20 years old mature technology. SSAS is a server-side modeling technology. It means the model will be hosted in a server, and there are client tools available to work with it. Through many years; the two most common client tools to work with SSAS were and still are: SQL Server Data Tools (SSDT), and SQL Server Management Studio (SSMS).
These tools are not only to build the model in SSAS, but also to manage it. And when it comes to management, It means monitoring, controlling, backup and restore of the model, and many other features. Here is a view of an SSAS database (model) from SSMS.
SSAS models can be monitored using a lot of queries and command called Dynamic Management Views (DMV). For example, the command below shows all the users querying or working with this SSAS model;
SQL Server Analysis Services is a server-side technology, that can give you a lot of details about the model, and can be monitored through client tools using many scripts and commands such as Dynamic Management Views.
Can I Access the SSAS Model of My Power BI Dataset?
Well, you learned so far that your Power BI dataset is an SSAS model behind the scene. And you learned that you can control and monitor SSAS models using some client tools. However, when the Power BI dataset is hosted in the Power BI service, how you can connect to that SSAS model? how you can control, manage, or monitor it? Or even more importantly; Why it is import to do it?
If you can connect to the SSAS model of your Power BI dataset, you can connect to the data model directly. You can see how many users are using it. You can see what processes take longer and what slower. You can leverage all those monitoring features to build a better model moving forward.
So far, there was no way to connect to the Power BI dataset hosted in the service except two methods: Power BI Desktop (using Get Data from Power BI dataset), and Excel (using Power BI Publisher for Excel, or Analyze in Excel).
Both of these tools are reporting tools. You can (somehow) run monitoring queries from these tools too, but these are not built for that purpose. If you can access it through other client tools, then you would have better control. The good news is that; now you can! using XMLA endpoint.
Now is a good time to explain what XMLA endpoint is. XMLA endpoint creates a connectivity channel for other tools and services (which can be third party tools too) to the SSAS model. XMLA endpoint is available for SSAS models hosted through SQL Server and been used for a long time already. That is why you can use a tool such as SSMS and connect to the local SSAS engine. Without an XMLA endpoint, the access to the SSAS model will be very limited and most probably unsupported.
XMLA endpoint creates a connectivity channel for other tools and services (which can be third party tools too) to the SSAS model.
The good news is that; XMLA endpoint is now available for Power BI datasets. Well, it means now you can use ANY client tools that support XMLA connectivity to connect to Power BI datasets. When I say ANY, I mean it, In addition to SSMS or SSDT or Microsoft SQL Server client tools, you can use third-party tools, such as DAX Studio, and Power BI Helper (coming very soon), and also tools such as Tableau! Yes, you heard it right; You can use Tableau to connect to a Power BI dataset hosted in the Power BI service, and then have your visualization in Tableau. (Although probably you won’t have a superb visualization like what Power BI has, because you are using Tableau, but you can do it 😉 anyways).
This diagram from Microsoft says it all actually, all of these tools now can be used to connect to a Power BI dataset hosted in the service;
In other words; Power BI dataset is not now just for Power BI tools, it can be the source for any other tools that have the XMLA connectivity support; Tableau, SSMS, Power BI Helper, and etc.
How does XMLA Connection work?
At this stage, the XMLA connectivity is just announced and has some limitations, however, through the time, it will be enhanced more and more. Right now, only read-only XMLA connection can be made to workspaces assigned to a premium capacity in Power BI service.
XMLA endpoint URL
If you go to a premium capacity allocated workspace, under the Premium Capacity, you will see the XMLA endpoint connection URL;
The format is:
workspace name can have spaces and is case sensitive. Example:
powerbi://api.powerbi.com/v1.0/myorg/Reza SAMPLE workspace
Admin Control under Capacity Configuration
Your capacity admin should enable you to use the XMLA endpoint. This is possible through Admin Portal -> Capacity Setting -> selecting the capacity -> Workloads
If you want to use this feature, XMLA Endpoint should be set to 1.
And you should have a client tool for this type of connection. While you are waiting for the next version of Power BI Helper (coming very soon), you can use a tool such as SSMS. However, you would need SSMS 18.0 RC1 or above, which can be download here. And client tools can have these libraries installed to be able to connect.
Here is a sample of connection created using XMLA endpoint through SSMS;
And an example of querying to see how many users are using this dataset now using DMVs.
XMLA endpoint is most probably one of the major game changer features added to Power BI in the last few months. Using XMLA endpoints you can use client tools to control, manage, and monitor Power BI datasets in the service. Also, a Power BI dataset now can be used as the data model for other visualization tools such as Tableau. Power BI Helper will give you the functionality of connecting through XMLA endpoint VERY soon. XMLA endpoint at this point of time is read-only, so it won’t give you the ability to write changes back to the model, but the read/write is in the plan and hopefully comes soon. When that becomes an available backup, restore and many other features such as migrating from live connection to import data or etc can be achievable options. What is your feeling about XMLA endpoint? Share your thoughts in the comments below.