I was testing using XMLA endpoints to refresh tables in PowerShell using the following command:
Invoke-ProcessTable -TableName $TableName -RefreshType Full -Server $Workspace -DatabaseName $DatabaseName -Credential $PSCred -ServicePrincipal
I have a script that I accidentally put in a neverending while loop using asynchronous code to refresh 3 datasets at the same time.
The embedded capacity has been stuck for 2 days constantly refreshing these datasets. Looking at the refresh summary, one dataset has refreshed almost 15,000 times in the past few days. The other two have refreshed over 1,000 times. There is no way to restart the capacity using Gen2. I tried turning the XMLA endpoint off, but when I set it back to Read Write, the datasets keep refreshing. CPU usage is showing as 300% in the Azure Portal.
How can I cancel these XMLA queries?
Update: I've tried using
Select * from $System.Discover_Sessions
And then finding the current refresh sessions and canceling using:
<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<SessionID>session_id</SessionID>
</Cancel>
But this only allows me to cancel one session ID at a time and there are many that are added in real-time.
I also tried moving my workspace to a new embedded resource but refreshes are still happening.
Update 2: I found this article to programmatically cancel all refreshes using Tabular Editor: https://www.elegantbi.com/post/canceldatarefreshte
