top of page
  • MK

Cancelling a Dataset Refresh Using Tabular Editor

Updated: Jan 25, 2022

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!

10 comentarios


Chris Allen
Chris Allen
18 mar 2022

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!

Me gusta
Dan B
Dan B
31 mar 2022
Contestando a

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

Me gusta

Mo Da
Mo Da
17 mar 2022

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!


Me gusta
Contestando a

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.

Me gusta

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?

Me gusta
Contestando a

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

Me gusta

Stephen Maguire
Stephen Maguire
22 abr 2021

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?

Me gusta
Contestando a

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

Me gusta
bottom of page