Source Code

Highlight

In 2017 asynchronous refresh API was released for Azure Analysis Services which allows users to refresh their models with simple REST calls. This allows for easy integration with their orchestration solutions. Learn how to build very simple logic apps and manage Azure Analysis Services refresh schedule.

Intro

Azure Analysis Services is a great in-memory analytical engine which allows enterprises to build very scalable and fast reporting solutions. In recent years Microsoft decided to provide this solution as Platform as a Service, bringing Azure Analysis Services. One of the key challenges in the cloud was refreshing analytical models which in the past was achieved using PowerShell scripts.

With release of refresh and sync API’s this process can be automated with variety of tools and services. In this article, a very simple Logic App will be created which will schedule model refresh and wait for its ending. This will present a classic principle of orchestration which then can be further integrated with other services.

Prerequisites

In order to execute this scenario a developer must have following tools

  1. Azure Subscription
  2. Azure Analysis Services with any model deployed
  3. SQL Server Management Studio 2017+

Design

In this article Azure Analysis Services REST API will be used. This second covers high level principles on how this process should be done.

High level architecture of this refresh will look like this.

With few principles in mind

  • Analysis Services Refresh API is asynchronous so after first call logic app will only receive accepted (202) response containing refresh ID.
  • Logic app will asking for refresh status until failed or succeeded status is received
  • Logic app will wait 30 seconds between retries to give analysis services refresh time to refresh the model

In high level Azure Analysis Services API looks as follows

Refreshing model with POST /refreshes

In order to refresh model on analysis services a REST call must be performed on https://<rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/refresh

As per diagram

  • <rollout> region name where analysis services is deployed. This can be retrieved from analysis services URL.
  • <serverName> name of azure resource, this is also unique public DNS name for analysis services.
  • <resource> name of analysis services model deployed on the server.

Request

{
  "Type": "Full",
  "CommitMode": "transactional",
  "MaxParallelism": 2,
  "RetryCount": 2,
  "Objects": [
    {
        "table": "DimCustomer",
        "partition": "DimCustomer"
    },
    {
        "table": "DimDate"
    }
  ]
}
  • Type a TMSL refresh type. Accepted options are: full, clearValues, calculate, dataOnly, automatic, add, defragment.
  • CommitMode is defining if objects during refresh will be committed in batches or only when completed. Currently only three options are valid: default, transactional, partialBatch.
  • MaxParallelism how many parallel threads are processing the model. Must be within MaxParallelism setting of the server.
  • RetryCount how many times server retries operation before failing.
  • Objects array of objects to refresh. Tables or partitions are accepted. No objects on the list means entire model.

For more info check the documentation HERE.

Response (202 Accepted)

Empty

But there is very important header in the response. This header can be used to check status in following requests.

{
  "Location": "https://<rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/refreshes/<refreshId>"
}

Response (409 Conflict)

If there is already model refresh currently running request response with 409 code will be returned. This means in production environment a model refresh submission should be wrapped in the loop trying to submit refresh until no 409 is returned.

{
  "code": "Conflict",
  "subCode": 1,
  "message": "Refresh job could not be created since there is already an active job running for model '<resource>'",
  "timeStamp": "2019-06-16T22:15:37.7356458Z",
  "httpStatusCode": 409,
  "details": [
    {
      "code": "RootActivityId",
      "message": "e5103f37-def2-43b0-b974-9169c3acd91d"
    },
    {
      "code": "Param1",
      "message": "<resource>"
    }
  ]
}

Checking refresh status with GET /refreshes<refresh_id>

To get status of refresh a REST call must be performed on https://<rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/refreshes/<refreshId>

  • <rollout> region name where analysis services is deployed. This can be retrieved from analysis services URL.
  • <serverName> name of azure resource, this is also unique public DNS name for analysis services.
  • <resource> name of analysis services model deployed on the server.
  • <refreshId> name of analysis services model deployed on the server.

Request

Empty because it's GET

Response (200 OK)

{
  "startTime": "2019-06-16T22:24:06.9112816Z",
  "endTime": "2019-06-16T22:24:08.2919036Z",
  "type": "full",
  "status": "succeeded",
  "currentRefreshType": "full",
  "objects": [
    {
      "table": "<table_name>",
      "partition": "Partition",
      "status": "succeeded"
    }
  ]
}

Available statuses are

  • notStarted
  • inProgress
  • failed
  • succeeded

Which means process should check for either failed or succeeded. All responses are returned with 200 http status call.

Authentication

All calls must be authenticated with a valid Azure Active Directory (OAuth 2) token in the Authorization header and must meet the following requirements:

  • Only user or service principal tokens are accepted. If you want to use MSI (Managed Service Identity), then you must enable identity on logic app and use CLI or PowerShell to get application ID of new service principal. Which then must be added as admin to analysis Services.
  • Audience of the token must be set to https://*.asazure.windows.net.
  • User or service principal must have AAS administrator privileges.

