Cancelling a Dataset Refresh Using Tabular Editor

In my previous post I discussed how to cancel a Power BI Premium dataset refresh. However, the method used was quite crude and too manual. Let's kick it up a notch and make it easier to accomplish this mundane activity.

First, make sure that you have enabled XMLA R/W endpoints. And, be sure to set up a Service Principal by following the steps outlined here.

Steps

Once you have XMLA R/W endpoints enabled and have set up a service principal, simply follow these steps.

1. Download and install Tabular Editor (version 2.12.1 or higher).

2. Download the C# script from GitHub.

3. Open Tabular Editor and go to File->Open->From DB...

4. Connect to your Power BI Premium workspace (as shown below). See this link for more details about obtaining the Premium workspace.

5. When prompted, choose the Premium dataset (model name).

6. Paste the C# script into the Advanced Scripting window of Tabular Editor.

7. Now you can cancel a dataset refresh for this model by clicking the 'Play' button (or 'F5').

The Script

The C# script is quite simple. It first runs the same DMV as outlined in my previous post and filters the query to acquire the Session ID of the refresh command. This uses the Querying Analysis Services capability released in Tabular Editor v2.12.1.

var DMV_Cmd = ExecuteDax("SELECT [SESSION_ID],[SESSION_LAST_COMMAND] FROM $SYSTEM.DISCOVER_SESSIONS").Tables[0];
string databaseID = Model.Database.ID;
string databaseName = Model.Database.Name;
string sID = string .Empty;

for (int r = 0; r < DMV_Cmd.Rows.Count; r++)
{
    string sessionID = DMV_Cmd.Rows[r][0].ToString();
    string cmdText = DMV_Cmd.Rows[r][1].ToString();
    
    // Capture refresh command for the database
    if (cmdText.StartsWith("<Batch Transaction=") && cmdText.Contains("<Refresh xmlns") && cmdText.Contains("<DatabaseID>"+databaseID+"</DatabaseID>"))
    {
        sID = sessionID;
    }      
}

Next, it runs the Cancel Session command using the Session ID and executes it against the Power BI Premium dataset.

Model.Database.TOMDatabase.Server.CancelSession(sID);
Info("Processing for the '"+databaseName+"' model has been cancelled (Session ID: "+sID+").");

Conclusion

Using this method, you no longer have to go through all the manual steps outlined in my previous post. It is all automated by using the C# script. Despite this post being centered around Power BI Premium, it should be known that this process can also be applied to SQL Server Analysis Services (SSAS) & Azure Analysis Services (Azure AS) models. Simply replace Step 4 by connecting to your SSAS or Azure AS model.

Perhaps you think this is as far as we can go to automate the process. However, we can take it to yet another level of sophistication. Stay tuned for my next post where I'll show you exactly that!