How to Automate Processing your Azure Analysis Services Models

An image of a cloud, surrounded by images of different cloud services, with a picture of Bit the Raccoon to the right.
A photo of Stephen Armory, Cloud Solution Architect at Microsoft

By Stephen Armory, Cloud Solution Architect at Microsoft

Introduction

Azure is a vast and varied service, so there are often a plethora of possible ways to tackle simple tasks. One example of this is “How to automatically process an Azure Analysis Services Model”. Numerous options exist, from writing your own Azure Function to PowerShell Scripting.

The “Processing” of Analysis Services Models is a typical admin task that falls to DBAs and Support Technicians to configure and monitor. As such, any approach to handling this shouldn’t include significant software development. In the on-premise world you could use SQL Server Agent to handle this.

The purpose of this article is to provide a simple methodology. This does not mean that it will necessarily be the best approach for you or your organisation, merely that we have considered a number of typical requirements when defining the process.

Summary of Approach

This approach leverages the following concepts, objects and services:

  1. Use of a Service Principal Name (SPN) within Active Directory.
  2. The Azure Automation service.
  3. Use of the “Azure.AnalysisServices” and “SqlServer” Automation Modules.
  4. Use of a Credential for the SPN.
  5. Some simple PowerShell script run by the Azure Automation service.
  6. SQL Server Management Studio (SSMS), to allow the management of the AS Model and Instance.

Benefits of Approach

We are proposing this approach for the following reasons:

  1. Simplicity of design: We have tried to keep the approach as simple as possible.
  2. Ease of implementation: By avoiding the use of software development.
  3. Re-usability: Once implemented, it should be possible to replicate the “Run Books” for each Processing Task.
  4. Maintainability and centralisation of control: By placing all the “Run Books” into a single “Azure Automation Account”.
  5. Logging and Reporting: Azure Automation provides excellent logging and reporting capabilities.
  6. Security: It is proposed that SPNs (Service Principal Names) are used in preference to standard user id’s and passwords. For improved security and manageability.
  7. Scheduling: Azure Automation provides excellent Scheduling capabilities.

Instructions

Step 1: Create your Service Principal Name (SPN).
Step 2: Give your SPN authority to administer Analysis Services.
Step 3: Get your AD Directory ID (AKA Tenant ID).
Step 4: Use SQL Server Management Studio (SSMS) to provide the Service Principal Name (SPN) with Admin access to the Analysis Services Model.
Step 5: Create the Azure Automation Service.
Step 6: Setup Azure Automation with the required Modules.
Step 7: Provide Automation with the credentials required to run the Analysis Services Refresh.
Step 8: Create your Azure Automation “Runbook”.
Step 9: Run your “Runbook” and next steps.

 


Step 1: Create your Service Principal Name (SPN)


