Asynchronous Refresh of AAS Model using PowerShell

With the introduction of REST API to refresh the analysis services model, we can now perform asynchronous data refresh operations. This also includes synchronization of read-only replicas for query scale out.

Some of the pros of using this approach is:

  1. Since the requests are asynchronous, there is no need for long running HTTP connections
  2. In response we get a operation id which can be used to query the status of the job
  3. In-built functionality for auto-retries
  4. Multiple table/partition names can be passed so that only that part is refreshed
  5. Batch Processing is supported using the “CommitMode” parameter

More details of the API can be found here, which includes the permissions required to make the calls along with detailed info of all the parameters:

https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-async-refresh

As mentioned in above documentation, you need a valid access token to make this call. The PowerShell to get that token via certificate auth or via client id/secret can be found here. The code works perfectly, just make sure to use the correct scope which in this case is: https://*.asazure.windows.net/.default. Also the user or application making the call should be server admin on the AAS.  The code is quite simple afterwards (used azure functions for the below):

using namespace System.Net
# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)
# Init defaults
$status = [HttpStatusCode]::BadRequest
$ErrorActionPreference = "STOP"
$RequestBody = $Request.Body
if (!$RequestBody) {
Push-OutputBinding Name Response Value ([HttpResponseContext]@{
StatusCode = $status
Body = "Please pass a valid body in the request's body."
})
}
else {
# Retrieve AAS details from request body
$AnalysisServerName = $RequestBody.AnalysisServerName
$AnalysisServerLocation = $RequestBody.AnalysisServerLocation
$ModelName = $RequestBody.ModelName
$AASObject = $RequestBody.AASObject
#check if AASObject is empty or not. Irrespective of documentation
#We need Refresh Type for a call as mandatory param
if (!$AASObject -or ($AASObject.Count -eq 0)) {
$AASObject = @{
'Type' = 'Full'
}
}
Connect-AzAccount
try {
$analysisServerResource = Get-AzAnalysisServicesServer Name $AnalysisServerName
Write-Output "Fetching access token to refresh the model"
#fetch access token which will be used to refresh the models
$Output = New-Object TypeName hashtable
Get-AccessToken Output $Output #fetch access token using the PS here https://adamtheautomator.com/microsoft-graph-api-powershell/
#request body to be sent to AAS REST API
$jsonBody = $AASObject | ConvertTo-Json;
Write-Output "The body of the REST API call to refresh the model is"
Write-Output $jsonBody
$uri = "https://$AnalysisServerLocation.asazure.windows.net/servers/$AnalysisServerName/models/$ModelName/refreshes"
Write-Output "Invoking POST $uri"
#$Output.AuthHeaders follows the following format
#$Output.AuthHeaders = @{
#'Content-Type' = 'application/json'
#'Authorization' = "Bearer " + access_token
#}
$PostSplat = @{
Method = 'POST'
Body = $jsonBody
Uri = $uri
Headers = $Output.AuthHeaders
}
$results = Invoke-RestMethod @PostSplat
Write-Output $results
$status = [HttpStatusCode]::OK
Push-OutputBinding Name Response Value ([HttpResponseContext]@{
StatusCode = $status
Body = $results
})
}
catch {
Write-Output "Exception was thrown. Details below."
$_
Push-OutputBinding Name Response Value ([HttpResponseContext]@{
StatusCode = $status
Body = $_
})
}
}

view raw
RefreshAASModel.ps1
hosted with ❤ by GitHub

The $results includes an operationId which can be used to make a GET call to fetch status of the operation. The payload for the above API looks similar to:

{
“AnalysisServerName”: “analysisservername”,
“AnalysisServerLocation” : “northeurope”,
“ModelName”: “adventureworks”,
“AASObject”: {
“Type”: “Full”,
“CommitMode”: “transactional”,
“MaxParallelism”: 2,
“RetryCount”: 2,
“Objects”: [
{
“table”: “DimCustomer”,
“partition”: “DimCustomer”
},
{
“table”: “DimDate”
}]
}}

If no “Objects” are specified above, the whole model is refreshed.

Also note, that only one request is accepted per model. If there is already an operation running and another is submitted, server will return 409 Conflict HTTP Status Code. Therefore, the return result is very important as we can use the id to query the status of the already running operation.

If you want to sync the new data with replicas for query scale-out, you need to make a POST request to the /sync endpoint separately. The above code for all the operations remain the same except the different in $uri and request body.

Hope this helps!

Happy Coding!