Process Analysis Service with Data Factory only

Case
There are several methods to process Azure Analysis Services models like with Logic Apps, Azure Automation Runbooks and even SSIS, but is there an Azure Data Factory-only solution where we only use the pipeline activities from ADF?

Process Azure Analysis Services

Solution
Yes you can use the Web Activity to call the Rest API of Azure Analysis Services (AAS), but that requires you to give ADF permissions in AAS via its Managed Service Identity (MSI).

1) Create ADF service principal
In the next step we need a user which we can add as a Server Administrator of AAS. Since we will not find the managed identity of ADF when we search for a user account, we will have to create one. This 'user' is called a service principal.

• Go to ADF in the Azure portal (not the Author & Monitor environment)
• In the left menu click on Properties which you can find under General
• Copy the 'Managed Identity Application ID' and the 'Managed Identity Tenant' properties to a notepad and construct the following string for the next step:
  app:<Application ID>@<Tentant> (and replace the <xxx> values with the properties)
  app:653ca9f9-855c-45df-bfff-3e7718159295@d903b4cb-ac8c-4e31-964c-e630a3a0c05e

Create app user from ADF for AAS

2) Add user as Server Administrator
Now we need to connect to your Azure Analysis Services via SQL Server Management Studio (SSMS) to add the user from the previous step as a Server Administrator. This cannot be done via the Azure portal.

• Login to your AAS with SSMS
• Right click your server and choose Properties
• Go to the Security pane
• Click on the Add... button
• Add the service principal from the previous step via the Manual Entry textbox and click on the Add button
• Click on Ok the close the property window

Add Server Administrator via Manual Entry

After this step the 'user' will appear on the portal as well, but you can not add it via the portal.

Analysis Services Admins

3) Add Web Activity
In your ADF pipeline you need to add a Web Activity to call the Rest API of Analysis Services. First step is to determine the Rest API URL. Replace in the string below, the <xxx> values with the region, servername and modelname of your Analysis Services. The Rest API method we will be using is 'refreshes':
https://<region>.asazure.windows.net/servers/<servername>/models/<modelname>/refreshes

Example:
https://westeurope.asazure.windows.net/servers/bitoolsserver/models/bitools/refreshes

Second step is to create a JSON message for the Rest API to give the process order to AAS. To full process the entire model you can use this message:

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

Or you can process particular tables within the model with a message like this:

{
    "Type": "Full",
    "CommitMode": "transactional",
    "MaxParallelism": 2,
    "RetryCount": 2,
    "Objects": [
        {
            "table": "DimProduct",
            "partition": "CurrentYear"
        },
        {
            "table": "DimDepartment"
        }
    ]
}

See the documentation for all the parameters that you can use.

• Add the Web activity to your pipeline
• Give it a descriptive name like Process Model
• Go to the Settings tab
• Use the Rest API URL from above in the URL property
• Choose POST as Method
• Add the JSON message from above in the Body property
• Under advanced choose MSI as Authentication method
• Add 'https://*.asazure.windows.net' in the Resource property (note this URL is different for suspending and resuming AAS)

Web Activity calling the AAS Rest API

Then Debug the Pipeline to check the process result

4) Retrieve refreshes
By only changing the method type from POST to GET (body property will disappear) you can retrieve information about the processing status and use that information in the next pipeline activities.

Retrieve process status via GET

Summary
In this post you learned how process your Analysis Services models with only Azure Data Factory. No other services are needed which makes maintenance a little easier. In a next post we will also show you how to Pause or Resume your Analysis Services with Rest API. With a few extra steps you can also use this method to refresh a Power BI dataset, but we will show that in a future post.

Update: firewall turned on?

Azure Data Factory - Use Key Vault Secret in pipeline

Case
I want to use secrets from Azure Key Vault in my Azure Data Factory (ADF) pipeline, but only certain properties of the Linked Services can be filled by secrets of a Key Vault. Is it possible to retrieve Key Vault secrets in the ADF pipeline itself?

Using Azure Key Vault for Azure Data Factory

