Processing Azure Analysis Services Using Azure Automation

19 November, 2017 (15:12) | Analysis Services, Automation, Azure, PowerShell, SSAS | By: Mark V

Recently, I was working with a customer who was deploying a Tabular model to Azure Analysis Services. They were wondering how they would automate the processing of that model. There are certainly a few ways to do that. You can do it with C# or JavaScript via Azure Functions, as is described in this post by Orsolya Gal. Since my background is not in C#, and the customer didn’t have that either, I decided to keep that option on a shelf for later.
 
I have done some PowerShell and I have worked with Azure Automation, so that seemed like a better place to start for me. Two posts in particular were helpful here:
 
Schedule Process Azure Analysis Services Database by the folks at microsoft-bitools.blogspot.com
 
Automation of Azure Analysis Services with Service Principals and PowerShell by Christian Wade, Senior Program Manager on the Analysis Services product team
 
My own solution ended up combining features from both of the above walk-throughs. My goal with this post is not only to walk through my own solution, but to also share, at each stage, why I made the choices I made. Note that I am still very much a student of PowerShell, so experts in that area will likely know of better ways to accomplish what I did. If you are a PowerShell guru and see where my scripts could be improved upon, I would love for you to share that in comments so I (and anyone else reading this) can learn. Mmmmm… learning…
 
Note: If you put all of this PowerShell into a single script file, you can declare each variable a single time and use it as many times as you need. For example, you will be entering a Password and storing it in a secure credential. You will need that in more than one place for this solution. If you have separate scripts for each location, you will have to enter that password more than once and increase the chance of having them not match, which will cause errors. I will proceed as though we are putting all of the PowerShell in a single script file to avoid having to repeat declarations and value assignments for PowerShell variables involved.
 

Creating Azure Active Directory Service Principal Using PowerShell

As Christian points out in his post above, Service Principals are essentially the Azure implementation of the Service Accounts we have on-premises. They allow applications and services to authenticate and access resources without manual intervention. While you can create service principals in the Azure Portal, I chose to go with PowerShell so that I could save the script and easily copy it for use again and again. In order for this to work, it is necessary to install the Azure PowerShell modules on the machine from which you want to run the scripts. In my case, it is my Surface Book. I, personally, use the Windows PowerShell ISE (Integrate Scripting Environment) that is native to Windows 10. Once you have that open, you can use the Login-AzureRmAccount cmdlet to log into Azure so you interact with the objects, etc, in the Azure subscriptions you have access to.
 
Once you log in, the output will include your Tenant ID. Capture that value for later.
 
image
 
From here, we will just assume you have done that.
 
$subscriptionID = "<your subscription ID GUID>"

$rootNameValue = "mvazureasdemo"

$servicePrincipalName = "$($rootNameValue)serviceprincipal"
$servicePrincipalPassword = Read-Host `
-Prompt "Enter Password to use for Service Principal" `
-AsSecureString

Write-Output "Selecting Azure Subscription..."
Select-AzureRmSubscription -Subscriptionid $subscriptionID

I started by creating a few variables and assigning them values. I will end up specifically selecting which azure subscription I want to work in. By specifying this, I can make sure I am creating and working with objects in the right place. To find your subscription ID, you can follow these steps in the Azure Portal.
 
I use the $rootNameValue as a prefix for all objects created as part of a solution. So, my server names, Azure Automation Assets (we’ll get to these in a bit), etc, all will start with this value. It helps me keep clear what goes with what. The $servicePrincipalName then derives its value by appended “serviceprincipal” onto the root name value.
 
The expression for the $servicePrincipalPassword will generate a box for me to securely enter in the password I want my Service Principal to have. This method allows me to make sure I don’t have something as sensitive as a password sitting in my code anywhere.
 
That last line above selects the subscription whose ID I put in the $subscriptionID variable.
 