Provisioning

Log into Azure Portal and start creating resources.


Hint

This can be automated quickly using ARM template that I created for this. Template can be found below.

logic-apps-arm.json


Check Application Account and Privilege setup sections to find out proper values for this template.

Resources

  1. Click on “+” sign to create new resource
  2. Type in logic app in Search window and press enter
  3. Click Create button
  4. Fill in basic info, name and location

Application Account

Log into Azure Portal and start creating resources.

  1. Go to Azure Active Directory into App registrations
  2. Click on New registration
  3. Name app, leave everything as default
  4. Copy Application (Client) ID, this will be required for later steps
  5. Click on Certificates & secrets
  6. Click on New Client secret
  7. Give it a name and expiration date and then select add
  8. Go to newly generated key and copy the value, this will be required for later steps
  9. Go to Azure Active Directory into Properties
  10. Copy Directory ID, this will be required for later steps

For further steps three properties are required

  • Directory ID
  • Application (Client) ID
  • Key (Secret)

Privilege setup

  1. Open SQL Server Management Studio
  2. Connect to Analysis Services
  3. Right-click on server and select Properties
  4. Go to Security page and click Add button
  5. Search for app by name given in previous step or add manual entry in following convention
    Convention IS
     app:<application_id>@<tenant_id>
     
    In this example this would mean value
     app:[email protected]1f1e69fab
     
  6. Click Add

Build Logic App

  1. Open Logic App and navigate to Designer
  2. Click on Blank Logic App block in the designer window
  3. Search for schedule trigger
  4. And select for recurrence action
  5. Add new step
  6. Search for HTTP action
  7. Configure HTTP call as follows
    • Method - submitting new refresh schedules are always done with POST calls
    • URI is built as https://<rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/refresh
      In this example the URL would be, rollout - region name, northeurope, servername - analysis services instance name, marczakioaas, resource - analysis services model name, aas-refresh-model
    • Headers - only header required is Content-Type with application/json value since a JSON configuration will be sent over
    • Body - Send over request details. For full refresh send below request.
       {
        "CommitMode": "transactional",
        "MaxParallelism": 2,
        "Objects": [],
        "RetryCount": 2,
        "Type": "Full"
      }
      It is also possible to refresh model partially. In this case in the Objects property send list of tables or partitions to refresh.
    • Authentication - Authenticate to Analysis Services using previously created application. Pass previously saved values and add https://*.asazure.windows.net URL as Audience.
  8. Click on three dots on current HTTP call step and select Settings
    2.Find Asynchronous Pattern option
    2.Unselect Asynchronous Pattern
  9. Add new step
  10. Search for Variables and add new Initialize Variable step. This variable will be used in loop iteration to check for refresh status.
    • Name - call it refreshStatus
    • Type - make it string
    • value - leave this empty
  11. Add new step
  12. Search for Control
  13. Add Until action
  14. Configure until loop condition to refreshStatus is not equal to succeeded
  15. In order to handle failed status an advanced editor must be used with following formula.
     @or(equals(variables('refreshStatus'), 'succeeded'), equals(variables('refreshStatus'), 'failed'))
     
  16. Add new step
  17. Search for Schedule action group and select Delay action
  18. Set it to 30 seconds, fine tune this setting later on.
  19. Optionally fine tune until loop limits.
  20. Click on add an action button and add new HTTP step
    • Method - getting status of refresh is done using GET method
    • URI is build as https://<rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/refreshes/<refresh_id>
      HTTP call which submits refresh to analysis services return “Location” header. This header contains full URL with refresh ID which then can be used to check refresh status. This can be obtained using following logic app expression.
      outputs('HTTP')['headers']['location']
    • Authentication - Authenticate to Analysis Services using previously created application. This should be configured exactly the same as in the first call.
  21. Add new step
  22. From Variables action group use Set variable action
  23. Configure with expression which replaces current value with retrieved status using following expression
    body('HTTP_2')['status']
  24. Save Logic App and run it
  25. Check the status
  26. Review logic app run

Final thoughts

When going to production it would be advised to add until loop which will try to submit process refresh until available slot comes appears. This of course depends on the refresh type and if process is not very complex or there are no multiple separate orchestration flows, then this might simply not be required.

Summary

A careful reader should by now understand and be able to execute following activities

  • Understand basics of Azure Analysis Services refresh API
  • Set up an application account (service principal) and assigning administrative privileges to this account on Azure Analysis Services
  • Submit REST calls to Azure Management API and controlling Azure Analysis Services Refreshes
  • Orchestrate workflows using Logic Apps

If you enjoyed this article you might want to check how to set up Analysis Services with On-premises gateway.

Source Code

Did you enjoy the article?

Share it!

More tagged posts