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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
BIanon
Resolver I
Resolver I

Programatic and asynchronus table refresh via XMLA endpoint

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

1 ACCEPTED SOLUTION
v-linyulu-msft
Community Support
Community Support

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.


 

View solution in original post

2 REPLIES 2
v-linyulu-msft
Community Support
Community Support

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
Super User
Super User

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.