*** Be sure to also check out related posts which take this to the next level:
There are many posts on cancelling the refresh of an Analysis Services model. They typically involve using the DISCOVER_COMMANDS DMV to find the SPID of the refresh and then running a 'Cancel SPID' command (shown below) which cancels the refresh.
<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<SPID>123</SPID>
<CancelAssociated>true</CancelAssociated>
</Cancel>However, this does not work when cancelling a Power BI Premium dataset refresh. Running this results in the following error message.
The '<pii>joe@abc.com</pii>' user does not have permission to perform a cancel operation.
The Solution
In order to properly cancel a Power BI Premium dataset refresh in this manner, you must first enable XMLA R/W endpoints for your Premium capacity. Next, instead of using the DISCOVER_COMMANDS, use DISCOVER_SESSIONS.
Locate and copy the Session_ID which has a SESSION_LAST_COMMAND value that starts with '<Batch Transaction=' (as shown below). It will also mention '<Refresh' and will include the Dataset ID.
Next, you need to run a Cancel command on the Session ID in an XMLA window. Make sure that you set the database (Premium dataset) within the Connection Properties window (select 'Options >>' to see this window).
As for the Cancel command, you MUST set the CancelAssociated value to false. Keeping the value as true will result in the same error shown earlier.
<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<SessionID>71DAF1EA-B7E2-434A-BEE5-297261754C78</SessionID>
<CancelAssociated>false</CancelAssociated>
</Cancel>Or, even easier, you can simply skip the CancelAssociated part.
<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<SessionID>71DAF1EA-B7E2-434A-BEE5-297261754C78</SessionID>
</Cancel>Why Session ID?
You may be wondering why this method uses the Session ID instead of the SPID. Actually, they both work - as long as you do not set the CancelAssociated value to true. However, in the future cancelling the SPID will not be possible in Power BI Premium so using the Session ID is future-proof.
What about Cancel Associated?
Setting the CancelAssociated property to 'true' will also cancel associated connections. Power BI Premium does not allow Server Admin access to connections (i.e. DISCOVER_CONNECTIONS). As such, setting the CancelAssociated property to 'true' will cause an error when using Power BI Premium.
Power BI Refresh Retry
If the dataset refresh was requested via the Dataset Refresh API or within the Power BI Service, Power BI will attempt the refresh again upon failure. In fact, it will retry several times. In that case, you would have to go through the process above for each retry attempt in order to make sure that the refresh is actually cancelled. This does not seem very efficient. It is why I do not recommend refreshing your model using the Power BI Service or using the API. In fact, I will be releasing a tool in the near future which will ease processing for Power BI Premium models - where the processing is completely independent of the Power BI Service or API.
Conclusion
This post presents a relatively crude method to cancelling a Power BI Premium refresh. As such, it is only a prelude to a more sophisticated approach - and not just for Power BI Premium but for any variation of tabular. If you've read my blog before, you know I like to automate tasks. This is no exception. Be on the lookout for an upcoming post on a more automated and advanced approach to this subject!