Solution
Yes the easiest way is to use a web activity with a RestAPI call to retrieve the secret from Key Vault. The documentation is a little limited and only shows how to retrieve a specific version of the secret via the secret identifier (a guid). This is not a workable solution for two reasons:

1. The guid changes each time you change the secret. In probably 99% of the cases you just want to get the latest of version of the secret. This means you need to change that guid in ADF as well when you change a secret.
2. The guid differs on each environment of your key vault (dev, test, acceptance and production). This makes it hard to use this solution in a multi ADF environment.

For this example we will misuse Key Vault a little bit as a configuration table and retrieve the RestAPI url of a database from the Key Vault. The example assumes you already have a Key Vault filled with secrets. If you don't have that then executes the first two steps of this post.

1) Access policies
First step is to give ADF access to the Key Vault to read its content. You can now find ADF by its name so you don't have to search for its managed identity guid, but using that guid is also still possible.

• Go to Access policies in the left menu of your Key Vault
• Click on the blue + Add Access Policy link
• Leave Configure from template empty
• Leave Key permissions unselected (we will only use a Secret for this example)
• Select Get for Secret permissions
• Leave Certificate permissions unselected (we will only use a Secret for this example)
• Click on the field of Select principal to find the name of your Azure Data Factory
• Leave Authorized application unchanged
• Click on Add and a new Application will appear in the list of Current Access Policies

Add Access policy

Note: for this specific example we do not need to create a Key Vault Linked Service in ADF.

2) Determine URL of secret
To retrieve the secrets we need the RestAPI URL of that secret. This URL is constructed as
https://{Name Keyvault}.vault.azure.net/secrets/{SecretName}?api-version=7.0

{Name Keyvault} : is the name of the keyvault you are using
{SecretName} : is the secretName

In this example the secretName is "SQLServerURL" and the URL should be looking like this https://{Name Keyvault}.vault.azure.net/secrets/SQLServerURL?api-version=7

Get the SecretName from Key Vault

3) Web activity
Next we have to add the activity ‘web’ into the ADF pipeline. Use the following settings in the settings tab.

• Set URL to https://{Name Keyvault}.vault.azure.net/secrets/SQLServerURL?api-version=7.0
• Set Method to Get
• Under Advanced select MSI
• And set the resource to https://vault.azure.net

Configuring the Web activity to retrieve the secret

4) Retrieve value
Now we want to use the secret from the Key Vault in a successive activity, in this case another web activity to upscale a database. In the URL property of this activity we now use the output value from the previous webactivity.

@activity('GetURL').output.value

Retrieve output value via expression

5) The result
To check the result of the changes we need to execute the pipeline.

Execute the pipeline to check the result

Note: if you are using this to retrieve real secrets like passwords and you don't want them to show up in the logging of Azure Data Factory then check the Secure output property on the general tab of your activity.

Don't show secret in logging

Conclusion
In this blogpost your learned how to retrieve Key Vault secrets in ADF. The trick is to retrieve them by there name instead of by there version Guid. This will always give you the latest version and allows you to use this construction in multiple environments.

Update: ADF now supports Global parameters to store parameters that can be used by all pipelines

Schedule Up/Downscale Azure SQL Database (API)

Case
I want to up- and downscale my Azure SQL Database within my ADF pipeline. You recently showed how do this with Powershell code in an Automation Runbook, but that is too much coding for me. You also showed how to do this with some simple TSQL code, but that requires a lot of database permissions for the ETL user. Is there a codeless solution in Azure Data Factory which doesn't require permissions within my Azure SQL Database?

Change the Tier of your SQL Azure DB

Solution
Yes: Rest API! And don't worry there is no coding required. For this example we will use the Web activity to call the Rest API of SQL Server. This doesn't require any coding or permissions within the database itself. However you need ofcourse some permissions to change the database Pricing Tier. For this we will be using managed identities for Azure resources: we will give the Azure Data Factory permissions to the Azure SQL Server.

For this example we assume you already have an ADF with a pipeline for your ETL which we will extend with an upscale and a downscale.

Stage multiple tables in a foreach loop

