Logic App to Refresh / Calculate / Sync Azure Analysis Services models

In this post, I will show how you can perform automated Refresh / Calculate / Sync the Azure Analysis Services models in one go by using Logic Apps and REST calls.

Some of pros of using Logic App, apart from being so handy and ready to use enterprise connectors available.

1. You can perform parallel refresh

2. Easy monitoring of executions

3. You can now have all the details with you whenever refresh happens 

Prerequisites

1. Create a Service Principal (SPN)

2. Configure permissions in Azure Analysis Services

3. Logic App

Once Logic app is created, Go to Identity and turn on Manage Identity.

You will get Object Id of logic app here, which is required to register in AAS.

To configure permissions in Azure Analysis Services,

You need to add a service principal to the server administrator role.

The easiest way to do this is to get application_id of logic app and tenant_id of your subscription by running below command and add those details in your SSMS:

To get application_id using azure-shell:

      Get-AzADServicePrincipal -ObjectID "ObjectID of Logic App, you can get it from 'Identity' tab"

      Get-AzADServicePrincipal -ObjectID "abcd1234-01ab-0aa0-a11b-ww123456798"

To get tenant_id:

        azure account show --json | jq -r '.[0].tenantId'

To complete this, you must have server administrator permissions on the Azure AS server.

1.       In SSMS, connect to your Azure AS server.

2.       In Server Properties > Security, click Add.

3.        In Manual Entry add "app:application_id@tenant_id"

Once all these setup done, Open Logic app and go to Logic App Designer,

1.  Choose the first action as When a HTTP request is received.

This step will populate with the HTTP POST URL once the Logic App is saved.

 

Request Body JSON Schema:

{

    "properties": {

        "AasName": {

            "type": "string"

        },

        "Activity": {

            "type": "string"

        },

        "ModelName": {

            "type": "string"

        },

        "Objects": {

            "type": "array"

        },

        "RefreshOperationId": {

            "type": "string"

        },

        "RolloutEnv": {

            "type": "string"

        }

    },

    "type": "object"

}

2.  Switch statement according to the input request

  

condition: 

@triggerBody()?['Activity']

3.  Add CASE statement with all input request operations

Do the refresh/calc/sync by using HTTP POST.

This will trigger refresh/calc/sync on AAS. You will get RefreshOperationId in Body

 

URI: 

@{concat('https://',triggerBody()?['RolloutEnv'],'/servers/',triggerBody()?['AasName'],'/models/',triggerBody()?['ModelName'],'/refreshes')}

Body:
{

  "CommitMode": "transactional",

  "MaxParallelism": 10,

  "Objects": @{triggerBody()?['Objects']},

  "RetryCount": 2,

  "Type": "Full"

}

Body:

{

  "RefreshOperationId": @{body('REST_POST_to_Refresh_AAS')['OperationId']},

  "body": @{body('REST_POST_to_Refresh_AAS')},

  "headers": @{outputs('REST_POST_to_Refresh_AAS')['headers']},

  "runId": @{workflow()['run']['name']},

  "status_code": @{outputs('REST_POST_to_Refresh_AAS')['statusCode']}

}

4.  Do HTTP GET To get the status of particular RefreshOperationId 

 
URI:

@{concat('https://',triggerBody()?['RolloutEnv'],'/servers/',triggerBody()?['AasName'],'/models/',triggerBody()?['ModelName'],'/refreshes/',triggerBody()?['RefreshOperationId'])}

Body:
{

  "body": @{body('REST_GET_to_get_the_status_of_Refresh')},

  "headers": @{outputs('REST_GET_to_get_the_status_of_Refresh')['headers']},

  "runId": @{workflow()['run']['name']},

  "status": @{body('REST_GET_to_get_the_status_of_Refresh')['status']},

  "status_code": @{outputs('REST_GET_to_get_the_status_of_Refresh')['statusCode']}

}

Once all done your app is ready to use this Logic app.

Just change the following value in the component to do Calculate and Sync

In order to Calculate model on analysis services a REST POST call must be performed on

https://<rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/calc

URI:
@{concat('https://',triggerBody()?['RolloutEnv'],'/servers/',triggerBody()?['AasName'],'/models/',triggerBody()?['ModelName'],'/calc)}

Body:

{

  "Type": "calculate"

}

In order to Sync model on analysis services a REST POST call must be performed on

https://<rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/sync

URI:

@{concat('https://',triggerBody()?['RolloutEnv'],'/servers/',triggerBody()?['AasName'],'/models/',triggerBody()?['ModelName'],'/sync)}

Do It Yourself and let me know in the comment section if you've implemented this or faced any challenge in doing the same.

#KeepDoiningAzureStuff

#KeepLearningAzureStuff