cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bluecar25
Frequent Visitor

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

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.

 

Bluecar25_0-1642175622811.png

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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).

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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).

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors