Cuir in eagar

Comhroinn trí

Refresh with Azure Automation

  • Alt

By using Azure Automation and PowerShell Runbooks, you can perform automated data refresh operations on your Azure Analysis tabular models.

The example in this article uses the SqlServer PowerShell module. A sample PowerShell Runbook, which demonstrates refreshing a model is provided later in this article.

Authentication

All calls must be authenticated with a valid Microsoft Entra ID (OAuth 2) token. The example in this article uses a Service Principal (SPN) to authenticate to Azure Analysis Services. To learn more, see Create a service principal by using Azure portal.

Prerequisites

Important

The following example assumes the Azure Analysis Services firewall is disabled. If a firewall is enabled, the public IP address of the request initiator must be included in a firewall rule.

  1. In your Azure Automation Account, Click Modules, then Browse gallery.

  2. In the search bar, search for SqlServer.

    Search Modules

  3. Select SqlServer, then click Import.

    Import Module

  4. Click OK.

Create a Service Principal (SPN)

To learn about creating a Service Principal, see Create a service principal by using Azure portal.

Configure permissions in Azure Analysis Services

The Service Principal you create must have server administrator permissions on the server. To learn more, see Add a service principal to the server administrator role.

Design the Azure Automation Runbook

  1. In the Automation Account, create a Credentials resource which will be used to securely store the Service Principal.

    Screenshot that shows the "Credentials" page with the "Add a credential" action selected.

  2. Enter the details for the credential. In User name, enter the service principal Application ID (appid), and then in Password, enter the service principal Secret.

    Create credential

  3. Import the Automation Runbook.

    Screenshot that shows the "Runbooks" page with the "Import a runbook" action selected.

  4. Browse for the Refresh-Model.ps1 file, provide a Name and Description, and then click Create.

    Note

    Use script from Sample PowerShell Runbook section at the bottom of this document to create a file called Refresh-Model.ps1 and save to local machine to import into Runbook.

    Import Runbook

  5. When the Runbook has been created, it will automatically go into edit mode. Select Publish.

    Publish Runbook

    Note

    The credential resource that was created previously is retrieved by the runbook by using the Get-AutomationPSCredential command. This command is then passed to the Invoke-ProcessASADatabase PowerShell command to perform the authentication to Azure Analysis Services.

  6. Test the runbook by clicking Start.

    Screenshot that shows the "Overview" page with the "Start" action selected.

  7. Fill out the DATABASENAME, ANALYSISSERVER, and REFRESHTYPE parameters, and then click OK. The WEBHOOKDATA parameter is not required when the Runbook is run manually.

    Start the Runbook

If the Runbook executed successfully, you will receive an output like the following:

Successful Run

Use a self-contained Azure Automation Runbook

The Runbook can be configured to trigger the Azure Analysis Services model refresh on a scheduled basis.

This can be configured as follows:

  1. In the Automation Runbook, click Schedules, then Add a Schedule.

    Create schedule

  2. Click Schedule > Create a new schedule, and then fill in the details.

    Configure schedule

  3. Click Create.

  4. Fill in the parameters for the schedule. These will be used each time the Runbook triggers. The WEBHOOKDATA parameter should be left blank when running via a schedule.

    Configure parameters

  5. Click OK.

Consume with Data Factory

To consume the runbook by using Azure Data Factory, first create a Webhook for the runbook. The Webhook will provide a URL which can be called via an Azure Data Factory web activity.

Important

To create a Webhook, the status of the Runbook must be Published.

  1. In your Automation Runbook, click Webhooks, and then click Add Webhook.

    Add Webhook

  2. Give the Webhook a name and an expiry. The name only identifies the Webhook inside the Automation Runbook, it doesn't form part of the URL.

    Caution

    Ensure you copy the URL before closing the wizard as you cannot get it back once closed.

    Configure Webhook

    The parameters for the webhook can remain blank. When configuring the Azure Data Factory web activity, the parameters can be passed into the body of the web call.

  3. In Data Factory, configure a web activity

Example

Example Web Activity

The URL is the URL created from the Webhook.

The body is a JSON document which should contain the following properties:

Property Value
AnalysisServicesDatabase The name of the Azure Analysis Services database
Example: AdventureWorksDB
AnalysisServicesServer The Azure Analysis Services server name.
Example: https://westus.asazure.windows.net/servers/myserver/models/AdventureWorks/
DatabaseRefreshType The type of refresh to perform.
Example: Full

Example JSON body:

{
    "AnalysisServicesDatabaseName": "AdventureWorksDB",
    "AnalysisServicesServer": "asazure://westeurope.asazure.windows.net/MyAnalysisServer",
    "DatabaseRefreshType": "Full"
}

These parameters are defined in the runbook PowerShell script. When the web activity is executed, the JSON payload passed is WEBHOOKDATA.

This is deserialized and stored as PowerShell parameters, which are then used by the Invoke-ProcessASDatabase PowerShell command.

Deserialized Webhook

Use a Hybrid Worker with Azure Analysis Services

An Azure Virtual Machine with a static public IP address can be used as an Azure Automation Hybrid Worker. This public IP address can then be added to the Azure Analysis Services firewall.

Important

Ensure the Virtual Machine public IP address is configured as static.

To learn more about configuring Azure Automation Hybrid Workers, see Hybrid Runbook Worker installation.

Once a Hybrid Worker is configured, create a Webhook as described in the section Consume with Data Factory. The only difference here is to select the Run on > Hybrid Worker option when configuring the Webhook.

Example webhook using Hybrid Worker:

Example Hybrid Worker Webhook

Sample PowerShell Runbook

The following code snippet is an example of how to perform the Azure Analysis Services model refresh using a PowerShell Runbook.

param
(
    [Parameter (Mandatory = $false)]
    [object] $WebhookData,

    [Parameter (Mandatory = $false)]
    [String] $DatabaseName,
    [Parameter (Mandatory = $false)]
    [String] $AnalysisServer,
    [Parameter (Mandatory = $false)]
    [String] $RefreshType
)

$_Credential = Get-AutomationPSCredential -Name "ServicePrincipal"

# If runbook was called from Webhook, WebhookData will not be null.
if ($WebhookData)
{ 
    # Retrieve AAS details from Webhook request body
    $atmParameters = (ConvertFrom-Json -InputObject $WebhookData.RequestBody)
    Write-Output "CredentialName: $($atmParameters.CredentialName)"
    Write-Output "AnalysisServicesDatabaseName: $($atmParameters.AnalysisServicesDatabaseName)"
    Write-Output "AnalysisServicesServer: $($atmParameters.AnalysisServicesServer)"
    Write-Output "DatabaseRefreshType: $($atmParameters.DatabaseRefreshType)"
    
    $_databaseName = $atmParameters.AnalysisServicesDatabaseName
    $_analysisServer = $atmParameters.AnalysisServicesServer
    $_refreshType = $atmParameters.DatabaseRefreshType
 
    Invoke-ProcessASDatabase -DatabaseName $_databaseName -RefreshType $_refreshType -Server $_analysisServer -ServicePrincipal -Credential $_credential
}
else 
{
    Invoke-ProcessASDatabase -DatabaseName $DatabaseName -RefreshType $RefreshType -Server $AnalysisServer -ServicePrincipal -Credential $_Credential
}

Next steps

Samples
REST API