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


By:   |   Updated: 2020-07-02   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > Azure


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:

model name

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

model name

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:

model name

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.

secure outputs

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:

filter array

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

filter array

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:

parse json

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

dynamic content

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:

model name

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:

set variable

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

refresh history

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

http request

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:

initialize variable

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:

model names

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

Next Steps


Last Updated: 2020-07-02


get scripts

next tip button



About the author
MSSQLTips author Fikrat Azizov Fikrat Azizov has been working with SQL Server since 2002 and has earned two MCSE certifications. Hes currently working as a Solutions Architect at Slalom Canada.

View all my tips





Comments For This Article





download





Recommended Reading

Overview of Azure Analysis Services and Logic App Tasks - Part 1

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

Automate Azure Analysis Services Model Refreshes with Small, Medium and Large Tables using Logic App - Part 3

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1








get free sql tips
agree to terms


Learn more about SQL Server tools