How to Automate Processing your Azure Analysis Services Models
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:
- Use of a Service Principal Name (SPN) within Active Directory.
- The Azure Automation service.
- Use of the “Azure.AnalysisServices” and “SqlServer” Automation Modules.
- Use of a Credential for the SPN.
- Some simple PowerShell script run by the Azure Automation service.
- 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:
- Simplicity of design: We have tried to keep the approach as simple as possible.
- Ease of implementation: By avoiding the use of software development.
- Re-usability: Once implemented, it should be possible to replicate the “Run Books” for each Processing Task.
- Maintainability and centralisation of control: By placing all the “Run Books” into a single “Azure Automation Account”.
- Logging and Reporting: Azure Automation provides excellent logging and reporting capabilities.
- 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.
- 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/APII’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.
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.
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.
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”.
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.