# Get the list of service principals that have the value in 
# $servicePrincipalName as part of their names
$sp=Get-AzureRmADServicePrincipal `
-SearchString $servicePrincipalName

# Filter the list of just the ONE service principal with a DisplayName
# exactly matching $servicePrincipalName
$mysp = $sp | Where{$_.DisplayName -eq $servicePrincipalName}

# Isolate the ApplicationID of that service principal
$appSPN = $mysp.ApplicationId

This next section checks to see if a Service Principal already exists with the name I want to use. It is a little bit involved since I need to filter on a search string and not a specific name (the Get-AzureRmADServicePrincipal does not currently support filtering by DisplayName directly). So, I isolate all existing (if any) service principals that have my $servicePrincipalName value IN their names. For example, searching for “Foo” would return both “Fool” and “Foot.” Then I filter that list for the one that has a DisplayName property that matches my $servicePrincipalName value exactly. I then grab the ApplicationID property of THAT principal for use later.
 
# Create a service principal if it does not exist
Write-Output "Creating Service Principal $servicePrincipalName..."

try
{
If(!($appSPN))
{
New-AzureRmADServicePrincipal `
-DisplayName $servicePrincipalName `
-Password $servicePrincipalPassword

# Sleep for 20 seconds to make sure principal is propagated in Azure AD

Sleep 20

}
Else
{
Write-Output "Service Principal $servicePrincipalName already exists."
}
}
catch
{
Write-Error -Message $_.Exception
throw $_.Exception
}

In the code section above, I first check to see if my $appSPN variable has a value. If the Service Principal I want to create already exists, then its ApplicationID property value will be in that variable. If that variable is empty, I go ahead and create Service Principal using the value in my $servicePrincipalName variable for the DisplayName and the password in my $servicePrincipalPassword variable for the password. Service Principals can be created with certificates as well. I chose to use the password for now for simplicity. If the Service Principal already exists, I skip the creation and just return a message indicate it already exists. This means I can run my script multiple times without causing issues or generating errors from trying to create a object that already exists.
 
If the creation step DOES execute, my script waits 20 seconds to make sure that new Service Principal is alive and well in Azure Active Directory before taking my next action below.
 
# Assigning Service Principal $servicePrincipalName to Contributor role
Write-Output "Assigning Service Principal $servicePrincipalName to Contributor role..."

try
{
If(!(Get-AzureRmRoleAssignment `
-RoleDefinitionName Contributor `
-ServicePrincipalName $appSPN `
-ErrorAction SilentlyContinue))
{
New-AzureRmRoleAssignment `
-RoleDefinitionName Contributor `
-ServicePrincipalName $appSPN
}
Else
{
Write-Output "Service Principal $servicePrincipalName already `
assigned to Contributor role."

}
}
catch
{
Write-Error -Message $_.Exception
throw $_.Exception
}

 
In the section of code above, I check to see if my Service Principal has already been assigned to the Contributor role in my Azure Subscription. If so, I just return a message to that effect. If not, I add it. Notice again that this prevents my script from trying create something that already exists.
 
Once creation of the Service Principal completes, the output will provide you with the Application ID GUID value. Take note of that as we will need it shortly.
 
image
 

Assign Service Principal to Administrator Role on Azure Analysis Services Server

The newly created service principal needs to be added to the Administrators role on the server via the Security tab in Server Properties. This is to provide it with the necessary rights to perform Processing operations. To add the principal, use the account picker or do what I did, create the string in a text editor and paste it in:
 
app:<Application ID>@<Azure tenant ID>

image
 

Azure Automation Modules

If you don’t already have an Azure Automation account, you can follow the steps here to create one. PowerShell cmdlets are grouped together in Modules that share certain context. For example, Modules that are intended for management of SQL Servers are grouped together in the SqlServer module. Whether it be on your computer for use in the PowerShell ISE or in Azure Automation, the use of cmdlets is only possible when the Modules that contain those cmdlets are installed. You can read more about installing Modules here. For the purposes of processing Azure Analysis Services models with Azure Automation, it will be necessary to install the Azure.AnalysisServices and SqlServer modules in your Automation account. Note that each of those pages includes a Deploy to Azure Automation button.

Azure Automation Basics

Azure Automation allows you to execute scripts against your Azure environment. The components that perform the work, that contain the scripts, are called Runbooks. There are a few different scripting languages supported (including Python, which is now in Preview). For this process, we will use PowerShell Runbooks.

Often, Runbooks will have various parameters or values that are needed at runtime. For example, if you have a Runbook that boots up a virtual machine, you will need to tell it WHICH virtual machine to start, etc. These values can be passed into the Runbooks at runtime as Parameters, OR you can store these values in Assets like Variables and Credentials. For this process, we will be using one Variable Asset and one Credential Asset. Assets allow you to store values once but make those values available across all of your Runbooks, etc. Super cool.

Azure Automation Variable Asset

One value we will need to pass to our Runbook for processing out Analysis Services model is the Tenant ID of our Azure Subscription. This is a GUID that you are welcome to type/paste in every time you want to use it. Or you can hard-code it into your Runbooks that need it. But there is a far better way. You can store it in a Variable Asset. The instructions there show you how to create the variable via the Azure Portal. Since we are already doing PowerShell, let’s stick with it.

# Create function to create variable assets
function CreateVariableAsset
{

param
(
[string] $variableName
, [string] $variableValue
)

Write-Host "Creating Variable Asset $variableName..."

try{
If(!(Get-AzureRmAutomationVariable `
-AutomationAccountName $automationAccountName `
-ResourceGroupName $automationResourceGroup `
-Name $variableName `
-ErrorAction SilentlyContinue))
{
New-AzureRmAutomationVariable `
-AutomationAccountName $automationAccountName `
-ResourceGroupName $automationResourceGroup `
-Name $variableName `
-Value $variableValue `
-Encrypted $false `
-ErrorAction Stop

Write-Host "$variableName created successfully. `r`n"

}
Else
{
Write-Host "Variable Asset $variableName already exists. `r`n"
}
}
catch
{
Write-Error -Message $_.Exception
throw $_.Exception
}

}

The PowerShell above creates a Function that includes the necessary script to create an Azure Automation Variable Asset. Creating functions can be super helpful when you need to run the same block of code several times, with only minor changes to parameter values, etc. I created this function when building out a solution that involve a couple dozen Variables. Rather than repeat this block of code a couple dozen times, I created it once and then called it for each Variable I needed to create. This makes for much cleaner and easier to manage code.

$automationAccountName = "AutomationB"
$automationResourceGroup = "AutomationBRG"
$variableTenantIDName = "$($rootNameValue)tenantid"
$varaibleTenantIDValue = "<your Tenant ID GUID>"

First, we need a create a few more PowerShell variables. The first two specify the name and resource group of the Azure Automation account we are using. When creating Azure Automation components via PowerShell, you must specify which Automation account will house the objects, as you can have more than one Automation account.

The next two variables provide the Name and Value of our Variable Asset, respectively.

CreateVariableAsset `
-variableName $variableTenantIDName `
-variableValue $variableTenantIDValue

The block above calls our function to create the Variable Asset, if it does not already exist.

image

Hazzah!

Azure Automation Credential Asset

Since are going to automate use of a Service Principal with a password, we want to store that securely so that our Runbook can get to it without additional intervention. To do that, we use a Credential Asset. That link provides instructions on creating a Credential Asset via the Azure Portal.

