Automate Azure Analysis Services Model Refresh with Logic App

Problem

Azure Analysis Services (AAS) management tasks automation enables building cloud applications. In this series of tips, I'm going to share my experience on using Azure Logic App to automate Azure Analysis Services model refresh tasks. In the previous tip (Automate Azure Analysis Services Management Tasks using Logic App - Part 1) I've created a core application module, explained how to extract the input parameters and how to construct JSON body for AAS refresh API requests. I'm going to complete the design of the core module by adding model refresh and logging sections in this tip.

Solution

Initiate the model refresh

As mentioned earlier, we'll need to send HTTP POST request to AAS, to initiate the refresh process. We'll need the following pieces of information, before we continue:

AAS server name: You can get this information from your AAS server's overview page on Azure Portal.
Client Id and Secret: You'll need to create a new application and add a secret for it, from App Registrations page of the Azure Portal (see this article for more details).
Tenant ID: You can get this information from your Azure Active Directory's 'Properties' tab (Directory ID field) or App Registrations page for the application you created on the Azure Portal.

Once you get above mentioned authentication details, add HTTP request to your flow, with the following settings:

Method: POST
URI: Use your AAS server name and ModelName variable, to create an expression like https://your server region/servers/aas server name/models/ @{variables('ModelName')} /refreshes
Headers: Content-Type, application/json
Body: Use variable JSonBody we created earlier

Next, select Authentication from Add new parameter drop down list, to add authentication parameters section, and set its fields as follows:

Authentication type: Active Directory Oath
Tenant: Use your Tenant ID acquired above
Audience: Type https://*.asazure.windows.net
Client ID: Use your Client ID acquired above
Credential Type: Secret
Secret: Use your secret acquired above

Here's the screenshot with all these settings:

You can read more about these configuration settings here.

Next, click the ellipsis button at the top right corner of the HTTP action, select Settings command, turn off the Asynchronous Pattern switch and click 'Done' at the bottom of the screen.

Initiate Refresh Status in a Loop

The AAS refresh initiation API is an asynchronous process. Since we've already initiated refresh in the previous step, we can add a flow to initiate refresh status in a loop.

Let's add a Initialize variable action to the end of the flow and type HttpUri as the variable name and string as the type.

Next, enter an expression outputs('HTTP_3')['headers']['location'] in the value box. Note that this expression includes HTTP request's name we created in the previous step. Next, add a ForEach action and enter an expression @or(equals(variables('RefreshStatus'), 'succeeded'),equals(variables('RefreshStatus'), 'failed')) in its validation box. Optionally, open 'Change limits' tab and set maximum number of iterations or a timeout limit.

Next, add HTTP request inside the ForEach action, select GET method for it and configure authentication settings in the same way, as we did for refresh initiation request earlier. Here's the screenshot:

Next, in order to store the status of ongoing refresh in the variable, let's add a Set variable action and assign the Status code attribute for HTTP 2 action to the variable RefreshStatus, as follows:

Finally, add a Delay action, to pause the flow for 30 sec. Here's the screenshot with all components we added in this section:

Log Refresh Statistics and Errors into Tables

Now that we have built the core functionalities for the AAS refresh, let's add logging functionality.

First, add a Initialize variable action and assign the function utcNow() to the variable RefreshEndTime.

Next, we need to determine if the refresh has failed and log the error message into the corresponding table. So, let's add a condition action with the name 'Log error message if refresh failed' and set its condition to validate, if the variable RefreshStatus equals to 'failed' string.

Leave the false outcome of this condition empty and add a Data operation action to its true output, to parse the JSON string with the error. Name it as Parse JSON, add the Body attribute of the HTTP 2 action to its Content, as well as the following JSON string to its Schema fields:

{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "properties": {
        "currentRefreshType": {
            "type": "string"
        },
        "endTime": {
            "type": "string"
        },
        "messages": {
            "items": [
                {
                    "properties": {
                        "message": {
                            "type": "string"
                        },
                        "type": {
                            "type": "string"
                        }
                    },
                    "required": [
                        "message",
                        "type"
                    ],
                    "type": "object"
                }
            ],
            "type": "array"
        },
        "objects": {
            "items": [
                {
                    "properties": {
                        "status": {
                            "type": "string"
                        },
                        "table": {
                            "type": "string"
                        }
                    },
                    "type": "object"
                }
            ],
            "type": "array"
        },
        "startTime": {
            "type": "string"
        },
        "status": {
            "type": "string"
        },
        "type": {
            "type": "string"
        }
    },
    "required": [
        "startTime",
        "endTime",
        "type",
        "status",
        "messages"
    ],
    "type": "object"
}

Here is the screenshot with the newly added component:

Next, add a Set variable action to the same flow and assign the Parse JSON action's 'messages' attribute to the ErrMessages variable, as follows:

Finally, let's add a SQL server Execute stored procedure (V2) action (I've named it as 'Log errors' ) to the error handling flow, configure it to call the [SP_LOG_AAS_REFRESH_ERRORS] stored procedure we created earlier, and assign variables to its parameters, as follows:

This concludes error logging part of the core module.

We need to add two more actions, to complete the core module. Add another Execute stored procedure (V2) action after the 'Log error message if refresh failed' action and configure it to call SP_LOG_AAS_REFRESH_STATS procedure, to collect refresh statistics. Here is screenshot with the settings:

The last component of our flow is needed to return the model refresh status to its parent process. Add a HTTP response action with the following settings:

This concludes the design of the core module to refresh AAS models. I'll explain how to call this module from other applications in the next tip.

Next Steps

About the author

Fikrat Azizov has been working with SQL Server since 2002 and has earned two MCSE certifications. He’s currently working as a Solutions Architect at Slalom Canada.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-06-02

Automate Azure Analysis Services Model Refresh using Logic App - Part 2