Case
I have my tabular database hosted in Azure Analysis Services, but how do I process it without on-premises tools or services?
 |
| Process Azure Analysis Services |
Solution
One solution could be using some PowerShell code in Azure Automation Runbooks. With only a few lines of code you can process your database.1) Automation Account
First we need an Azure Automation Account to run the Runbook with our PowerShell code. If you don't have one or want to create a new one, then search for Automation under Monitoring + Management and give it a suitable name, then select your subscription, resource group and location. For this example I will choose West Europe since I'm from the Netherlands. Also make sure the Create Azure Run as account option is on Yes (we need it for step 3).
 |
| Azure Automation Account |
2) Credentials
Next step is to create Credentials to run this Runbook with. This works very similar to the Credentials in SQL Server Management Studio. Go to the Azure Automation Account and click on Credentials in the menu. Then click on Add New Credentials. You could just use your own Azure credentials, but the best options is to use a service account with a non-expiring password. Otherwise you need to change this regularly. Make sure this account has the appropriate rights to process the cube.
 |
| Create new credentials |
3) Connections
This step is for your information only and to understand the code. Under Connections you will find a default connection named 'AzureRunAsConnection' that contains information about the Azure environment, like the tendant id and the subscription id. To prevent hardcoded values we will retrieve these fields in the PowerShell code.
 |
| Azure Connections |
4) Variables
An other option to prevent hardcoded values in your PowerShell code it to use Variables. We will use this option to provide the Analysis Server Name and the Database Name to specify which database you want to process. Go to Variables and add a new string variable AnalysisServerName and add the name of the server that starts with asazure://westeurope.asazure.windows.net as value. Then repeat this with a string variable called DatabaseName for the database name of your tabular model. You can find the values on the Azure Analysis Services Overview page.
 |
| Add variables |
5) Modules
The Azure Analysis Services process methods (cmdlets) are in a separate PowerShell module called "SqlServer" which is not included by default. If you do not add this module you will get errors telling you that the method is not recognized. Note that this is a different module then for pausing/resume and upscale/downscale AAS.
Go to the Modules page and check whether you see the SqlServer module in the list. If not then use the 'Browse gallery' button to add it. Adding a module could take a few moments.
 |
| Add modules |
6) Runbooks
Now it is finally time to add a new Azure Runbook for the PowerShell code. Click on Runbooks and then add a new runbook (There are also five example runbooks of which AzureAutomationTutorialScript could be useful as an example). Give your new Runbook a suitable name and choose PowerShell as type.
 |
| Add Azure Runbook |
7) Edit Script
After clicking Create in the previous step the editor will we open. When editing an existing Runbook you need to click on the Edit button to edit the code. You can copy and paste the code below to your editor. Study the green comments to understand the code. Also make sure to compare the variable names in the code to the once created in step 4 and change them if necessary.
 |
| Edit the PowerShell code |
# PowerShell code # Connect to a connection to get TenantId and SubscriptionId $Connection = Get-AutomationConnection -Name "AzureRunAsConnection" $TenantId = $Connection.TenantId $SubscriptionId = $Connection.SubscriptionId # Get the service principal credentials connected to the automation account. $null = $SPCredential = Get-AutomationPSCredential -Name "SSISJoost" # Login to Azure ($null is to prevent output, since Out-Null doesn't work in Azure) Write-Output "Login to Azure using automation account 'SSISJoost'." $null = Login-AzureRmAccount -TenantId $TenantId -SubscriptionId $SubscriptionId -Credential $SPCredential # Select the correct subscription Write-Output "Selecting subscription '$($SubscriptionId)'." $null = Select-AzureRmSubscription -SubscriptionID $SubscriptionId # Get variable values $DatabaseName = Get-AutomationVariable -Name 'DatabaseName' $AnalysisServerName = Get-AutomationVariable -Name 'AnalysisServerName' # Show info before processing (for testing/logging purpose only) Write-Output "Processing $($DatabaseName) on $($AnalysisServerName)" #Process database $null = Invoke-ProcessASDatabase -databasename $DatabaseName -server $AnalysisServerName -RefreshType "Full" -Credential $SPCredential # Show done when finished (for testing/logging purpose only) Write-Output "Done"
Note 1: This is a very basic script. No error handling has been added. Check the AzureAutomationTutorialScript for an example. Finetune it for you own needs.
Note 2: Because Azure Automation doesn't support Out-Null I used an other trick with the $null =. However the Write-Outputs are for testing purposes only. Nobody sees them when they are scheduled.
7) Testing
You can use the Test Pane menu option in the editor to test your PowerShell scripts. When clicking on Run it will first Queue the script before Starting it. Running takes a couple of minutes.
 |
| Testing the script in the Test Pane |
After that use SSMS and login to your Azure Analysis Services and checkout the properties of your database. The Last Data Refresh should be very recent.
 |
| Login with SSMS to check the Last Data Refresh property |
8) Publish
When your script is ready, it is time to publish it. Above the editor click on the Publish button. Confirm overriding any previously published versions.
 |
| Publish the Runbook |
9) Schedule
And now that we have a working and published Azure Runbook, we need to schedule it. Click on Schedule to create a new schedule for your runbook. For the process cube script I created a schedule that runs every working day on 9:00PM (21:00) to process the database. Now you need to check the properties in SSMS to check whether the scheduled script works. It takes a few minutes to run, so don't worry too soon.
 |
| Add schedule |
Summary
In this post you saw how you can process your Azure Analysis Services database with only a few lines of easy code. The module you need is not included by default and it is a different module than the previous AAS PowerShell scripts from this blog.
You could borrow a few lines of code from the pause / resume script to check whether the server is online before processing it.
