All articles
Automation Tools Power BI Premium

Cancelling a Dataset Refresh Using Tabular Editor

Learn how to programmatically cancel 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!

Comments 10

Comments with links are reviewed before they appear.

  1. Stephen Maguire

    I love this SOOO much! But have get the 'user does not have permission' error when trying to do this against PBI Premium. I've even tried explicitly defining the cancelAssociated as false in the CancelSession method. Odd thing is it works when issuing the xml via SSMS as per your prior blog post. Any ideas?

    1. Michael Kovalsky Author

      Glad to hear, Stephen! Make sure you have the Service Principal set up properly (with Tenant access). Since you’re getting a permission error it leads me to believe that is the issue.

    2. Pablo Mancini

      Awesome! But how can I configure the main service? (With tenant access). The link doesn't work.

    3. Michael Kovalsky Author

      I’ve updated the link for setting up the service principal.

  2. analyticsBI

    Hi - really appreciate you putting this together. I too am getting the permission error (same as Stephen) - the link to the Service Principal set up is not working for me either. Could you update it again?

    1. Michael Kovalsky Author

      Hi, this has been updated. Many links to Tabular Editor’s documentation have changed since Tabular Editor 3 launched, causing old links to break.

  3. Mo Da

    Great Post and thanks a lot - it worked out great. I would have a question: Which other ways do you have to trigger a refresh besides the PowerBI Service or the PowerBI RestAPI ? I just realized, that the PBI Service does retry 5 times.

    If I would trigger the refresh via TabularEditor or SSMS I could cancel that in the tool itself - same with the SQLcmds ?

    Or am I mixing up something ?

    Thanks a lot!

    1. Michael Kovalsky Author

      See my post about triggering a refresh from Tabular Editor (can also be triggered using the r command line, so can be done from Azure DevOps etc). If you trigger a refresh in SSMS or tabular editor you can cancel it within the same tool. However, if you trigger the refresh programmatically, then this series of posts shows (imo) the best way to cancel a refresh.

  4. Chris Allen

    I'm also getting the 'user does not have permission' error when trying to do this against PPU. I've confirmed XMLA read/write is enabled and my service principal has Tenant.read/write. Once item I did notice when looking at script is that Intellisense can't find the CancelSession function (Model.Database.TOMDatabase.Server.CancelSession(sID). I'm running tabular editor version 2.16.2. Is there something else I'm missing?

    Thanks!

    1. Dan B

      Same here. And I have every admin right possible in M365.

Search articles