How to Cancel a Power BI Refresh

In Power BI Premium, the XMLA endpoint enables many operations previously unavailable – one being the ability to cancel a dataset refresh. To do so, you’ll need to be a workspace admin and launch a few commands from SQL Server Management Studio (SSMS).

First, use SSMS to connect to the XMLA endpoint (you can learn how to get the XMLA endpoint here). Select “Azure Active Directory – Universal with MFA” authentication and ented your email address as your user name. It is important to include the database when establishing the connection from SSMS. Click the Options button on the connection dialog and enter the Power BI dataset as the “Connect to database”:

After connecting, right click on the database representing the dataset being refreshed and select DAX query:

Enter the following query to get a list of current sessions:

SELECT * FROM $System.Discover_Sessions

You will see at least two sessions – your current one, the refresh and any other queries to the dataset. Look for a row that has the column SESSION_LAST_COMMAND_START_TIME with a value roughly when the refresh should have started and SESSION_LAST_COMMAND with the value “DISCOVER_XML_METADATA”. This should be your refresh session. From this row, get the value for the SESSION_SPID column. Then cancel the session with this statement (but using your session’s SPID instead of the one below):

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
   <SPID>5741</SPID>  
</Cancel>

You’re not done yet. That cancels the session, but Power BI has retry logic and will attempt to refresh the dataset three times before failing. The retries come after 1, 2, and 5-minute delays after each subsequent cancel. So this session discovery and cancel must be completed four times in total.

After your fourth Cancel, Power BI gives up. If you click on the error icon, you should see this: