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.



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”.


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.

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”.

You should now see the following dialog. Type in “Automation”, then click on the “Automation” service.

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.


Step 6: Setup Azure Automation with the required Modules.



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.

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”.

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 new “Job” will be created and you should now see the following.
Navigate back to the “Runbook” dialog.

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”.

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



Source link


Computers and Software Buyers Guide

Compare Computers and Laptops

Mobile Phones Buyers Guide

Compare Mobile Phones

Game Console Buying Guide

Compare Game Consoles


Tech Shop Offers


Shop Categories