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


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


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:

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:

model name

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:

http request

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:

refresh status

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

initialize variable

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:

parse json

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:

parse json

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:

parse json

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:

log refresh stats

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:

log refresh stats

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


Last Updated: 2020-06-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 Refreshes with Small, Medium and Large Tables using Logic App - Part 3

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

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