The most exciting (at least for me) feature in the new Enhanced Refresh API (blog announcement | docs) is the ability to cancel a dataset refresh that’s currently in progress. Up until now, as this blog post by my colleague Michael Kovalsky shows, this has been quite difficult to do: not only do you need to use the XMLA Endpoint but you also need to take into account that in many cases the Power BI Service will automatically restart the refresh even after you’ve cancelled it. Now, though, if (and only if) you start the refresh using the Enhanced Refresh API you can also cancel it via the Enhanced Refresh API too. This is important because I’ve seen a few cases where rogue refreshes have consumed a lot of CPU on a Premium capacity and caused throttling, even after all CPU smoothing has taken place, and Power BI admins have struggled to cancel the refreshes.
This and all the other great functionality the new API includes (the ability to refresh individual tables or partitions! control over parallelism!) means that it can handle many of the advanced scenarios that, in the past, you’d have had to write some complex TMSL commands for; in my opinion anyone working on an enterprise-level dataset in Power BI Premium should be using it for their refreshes.
But Chris, I hear you say, I’m a data person and find working with APIs confusing and difficult! Yeah, me too – which is why, when I saw this tweet by Stephen Maguire about .NET interactive notebook for Visual Studio Code he’s built for the Enhanced Refresh API, I was interested:
https://github.com/samaguire/PowerBINotebooks
It’s a really great set of examples for learning how to use the Enhanced Refresh API through PowerShell and the notebook format makes it a lot more user-friendly than just another bunch of scripts. I highly recommend that you check it out.
Thanks Chris for the article.
I have been using the advanced APIs quite extensively lately and they do are practical and very helpful. However I believe they should have some changes…
If you pass a incorrect body to the API while performing a refresh, it somehow triggers a full refresh of the same. This should be fixed by Microsoft since its an unexpected behavior. The API should be able to identify the validity of the body parameters and throw an exception in case they are wrong as for example wrong partition or table names…
Yeah I just noticed this too when playing with the notebook for the first time 🙂
It appears to trigger a sychronous refresh as well, which can’t be cancelled.
Hi Luis and Josh, thanks for trying out our feature.
I’m one of the engineer working on this project.
Would this sentence in document helpful?
Looks like we either need to improve document or fix a bug.
Could you help provide an example?
For wrong partition or table name, to my knowledge, I have added code to check before. Maybe I miss something.
“To perform an enhanced refresh operation, you must specify one or more parameters in the Request Body.”
https://docs.microsoft.com/en-us/power-bi/connect-data/asynchronous-refresh
Hi Po-wei,
Most definitely it seems to either be a bug or a unexpected behavior. Its very simple to repro, just pass a wrong body payload with incorrect partition names etc and you will observe the problem.
Let us know if this gets fixed 🙂
Hi, could you let me know what body is being sent?
I’m one of the engineer working on this project.
Would like to fix this in product or document.
Thanks.
Hi Luis, thanks for using this feature. I’m one of the engineer working on this project.
I’m intended to fix this problem.
Could you help me with an example? As in this doc, we try this sentence to let user understand but looks like it’s still pretty confusing.
“To perform an enhanced refresh operation, you must specify one or more parameters in the Request Body.”
https://docs.microsoft.com/en-us/power-bi/connect-data/asynchronous-refresh
Hey Po-wei
It might have been an issue with the prior version of the powershell module
I just updated the Powershell module and can confirm this has worked
$type = “full”
$commitMode = “transactional”
$maxParallelism = $null
$retryCount = $null
$applyRefreshPolicy = “false”
$effectiveDate = $null
$objects = $null
Invoke-PowerBIRestMethod -url “$($groupUrlPart)datasets/$datasetId/refreshes” -Method Post -Body $bodyJson -ContentType “application/json”
$requestId = (Invoke-PowerBIRestMethod -url “$($groupUrlPart)datasets/$datasetId/refreshes?`$top=1” -Method Get | ConvertFrom-Json).value.requestId
Write-Host “Request ID : $requestId”
# A Cancel also has worked thus :-
Invoke-PowerBIRestMethod -url “$($groupUrlPart)datasets/$datasetId/refreshes/” -Method Delete
# And the output from the refreshes get endpoint shows cancelled as expected
$body = Invoke-PowerBIRestMethod -url “$($groupUrlPart)datasets/$datasetId/refreshes/$requestId” -Method Get | ConvertFrom-Json
$body | Select-Object * -ExcludeProperty “objects”
$body.objects | Where-Object -Property “status” -NE “Completed”
Output exceeds the size limit. Open the full output data in a text editor
startTime : 21/06/2022 11:13:41 AM
endTime : 21/06/2022 11:15:48 AM
type : Full
commitMode : Transactional
status : Cancelled
extendedStatus : Cancelled
Sorry I can’t reproduce it now 🙂
Hi Luis, we already have check and refresh will go into failed state quickly. But the check is not enforced when submission, by current design. If you use get detail, you will see refresh fail quickly with incorrect partition. Do you mean that you want to change it to check at submission?
It’s a step in the right direction. So far we are unable to actually cancel any ad-hoc requests (getting 405 errors – not authorized – despite the right scope and permissions). What we actually need is the ability to cancel any type of refresh. And yes, there’s always the option to restart the capacity but that seems to be too blunt of a tool.