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

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!