# Create function to create credential assets
function CreateCredentialAsset
{

param
(
[string] $credentialName
, [PSCredential] $credentialValue
)

Write-Host "Creating Credential Asset $credentialName..."

try{
If(!(Get-AzureRmAutomationCredential `
-AutomationAccountName $automationAccountName `
-ResourceGroupName $automationResourceGroup `
-Name $credentialName `
-ErrorAction SilentlyContinue))
{
New-AzureRmAutomationCredential `
-AutomationAccountName $automationAccountName `
-ResourceGroupName $automationResourceGroup `
-Name $credentialName `
-Value $credentialValue `
-ErrorAction Stop

Write-Host "$credentialName created successfully. `r`n"

}
Else
{
Write-Host "Credential Asset $credentialName already exists. `r`n"
}
}
catch
{
Write-Error -Message $_.Exception
throw $_.Exception
}

}

I once again create a Function for the Asset creation.

$credentialServicePrincipalName = "$($rootNameValue)serviceprincipal"
$credentialServicePrincipalUserNameValue = "<your Application ID GUID>"

We need to create two more PowerShell variables; one for the Credential Asset name, another for the Credential Asset User Name value. We will need a Password but we already have that variable from when we created the Service Principal above. We can reuse that same variable so we don’t have to enter in a password again.

# Create a PSCredential object using the User Name and Password
$credentialServicePrincipalValue = `
New-Object System.Management.Automation.PSCredential`
(
$credentialServicePrincipalUserNameValue `
, $servicePrincipalPassword
)


# Call function to create credential assets
CreateCredentialAsset `
-credentialName $credentialServicePrincipalName `
-credentialValue $credentialServicePrincipalValue

We have to do one extra step here. We start by taking our User Name (Application ID) and Password and using them to create a PSCredential object. Then we call our function to create our Credential Asset.

image

More Hazzah!

Azure Automation Runbook

As I stated before, Runbooks contain the scripts that perform the work in Azure Automation. You can see how to create a Runbook in the Azure Portal. We will create out Runbook with PowerShell. However, we will not be using PowerShell to actually put the script inside the Runbook. You can do that with PowerShell by importing a saved .ps1 (in the case of a PowerShell runbook) that contains the PowerShell the Runbook will contain. I decided that was more complex than I wanted to get here. Besides, the steps after that take place in the Portal anyway. So, we will create the Runbook using PowerShell and then go to the Azure Portal to edit it and paste in our script.

$runbookName = "$($rootNameValue)processasdatabase"

We start by declaring a PowerShell variable to hold the name for our Runbook.

# Create empty Runbook
Write-Host "Creating Runbook $runbookName..."

try{
If(!(Get-AzureRmAutomationRunbook `
-AutomationAccountName $automationAccountName `
-ResourceGroupName $automationResourceGroup `
-Name $runbookName `
-ErrorAction SilentlyContinue))
{
New-AzureRmAutomationRunbook `
-AutomationAccountName $automationAccountName `
-ResourceGroupName $automationResourceGroup `
-Name $runbookName `
-Type PowerShell `
-ErrorAction Stop

Write-Host "Runbook $runbookName imported successfully. `r`n"

}
Else
{
Write-Host "Runbook $runbookName already exists. `r`n"
}
}
catch
{
Write-Error -Message $_.Exception
throw $_.Exception
}

Here we create the Runbook, using our shiny new $runbookName PowerShell variable along with a couple we declared previously.

image

Hazzah!

When to go to the Runbooks blade of our Automation account in the Azure Portal, we see our shiny new Runbook. It still has that new Runbook smell.

image

Clicking on the name of the Runbook will open the Runbook blade. Here we click the Edit button.

image

Then we past our PowerShell script into the window.

# Get the values stored in the Assets
$TenantId = Get-AutomationVariable -Name "mvazureasdemotenantid"
$Credential = Get-AutomationPSCredential -Name "mvazureasdemoserviceprincipal"

# Log in to Azure Analysis Services using the Azure AD Service Principal
Add-AzureAnalysisServicesAccount `
-Credential $Credential `
-ServicePrincipal `
-TenantId $TenantId `
-RolloutEnvironment "southcentralus.asazure.windows.net"

# Perform a Process Full on the Azure Analysis Services database
Invoke-ProcessASDatabase `
-Server "asazure://southcentralus.asazure.windows.net/mvazureasdemoserver" `
-DatabaseName "AzureASDemo" `
-RefreshType "Full"

Once we do, it should look like the figure below.

image

There are a few things to note here.

1. The names in the top section of the runbook must match the names you used when creating the Assets.

2. I am using the Add-AzureAnalysisServicesAccount cmdlet instead of the Login-AzureAsAccount cmdlet Christian used. I had trouble making that one work and more trouble trying to find documentation on it. So, I punted and used a different one. You can find documentation on the Add-AzureAnalysisServicesAccount cmdlet here. This is used to pass our Service Principal into the Azure Analysis Services server to connect to it.

3. Notice that my Azure Analysis Services server name matches my naming convention for this entire solution. Yeah. I know. I’m pretty cool. Yours does not have to match. Mine only does since I created it solely for this demo/post. This step performs the processing operation on the database. In this example, I am performing a Process Full on the entire database. You have many other options as well. You can find more information on the Invoke-ProcessASDatabase cmdlet here. On that page, the left sidebar includes other cmdlets related to processing Analysis Services objects.

When you have finished reveling in the awesomeness of this Runbook, click on the Test Pane button, shown by the arrow in the image above.

image

If our Runbook had actual parameters, they would show up in the Parameters section and we would be able to enter values for them. Since our Runbook is configured to just go grab what it needs from Assets, all we have to do is click Start.

image

Our Test run will go into queue to await its turn to run. Since we do not each have our own dedicated capacity in Azure Automation, each job needs to wait for a worker to pick it up to execute it. I have not seen the wait exceed ten seconds, personally. But not that starting a Runbook is not an instantaneous process. You can read more about Runbook execution in Azure Automation here.

image

Once a worker has picked it up, the status will change to Running.

image

Victory! Note that although this was a Test Run in the context of Azure Automation, this ACTUALLY PEROFMS THE ACTIONS in the Runbook. It does not “pretend” to call the Runbook. It executes it. In full.

You can check the status of the database via its Properties in SQL Server Management Studio.

image

The Last Data Refresh status should closely match the time you ran the Test Run.

Close the Test Pane via the X in the upper right-hand corner to return to the Edit pane.

image

Click Publish. This will fully operationalize your Runbook, replacing the version of the Runbook that exists if it was published previously. Once it is published, we can start it manually or give it a Schedule.

image

Note: If you just want to look at the contents of the Runbook, use the View button. Entering Edit mode on a Published Runbook will change its status to In Edit, and it will not execute. If you do enter Edit mode by accident, and don’t want to change anything, choose the Revert to Published button to exit Edit mode and return the Runbook to the Published state.

Click the Schedule button.

image

You can modify Parameter settings, etc, from the bottom option. In our case here, our Runbook has no parameters, so we can click to Link a schedule to our Runbook.

image

This will launch the Schedule blade. If you already have existing Schedules in your Automation Account, like “Every Monday at 6 AM” or something, those will show up here and you can choose to tack your Runbook execution onto one of those. I will just create a new one.

image

Give you schedule a meaningful name, ideally, one more meaningful than what I chose above. Note that you have to make sure the schedule is set to kick off at least 5 minutes into the future. Also, for this purpose, I am just choosing to have this execute Once.

Now we wait. Or, rather I wait, I guess. Anyway, back in a sec. Talk amongst yourselves. Here’s a topic: If Wile E Coyote could buy all that ACME stuff to try to catch the Roadrunner, why couldn’t he just buy some food?

And we’re back.

image

We can check the status of our scheduled Runbook execution by clicking on the Jobs button in the Runbook blade.

image

We can see it completed successfully. Hazzah! If we click on it, we can view more details.

image

The Job Status is Completed. There are not Errors or Warnings. We can click on the Output to see the actual job Output, just like in the Test Pane.

image

Hazzah!

image

And we can see in the Database properties that the Last Data Refresh matches the time of our scheduled Runbook execution.

Hazzah! Hazzah!

Wrapping Up

There you have it. We just automated the processing of an Azure Analysis Services database using an Azure Active Directory Service Principal, PowerShell, and Azure Automation. Although this post is a bit long, the actual steps to make this happen aren’t very difficult. Hopefully, this will help walk you through trying this yourself.

Comments

Comment from surendra
Time November 29, 2017 at 11:44 am

Hi Mark

i am getting the error while iam testing the run book. i have fallowed all the steps . i have created another runbook with my credtentials instead of service principal it is working fine . Please help me if i am missing some thing

provided.
Trace ID: 59b0cd40-3c36-4c25-ac3c-39c7316a0e00
Correlation ID: 7f4866d9-8999-4e91-b3e7-c7d98759adc8
Timestamp: 2017-11-29 17:41:11Z
At line:6 char:1
+ Add-AzureAnalysisServicesAccount `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [Add-AzureAnalysisServicesAccount], AdalServiceException
+ FullyQualifiedErrorId : Microsoft.Azure.Commands.AnalysisServices.Dataplane.AddAzureASAccountCommand

Invoke-ProcessASDatabase : Authentication failed: User ID and Password are required when user interface is not
available.
At line:13 char:1
+ Invoke-ProcessASDatabase `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Invoke-ProcessASDatabase], ArgumentException
+ FullyQualifiedErrorId : System.ArgumentException,Microsoft.AnalysisServices.PowerShell.Cmdlets.ProcessASDatabase

Comment from surendra
Time November 29, 2017 at 12:07 pm

Hi Mark
It is working fine , we have some issue in secret , we have resolved it , thanks for the post .

Thanks

Surendra

Comment from Mark V
Time November 29, 2017 at 12:33 pm

Awesome, Surendra. Glad you got it figured out.

Write a comment