Processing PBI Premium dataset through XMLA query and polling dataset history after

Anonymous — Fri, 14 Jan 2022 16:03:36 GMT

Hello,

I have a scenario where we need to process individual tables of a data model in Power BI Premium.

We figured this is possible by firing a XMLA query using the following powershell:

Import-Module SqlServer # Executing a DAX-query to retrieve the property we need in a given scenario $PbicomWorkspaceUri = 'powerbi://api.powerbi.com/v1.0/myorg/<workspace>' $PbiDatasetName = "<dataset>" $XmlaQuery = @" { "refresh": { "type": "full", "objects": [ { "database": "<dataset>", "table": "<table>" } ] } } "@ $PbiConnectionString = "Data Source=$PbicomWorkspaceUri;Initial Catalog=$PbiDatasetName" try { Invoke-ASCmd -Query "$XmlaQuery" -Server "$PbicomWorkspaceUri" -ConnectionString $PbiConnectionString Write-Host "XMLA query has been processed succesfully" } catch { Write-Host $_.Exception.Message`n }

Now that Premium is processing the table we need a way to poll whether this has succeeded.

My idea was to poll the power bi rest api on the dataset/{datasetid}/refreshes endpoint. We take the last 10 refreshes and filter on refreshType = ViaXmlaEndpoint, next we take the max startTime in order to find the current refresh.

Login-PowerBI # URL is a relative or absolute URL of the Power BI entity to access. $Responses = Invoke-PowerBIRestMethod -Url 'datasets/<datasetid>/refreshes?$top=10' -Method Get | ConvertFrom-Json # Filter on refreshType = ViaXmlaEndpoint and take response with max startTime $LastResponseStatus = ($Responses.value | Where-Object { $ResponseItem = $_ $ResponseItem.refreshType -eq 'ViaXmlaEndpoint'} | Sort-Object -Property @{Expression = 'startTime'; Descending = $false} | Select-Object -Last 1).status Switch ($LastResponseStatus) { 'Completed' {$body = 'Refresh completed'} 'Unknown' {$body = 'Refresh in progress'} 'Failed' {$body = 'Refresh failed'} 'Disabled' {$body = 'Refresh disabled'} } $body

Now the problem is that when the dataset history is of type 'ViaXmlaEndpoint', the message will appear after processing with status 'Completed'. The Power BI REST API documentation states that during a refresh it should have status 'Unknown'. When doing a manual refresh the history instantly shows status = 'Unknown'.

Is this expected behaviour?

Is there any other way to poll whether processing via XMLA endpoint has succeeded?

Point is that we fire an Azure function for processing a table, and next we fire another Azure function every 30 seconds to poll whether processing has succeeded.

Re: Processing PBI Premium dataset through XMLA query and polling dataset history after

lbendlin — Sat, 15 Jan 2022 20:04:53 GMT

You can run another query to look at the last refresh date for each partition. You would take a snapshot of that date before initiating the refresh, and then check if that date has changed to a more recent date. Would be interesting to see if that date updates when the refresh fails (i would hope not - but then you would also not know when it failed).

topic Processing PBI Premium dataset through XMLA query and polling dataset history after in Developer

Processing PBI Premium dataset through XMLA query and polling dataset history after

Re: Processing PBI Premium dataset through XMLA query and polling dataset history after