Open the Azure Portal ( https://portal.azure.com ) and sign into your account.
Open “Azure Active Directory” and click on “App Registration” then click on “New application registration”.
Give the App-registration a name, select Application type of Web app/API
I’ve used a sign-on URL of “https://login.windows.net”, feel free to use your own if required.
Click “Create”.

You should now see the following dialog. Copy the “Application ID” onto notepad or equivalent, you will need this later.

 


Step 2: Give your SPN authority to administer Analysis Services.


Click on “Settings for your “Registered app” then click on “Required Permissions” you should see something like this. A screenshot of the 'Required Permissions' menu.

Now click on “+Add”.

Click on “1. Select an API”. A screenshot of the 'Add API Process' menu, with

Type in “Azure Analysis Services”.

Select “Azure Analysis Services” from the list.
Then click “Select” (At the bottom).

You should now see the following dialog. As shown, click on “Read and Write all models” and click “Select”. A screenshot of the 'Add API Process' menu, with "Read and Write All Models" selected.

You should now see the following dialog. Click on “Done”.

Return to the “Settings” dialog for the “Web app/API” and click on “Keys”.

Type a name for a new key into the “Key Description” area and select how long you want the key to last. Then click “Save”.

Copy the Key Value into Notepad as you will need this later.
Note: You should now have an “App Id” and a “Key” value in your notepad.

 


Step 3: Get your AD Directory ID (AKA Tenant ID).


Navigate to “Azure Active Directory” dialog. Scroll down and click on “Properties”.
Copy the “Directory ID” into your Notepad.
You should now have an “App ID”, “Key” and “Directory ID”.
Note: This is also known as the “Tenant ID”.
A screenshot of the 'Properties' window, showing the ser

 


Step 4: Use SQL Server Management Studio (SSMS) to provide the Service Principal Name (SPN) with Admin access to the Analysis Services Model.


Open SSMS and connect to your Azure Analysis Service Instance.
Right click on the server name to get the properties dialog.
A screenshot of the

Click on the “Security” option and you should see the following.
Click on the “Add” button”.

We are now going to provide Azure Analysis Service with the details of the SPN.

In the “Manual Entry” area construct the following string with values from your Notepad.
app:AppId@TenantId (AKA Directory Id)
e.g.

app:da5c1ba1-b922-4900-bf63-15c0f44cf535@72f988bf-52f1-41af-95ab-2d7cd011db77

Click “Add”.

Note: Don’t use the “Search” function, it breaks the AD connection.

 


Step 5: Create the Azure Automation Service.


It is recommended that you use a “Resource Group” to keep your components in a suitable location. Once in your resource group, click on “Add”. A screenshot showing the 'Overview' screen in the 'Resource Group' menu.

You should now see the following dialog. Type in “Automation”, then click on the “Automation” service. A screenshot showing the 'Automation' option being added to the 'Resource Group'.

You should now see the following dialog. Click on “Create” at the bottom.

You should now see the following dialog. Complete it as follows:
Name: Choose a suitable name.
Subscription: Choose the subscription you want to use.
Resource Group: Use the one you chose.
Location: Choose a suitable location.
Leave the rest of the defaults and click on “Create” at the bottom. This will take a couple of minutes to complete.
Go to the next step once completed.
A screenshot showing that an Automation account is being added.

 


Step 6: Setup Azure Automation with the required Modules.


A number of resources will have been created along with the Automation Account. Click on the Automation Account.

You should see the following dialog, click on “Modules” on the left at the bottom. A screenshot showing the Properties window for the Automation account.

You should see the following dialog. Click on “Browse Gallery”.

Type in “SQLServer” into the Search area and hit “Enter”.
Click on the “SqlServer” module.

You should see the following dialog. Click “Import”. A screenshot of the dialogue that appears, detailing the SQL Server PowerShell module.

You should see the following “Confirmation” dialog. Click “Ok”. A screenshot showing the confirmation dialogue, warning that the process may take several minutes to complete.

Next, we are going to repeat the above process for the “AzureRM.Profile” module.
Return to the “Browse Gallery” dialog and type “AzureRM.Profile” into the search area, then hit “Enter”.
A screenshot showing the Azure moduleRM.profile module in a list of other modules.

Click on “AzureRM.Profile”, you should see the following. Click on “Import”.

You should see the following “Confirmation” dialog. Check the “I agree to updates” box then click “OK”.
Once the module has completed importing, move to the next step.
A screenshot showing the confirmation dialogue, warning that the process may take several minutes to complete.

Again, we are going to repeat the above process for the “Azure.AnalysisServices” module.
Return to the “Browse Gallery” dialog and type “Azure.AnalysisServices” into the search area, then hit “Enter”.
A screenshot showing the Azure.AnalysisServices module in a list of other modules.

Click on “Azure.AnalysisServices”, then click on “Import”. A screenshot of the dialogue that appears, detailing the Azure.AnalysisServices PowerShell module.

You should see the following “Confirmation” dialog. Check the “I agree to updates” box (if required) then click “OK”.
Once the module has completed importing, move to the next step.
You should now have “Azure.AnalysisServices” and “SqlServer” modules installed.
A screenshot showing the confirmation dialogue, warning that the process may take several minutes to complete.

 


Step 7: Provide Automation with the credentials required to run the Analysis Services Refresh.


Return to the “Azure Automation Account” dialog.
Click on “Credentials” at the bottom left.
A screenshot showing the “Azure Automation Account”

You should now see the following dialog.
Click on “+ Add a credential”

Now create your credential as follows:
Name: Give the credential a sensible name.
Description: We recommend you fill this in.
User name: This is the App Id you should have in your Notepad.
Password: This is the Key you should have in your Notepad.
Confirm Password: This is the Key you should have in your notepad.
Take a copy of the Name you chose and put it into your notebook, this will be used later.
Click “Create”

 


Step 8: Create your Azure Automation “Runbook”.


Navigate to your new Azure Automation Account.
Click on “Runbooks”.
Click on “+ Add a runbook”.
Click on “Create a new runbook”.
Fill in the “Runbook” form as follows:
Name: Meaningful name.
Runbook type: PowerShell.
Description: It is recommended you describe the purpose of the runbook here.
Click “Create”.
A screenshot showing a new Runbook being set up.

You should now see the “Edit Runbook” dialog. If not, you may have to scroll to the right.

Place the following text into the code panel of the runbook.
Click “Save”.
Click “Publish” and confirm.

$AzureCred = Get-AutomationPSCredential -Name "{Your Credential Name}"
Add-AzureAnalysisServicesAccount -RolloutEnvironment '{AAS Instance Region}' -ServicePrincipal -Credential $AzureCred -TenantId "{Your Tenant ID}"
Invoke-ProcessASDatabase -server "{AAS Instance URL}" -DatabaseName "{Your AAS DB Name}" -RefreshType Full

e.g.

$AzureCred = Get-AutomationPSCredential -Name "ASRefreshCred"
Add-AzureAnalysisServicesAccount -RolloutEnvironment 'eastus.asazure.windows.net' -ServicePrincipal -Credential $AzureCred -TenantId "74f977bf-8631-41gf-91ab-4d7cd041db47"
Invoke-ProcessASDatabase -server "asazure://eastus.asazure.windows.net/demoas" -DatabaseName "MyASDatabase" -RefreshType Full

 


Step 9: Run your “Runbook” and next steps.


Navigate back to the “Runbook” dialog. You should see the following.
Run your “Runbook” (Click on “Start”) and confirm.
A screenshot showing a summary of the Runbook.

A new “Job” will be created and you should now see the following.
Navigate back to the “Runbook” dialog.
A screenshot of the new "Job" dialogue windown that has been created.

You should now see a “Queued” job.
Click refresh every few seconds to see the “Job” progress.
Once you see “Completed” click on the “Job”.
A screenshot of the dialogue window showing there is a job in the queue.

With any luck you will see the following “Completed Job” with no “Errors”.