Terminate Long Running Azure Analysis Services Refresh Using Logic App

Problem

Azure Analysis Services (AAS) management tasks automation can be achieved by using cloud applications. I explained the design of the application modules for AAS model refresh in my earlier tips (see Automate Azure Analysis Services Management Tasks using Logic App - Part 1, Automate Azure Analysis Services Management Tasks using Logic App - Part 2 and Automate Azure Analysis Services Management Tasks using Logic App - Part 3). Sometimes, we need to terminate long-running AAS refresh jobs. Unfortunately, Microsoft's out of box tools, like Azure Portal or SQL Server Management Studio do not allow terminating currently running refresh jobs. I'll demonstrate how to build a Logic App, to terminate the AAS model refreshes, in this tip.

Solution

Application design and pre-requisites

If you've seen the previous tips in this series, you already know that API calls are the main methods of managing AAS refresh tasks. The AAS refresh termination API accepts an HTTP request with the Delete method and the Refresh ID value of the ongoing refresh (see here for more details).

The application we'll build here has two modules:

The core module - performs the actual termination
The parent module - calls the core module for different models

Get the following pieces of information, before we continue:

AAS server name - You can get this info 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 the App Registrations page of the Azure Portal (see this article for more details).
Tenant ID - You can get this info from your Azure Active Directory's 'Properties' tab (the Directory ID field) or App Registrations page for the application you created on the Azure Portal.

Build the code module

Let's create a new Logic App, and name it as Cancel AAS Refresh. Add an HTTP requesttrigger as its first component, and include the following JSON string in its body:

{
    "properties": {
        "ModelName": {
            "type": "string"
        }
    },
    "type": "object"
}

This json schema allows passing the ModelName parameter from the external application.

To extract the ModelName parameter's value, add an Initialize variable action and configure it to assign the HTTP request's ModelName attribute to the variable ModelName.Here's the screenshot with the configurations of two components, we created so far:

Next, add another Initialize variable action for the variable RefreshId and assign it a string type, as follows:

Next, add an HTTP request with the GET method. Provide the URI in the format https://your server region/servers/AAS server name/models/ @{variables('ModelName')} /refreshes. This request will receive the refresh history for the given model. Enter key-value pair Content-Type: application/json into the Headers field. Select Authentication from Add new parameter drop-down list, and set its fields as follows:

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

Here's the screenshot with the settings for HTTP action:

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.

Next, add a Filter array action and assign the HTTP requests' Body attribute to its From field. This action will filter out only currently running refresh details, for the given model. Select the 'Edit in advanced mode' ellipsis button at the bottom left corner of the Filter array and enter an expression @equals(item()?['status'], 'inProgress'), as follows:

Here's the screenshot with this action's settings:

Next, add a Data operations action,name it as 'Parse JSON', assign the filter array's Body attribute to its Content field and enter the following JSON schema in its Schema field:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "refreshId": {
                "type": "string"
            },
            "startTime": {
                "type": "string"
            },
            "status": {
                "type": "string"
            }
        },
        "required": [
            "refreshId",
            "startTime",
            "status"
        ]
    }
}

Here's the screenshot with the Parse JSONaction's settings:

Next, add a 'For each' action and assign the Parse JSON action's Body attribute to its looping condition, as follows:

Add a Set variable action as a first component into the 'For each' action and use it to assign the Parse JSONaction's refreshId attribute to the variable RefreshId, as follows:

Next, add HTTP request action with the DELETE method. Enter URI in the following format: https://your server region/servers/AAS server name/models/ and the authentication details, like you did for the previous HTTP request action. Here's the screenshot with the HTTP request's settings:

Finally, add HTTP response action at the end of 'For each' action, as follows:

Here's the screenshot with the entire data flow we built so far:

Build the parent module

Let's create a new Logic App for the parent module and add a When HTTP request received trigger there.

Next, add an Initialize variable action and configure it to assign the names of the models you want to terminate, to the variable ModelNames, as follows:

Add For each action and assign the ModelNamesvariable to its looping condition.

Finally, add Azure Logic Apps action, select the app Cancel AAS Refreshfrom the list ofavailable applications. Assign Current item attribute for the 'For each' action, as follows:

This is all that is required to terminate the model refreshes.

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-07-02

Terminate Long Running Azure Analysis Services Refresh Using Logic App - Part 4