Process Azure Analysis Services objects from Azure Data Factory v2 using a Logic App

Posted by Jorg Klein

In this blog post I will show how you can orchestrate processing of your Azure Analysis Services objects from Azure Data Factory v2. This makes it possible to process an Analysis Services model right after your Azure Data Factory ETL process finishes, a common scenario.

I will guide you through creating a Logic App that communicates with the Azure Analysis Services REST API and show how you can trigger it’s execution using a webhook from Azure Data Factory v2. The whole process will be executed using an Azure Active Directory App registration that has been granted access to process Azure Analysis Services objects via it’s REST API.

The following figure gives an overview of the process:

Now let me guide you through the following steps:

1. Create App Registration (application service principal) in Azure Active Directory
2. Grant App Registration permissions to process your Azure Analysis Services model
3. Create the Logic App that will communicate with the Azure Analysis Services REST API to process a model
4. Create an Azure Data Factory pipeline with a Web Activity that will trigger the execution of the Logic App

Create App Registration (application service principal) in Azure Active DirectoryNavigate to the Azure Active Directory page. On the Overview page, click “App registrations” followed by “+ New application registration”.

Enter a name for your App registration, select Web App / API for application type and enter a dummy Sign-on URL as we don’t intend to use Web Authorization.

In the Settings section for your App registration, click Required permissions.

In Select an API, type “Azure Analysis Services” into the search box. Then select “Azure Analysis Services”.

In Select permissions, select “Read and Write all Models” and then click the Select button. Then click Done to add the permissions. It may take a few minutes to propagate.

Back in the Settings section for your App registration, now click Keys.

Enter a key description, choose a duration and click Save. A new key will be created. Make sure to copy and save it as you won’t be able to retrieve it after you leave this blade.

The Application Registration is ready. Take note of the Application ID in the settings section for your App registration as you need it later when configuring the Logic App.

Azure Active Directory ID (Tenant ID)When you will configure your Logic App to use the App Registration we just created, you also need to provide a value for the Tenant property. This reflects to the Azure Active Directory ID in which the App Registration is created.

Navigate to the Azure portal, go to the Azure Active Directory page and click Properties. Now take note of the “Directory ID”.

Grant App Registration permissions to process your Azure Analysis Services modelConnect to your Azure Analysis Services server using SQL Server Management Studio. To process models using the API, the App Registration needs Server administrator permissions.

Open the Analysis Services Server Properties, click Security and click Add. You can add the App Registration as a manual entry using the Application ID (app guid) and the Azure Active Directory ID (tenant guid) that you saved before. Use the following syntax:

app:<app guid>@<tenant guid>

Create the Logic AppCreate a new Logic App from the Azure portal Marketplace and click Edit.

Pick the “When a HTTP request is received” trigger from the list of most commonly used triggers.

Click “+ New step”, “Add an action” and choose the “HTTP – HTTP” action.
Your Logic App Designer now contains all the necessary components to be triggered from Azure Data Factory and to communicate with the Analysis Services REST API.

We now need to configure the HTTP action. You can find background information regarding the Uri (Base URL) and Body in the REST API for Azure Analysis Services documentation on the Analysis Services Team Blog.

• Method: POST
• Uri: https://northeurope.asazure.windows.net/servers/myaas/models/MyModel/refreshes
  The base URL for the Azure Analysis Services REST API to process (refresh) models follows the following format:
  https:// <rollout>.asazure.windows.net/servers/<serverName>/models/<resource>/refreshesIn this URL replace the following values with parts of your Azure Analysis Services Server Name property and model name. In this example my server name was:
  asazure://northeurope.asazure.windows.net/myaas
  • <rollout>: northeurope
  • <serverName>: myaas
  • <resource>: MyModel (model name)
• Body: Read the REST API documentation on the Analysis Services Team Blog under “POST /refreshes” for more information. In the example below I will refresh my entire database (model) by a process Type Full, but processing specific tables or partitions (objects) is also possible.

  1 2 3 4 5 6 7

  {  "Type": "Full",  "CommitMode": "transactional",  "MaxParallelism": 2,  "RetryCount": 2,  "Objects": [] }

• Authentication: Active Directory OAuth
• Tenant: Use the Azure Active Directory ID that you saved before
• Audience: https://*.asazure.windows.net
• Client ID: Use the App Registration Application ID that you saved before
• Credential Type: Secret
• Secret: Use the App Registration Key that you saved before

Your Logic App HTTP Action should now look like this:

Test if everything works as expected by running your Logic App. You can fold the HTTP Action open to see the REST API response code and message.

The Logic App works and is ready to get executed from Azure Data Factory. Open the “When a HTTP request is received” trigger and take note of the HTTP POST URL.

Create the Azure Data FactoryCreate a new Azure Data Factory v2 from the Azure portal Marketplace. Click on the “Author & Monitor“ tile in your provisioned v2 data factory blade to open the visual tools for ADF v2.

In the visual tools, create a new pipeline and drag and drop a Web Activity on the pane.

Configuring the Web Activity is easy. Open the Settings tab and specifiy the following property values:

• URL: Use the HTTP POST URL of the Logic App that you saved before.
• Method: POST
• Body: A body is required but not needed for our Logic App to function the way we want, therefore use the Sample:

  1	{"message":"hello world"}

Your Web Activity Settings should now look like this:

Execute a Test Run and check the Output tab to see if everything works as expected.

Now open your Logic App and check if it ran succesfully.

Your Analysis Services model has started processing from Azure Data Factory! In a next blog post I plan to show how you can enhance the Logic App with a workflow that will poll the Analysis Services REST API to check when the process operation is completed.

Update: I have created a more advanced version of this Logic App, read about it here: Process