Process Azure Analysis Services objects using a Logic App part 2 – Jorg Klein’s Blog

Many of my blog posts are focused on Azure cost reduction as this is essential to keep in mind when designing cloud solutions. Azure is a very cost-effective platform when it’s used in the right way, meaning you always have to consider the fact Azure is elastic and make sure your solutions scale up and down in an efficient way. Previously I showed how to autoscale Azure Analysis Services and Azure SQL Database. This time I show how to process Azure Analysis Services objects (models) in a cost-conscious way.

In my last blog post I showed how to Process Azure Analysis Services objects from Azure Data Factory v2 using a Logic App. Based on a simple example I showed how to link Azure Data Factory and Logic Apps using a webhook and how to communicate with the Azure Analysis Services (Azure AS) REST API from a Logic App. For this blog post I teamed up with a cloud integration specialist from Macaw, Stefan Conijn, to create an advanced Logic App that scales Azure AS up, processes your Azure AS objects and scales Azure AS down again. This is a common pattern as processing is a memory consuming operation, which often requires to scale up. The solution is cost-conscious because it waits for the process operation to finish after which Azure AS will be scaled down immediately.

A couple of things that are good to know when using this solution:

• The Azure AS API is asynchronous. The only response you will get will be a 202 (accepted) status code that tells you the message arrived successfully, but that’s it. You need to poll for progress yourself. This is actually one of the advantages of using the API for processing because the traditional methods may require long-running HTTP connections. Given the nature of HTTP, it may be more reliable to avoid long-running HTTP connections from client applications as you can read in this blog post by Microsoft.
• Logic Apps can be whitelisted on your Azure AS firewall because the outbound IP adresses of Logic Apps per Azure region are published online. This means you can keep your firewall on while processing your models using a Logic App.
• The Logic App is by default configured to process your entire model. You can change body of the request to process specific objects (tables/partitions) as desired.

Below a screenshot of the Logic App. Every step is numbered and explained below the screenshot.

1. The actual trigger for the Logic App. Can be triggered manually or via an HTTP POST request.
2. A variable used to stop the ‘do until’ loop statement when upscaling of AAS is finished.
3. A variable used to stop the ‘do until’ loop statement when processing of AAS is finished.
4. A variable used to check if processing of AAS finished successfully.
5. HTTP PATCH request to the Azure Resource Management API to scale up the AAS instance.
6. ‘Do until’  loop that is used to wait for the upscaling of the AAS instance to finish.
7. A delay of 30 seconds in each cycle of the ‘do until’  loop.
8. HTTP GET request to the Azure Resource Management API to poll the status of the scaling process.
9. Parse the result of the GET request above, so that we have easy access to the response object.
10. Condition to check whether the scaling was finished. If so, set upscalingFinished variable to true so that the ‘do until’ loop is discontinued.
11. HTTP POST request to the AAS REST API to trigger a refresh of the model.
12. ‘Do until’  loop that is used to wait for the processing of the AAS model to finish.
13. A delay of 30 seconds minute in each cycle of the ‘do until’  loop.
14. HTTP GET request to the Azure Resource Management API to poll the status of the refresh process.
15. Parse the result of the GET request above, so that we have easy access to the response object.
16. Switch over the possible processing status values: inProgress, notStarted, succeeded or no matching value. If status equals succeeded set variables processingFinished and processingWasSuccessful to true. If status has no matching value, set variables processingFinished to true and processingWasSuccessful to false.
17. HTTP PATCH request to the Azure Resource Management API to scale down the AAS instance.
18. Check if processing was unsuccessful.
19. If processing was unsuccessfull set the status of the Logic App to ‘failed’ by using a ‘terminate’ action.
20. If processing was not unsuccessfull do nothing.

Deployment of the Logic App to your own Azure subscription is easy. First make sure you have a Service Principal (App Registration) that has the 3 permissions below.

1. Azure Analysis Services Server administrator
2. API Access: Azure Analysis Services (SQL Server Analysis Services Azure) – Read and Write all Models
3. Contributor role on the Azure Analysis Services service in Azure

A step-by-step guide to create a service principal and grant permissions 1 and 2 to it can be found in my previous blog post. To add the service principal to the Contributor role you have to navigate to your Azure AS service in the Azure portal, click “Access control (IAM)” and then click “+ Add”. Now select the contributor Role and select your service principal by searching on it’s name:

Now go to my github repository and click on the “Deploy to Azure” button. After you have logged in to Azure to following form appears. Fill the required parameters, agree with the terms and click on Purchase. After the deployment go to the created resource group and click on the Logic App resource. Click on Edit and verify that it was deployed correctly. Run the Logic App!

Many thanks to Stefan Conijn!!