Programatic and asynchronus table refresh via XMLA endpoint

[BIanon]

Hello community,

I've been having some difficulties refreshing specific tables via the xmla endpoint using python and was hoping anyone might have  some code examples to share?

Right now I am just trying to get to know the xmla endpoint and my options of working with it so i can decide how to integrate some of this stuff into our custom applications

[v-linyulu-msft · Accepted solution]

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @BIanon 
Could you please let us know if lbendlin's response resolved your issue? If it did, kindly accept it as the solution.
 

Based on your requirements, I found this article that I hope will assist you in achieving automatic XMLA refresh. Please note that most of the content in the article is written in PowerShell, so you may need to convert it to Python as per your needs.

# Parameters for the Runbook. Expected: destination Power BI Workspace, XMLA update script, callback URI from Azure Data Factory
Param (    
    [Parameter(Mandatory=$False,Position=1)]
    [object] $WebhookData
)

$errorActionPreference = "Stop"

$Parameters = (ConvertFrom-Json -InputObject $WebhookData.RequestBody)

$CallBackUri =  $Parameters.callBackUri
$Query =  $Parameters.queryXMLA
$WorkspaceName = $Parameters.workspaceName


# Set Power BI server address
$Server = "powerbi://api.powerbi.com/v1.0/myorg/$WorkspaceName"

# Ensures you do not inherit an AzContext in your runbook
Disable-AzContextAutosave -Scope Process

# Connect to Azure with system-assigned managed identity
$AzureContext = (Connect-AzAccount -Identity).context
    
# Retrieve the Power BI SP credentials from Key Vault
$PBIAppID = Get-AzKeyVaultSecret -VaultName "mp-thevault" -Name "PBI-AppID" -AsPlainText
$PBIPass = Get-AzKeyVaultSecret -VaultName "mp-thevault" -Name "PBI-Pass" 
$TenantID = Get-AzKeyVaultSecret -VaultName "mp-thevault" -Name "TenantID" -AsPlainText

# Create the credential to connect to the Power BI tenant 
$Credential = New-Object System.Management.Automation.PSCredential ($PBIAppID, $PBIPass.SecretValue)

Try {    

    # Execute the refresh command via the XMLA endpoint
    Invoke-ASCmd -Server $Server -TenantId $TenantID -ServicePrincipal -Credential $Credential -Query $Query  
    
    Write-Output "Results:"
    
    $Body = @{
        StatusCode = "200"
    }

    If ($CallBackUri)
    {
        Write-Output "Invoke call back to ADF with status 200.."

        # Return the success state back to the calling Azure Data Factory pipeline
        Invoke-RestMethod -Method Post -Uri $CallBackUri -Body $Body
    }
}

Catch{
    Write-Output "An error occurred:"
    Write-Output $_

    $Body = @{
        StatusCode = "400"
    }
    Write-Output "Invoke call back to ADF with status 400.."
    If ($CallBackUri)
    {
        # Return the failed state back to the calling Azure Data Factory pipeline
        Invoke-RestMethod -Method Post -Uri $CallBackUri -Body $Body
    }
}

It’s important to highlight the following preparatory steps you will need to take:

1.Create a Service Principal: Set up a service principal in Power BI and grant it the appropriate permissions.

2.Create an Automation Account: Ensure that the SqlServer and Az.KeyVault modules are installed, and enable the system-assigned identity.

3.Create an Azure Key Vault: Add the necessary secrets to connect to your Power BI tenant (such as tenant ID, service principal name, and password).

For further details, please refer to the article.

GitHub - mariuspc/pbi-xmla-refresh: Use the PBI XMLA endpoint to automate table and partition level ...
 

I hope this is helpful to you!

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Read about Enhanced Refresh

• For enhanced refresh, notifyOption is not required and must be excluded from the request body. However, one or more parameters other than notifyOption are required.

You can specify tables or even partitions, and you will receive a requestID that you can then use to poll the refresh status.

Datasets - Refresh Dataset In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn