Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.