1) Assign permissions to ADF
The permissions will be granted to the Azure SQL Server and not to a specific database. To change the database Pricing Tier with ADF, we need a role that can only change the database settings, but nothing security related: Contributer, SQL DB Contributer or SQL Server Contributer.

• Go to the Azure SQL Server of the database that you want to up- or downscale with ADF
• In the left menu click on Access control (IAM)
• Click on Add, Add role assignment
• In the 'Role' drop down select 'SQL DB Contributer'
• In the 'Assign access to' drop down select Data Factory
• Search for your Data Factory, select it and click on Save

Note: if you forget this step you will get an error while executing the pipeline in ADF

Not enough permissions

{
    "errorCode": "2108",
    "message": "{\"error\":{\"code\":\"AuthorizationFailed\",\"message\":\"The client 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' with object id 'xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' does not have authorization to perform action 'Microsoft.Sql/servers/databases/write' over scope '/subscriptions/xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/Joost_van_Rossum/providers/Microsoft.Sql/servers/bitools2/databases/Stage' or the scope is invalid. If access was recently granted, please refresh your credentials.\"}}",
    "failureType": "UserError",
    "target": "Upscale Stage DB",
    "details": []
}

2) Get URL Rest API
Now it is almost time to edit your ADF pipeline. The first step will be adding a Web activity to call the Rest API, but before we can do that we need to determine the URL of this API which you can find here. Check the api version because it changes regularly.

https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}?api-version=2021-02-01-preview

Within this URL you need to replace all parts that start and end with a curly bracket: {subscriptionId}, {resourceGroupName}, {serverName} and {databaseName} (including the brackets themselves). Don't use a URL (bitools2.database.windows.net) for the database server name, but use only the name:

https://management.azure.com/subscriptions/xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/Joost_van_Rossum/providers/Microsoft.Sql/servers/bitools2/databases/Stage?api-version=2021-02-01-preview

3) JSON message for Rest API
The Rest API expects a JSON message with the pricing tier for which you can find the details here under SKU. For some reason the location is required in these messages. Here are two example which you need to adjust for your requirements:

{"sku":{"name":"S1","tier":"Standard"}, "location": "West Europe"}

or

{"sku":{"name":"Basic","tier":"Basic"}, "location": "West Europe"}

3) Upscale DB
Go to your existing ADF pipeline to add an upscale (and downscale) activity

• Add a Web activity in front of the existing Lookup activity
• Give it a descriptive name: Upscale Stage DB
• Go to the Settings tab of the Web activity
• For URL enter the URL you created in step 2
• For Method choose PUT
• Add a new Header called 'Content-Type' with the value 'application/json'
• Copy the JSON message that you create in step 3 to the Body
• Collapse Advanced at the bottum
• Choose MSI (old name for Managed Instance) as authentication
• Enter 'https://management.azure.com/' in the Resource field. This is the URL for Managed Identity

Add Web activity to call Rest API

4) Downscale DB
Repeat the previous step, but now add the downscale activity at the end

• Add a second Web activity, but now after your ETL activity (foreach in this example)
• Give it a descriptive name: Downscale Stage DB
• Go to the Settings tab of the Web activity
• For URL enter the URL you created in step 2
• For Method choose PUT
• Add a new Header called 'Content-Type' with the value 'application/json'
• Copy the JSON message that you create in step 3 to the Body
• Collapse Advanced at the bottum
• Choose MSI (old name for Managed Instance) as authentication
• Enter 'https://management.azure.com/' in the Resource field

Adding an upscale and downscale via Rest API

Conclusion
In this post you learned how to give a resource (ADF) access to an other resource (SQL Server) via Managed Identities. Then we showed you how to call a Rest API in an ADF pipline Web activity for which we didn't have to write any code at all. From all the different options we showed you to Up- and Downscale an Azure SQL Database this is probably the easiest and safest method especially when you want to incorporate in your ETL process. An other positive thing is that the Rest API call is synchronous which means it doesn't start the ETL before the database has been upscaled. So no extra wait activities required.