Getting more insights in the telemetry of your Power BI dataset is always interesting. Especially if you share your Power BI dataset for self-service purposes to build new reports on top of your managed dataset, you might want to know who is actually using in and what queries are they executing against your datasets.

Besides that, there might be a whole lot of other valid use cases where you want to have more insights in currently running sessions on your dataset. An example where I recently ran into, was canceling a Power BI Dataset refresh. As there is no button in the Power BI Service to do this, I had to find a different way to do this.

XMLA to the rescue

XML for Analysis, also known as simply XMLA is a protocol for read and write operations between applications and Power BI datasets or Analysis Services datasets. In Power BI, XMLA endpoints are only there for content that resists in a Power BI Premium Workspace.

Using XMLA endpoints, we can read the Dynamic Management Views (DMVs) on top of a dataset. This allows us to query metadata from the dataset as well as other system telemetry data.

Having that said, let’s have a look at what operation we can execute to get more insights on our currently running queries and what we can do with it. For this approach I will use DAX Studio.

Connect to XMLA endpoint using DAX Studio

In a Power BI workspace, you can easily connect to a XMLA endpoint. You can do this for both Power BI Premium as well as Analysis Services datasets. The XMLA endpoint can be found in the Power BI Premium workspace settings, or in the dataset settings and must be pasted as tabular server connection in DAX Studio.

After clicking on the Connect button, you must authenticate using OAuth credentials. Finally, it will show you all the tables that are part of the dataset you connected to. In case you used the workspace connection, instead of dataset connection, notice the little dropdown menu on the left top where you can switch the dataset.

Active Sessions

Using the Dynamic Management Views, we can see all sessions that are active at this moment. We can simply copy-paste these SQL queries in the DAX Studio window and click run on the right top. Below I explain a few interesting ones to have a look at.

Current sessions

Below query shows us the currently active sessions. This can either be an end user viewing the report, scheduled refreshes, DMV queries on anything else that can be executed over XMLA against the data model. You will always see at least one row as result, which is the query itself executed by the user you have authenticated with.

//shows all running queries on the model
SELECT * 
FROM $System.Discover_Sessions
ORDER BY SESSION_START_TIME DESC

Identify a refresh session
In case a refresh is running, you will see a query where the SESSION_USER_NAME is “NT AUTHORITY\SYSTEM”. This is the System that has triggered the refresh on the back. Running the above DMV query will also show this query to you. Of course, you can further extend this query with a WHERE clause where you filter only on the refresh queries.

//shows all the running queries executed by NT AUTHORITY\SYSTEM. This shows your Power BI refreshes. 
SELECT * 
FROM $System.Discover_Sessions
WHERE SESSION_USER_NAME = 'NT AUTHORITY\SYSTEM'
ORDER BY SESSION_START_TIME DESC

Identify all active users
In case something is wrong with your dataset, you might want to know who is using your dataset at this specific moment. In fact, you want to get a list of users and their session start time. Below query will show you these insights.

//shows an unique list of users, sessions and start time
SELECT DISTINCT SESSION_USER_NAME, SESSION_START_TIME 
FROM $System.Discover_Sessions
ORDER BY SESSION_START_TIME DESC

Cancel a refresh

Now we got to know some basic stuff and be able to see the currently running sessions, you might also be interested to know how you can cancel a refresh.

It all starts with identifying the SESSION_SPID of the refresh. This is one of the returned columns in the earlier ran query. After identifying the SESSION_SPID, you can run the following code snippet to cancel the refresh. Simply refresh the below SESSION_SPID (440142) with your own.

//cancels the active session with the specified SPID
<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
   <SPID>440142</SPID>  
</Cancel>

After executing the above code snippet, DAX Studio will throw an error. Though, If you directly re-run the Query that shows you the active sessions (first one above) you will notice that it did actually cancel the refresh but it simply cannot find the represented session any longer.

Please know that Power BI has an automated retry mechanism that re-triggers the refresh three times. You might need to repeat the above step a few times before the entire refresh execution is canceled. In the end, it’s dogged that does it and Power BI will give-up.

For this specific scenario, I must do a shout out to Richard Tkachuk! Richard describes in his blog the same approach taken from SSMS instead of DAX Studio.

Wrap up

Getting insights in the currently running sessions is very valuable. Especially in the unlikely situation that something went wrong with your dataset, you might want to know which users where impacted by this issue. The approach described above with Dynamic Management Views will help you to get these insights. On top of that, being able to cancel sessions can be very powerful, for example to cancel a refresh but in fact you can cancel any sessions in the list.

From a different perspective, it is very unfortunate that this requires XMLA endpoints, Due to that, the applicability of this approach is limited to Power BI Premium only.

Approaches describe are not for everyone, but rather for specialists and admins. Using the XMLA endpoints is a technical approach that requires you to understand how the metadata structure of Analysis Services and Power BI works.

Links

• Documentation XMLA endpoints for Power BI
• Documentation Dynamic Management Views (DMVs)
• Website DAX Studio
• Blog by Richard Tkachuk XMLA and Datasets