Azure Analysis Services: How to Automate Your Tabular Model Refresh Using Azure Automation

This article shows how to refresh an Analysis Services Tabular Model which is hosted on Microsoft Azure regularly and automatically using Azure automation. A Tabular Model is an in-memory Analysis Services database which can be used by several business intelligence tools for fast and intuitive analysis of data. Tabular models are deployed to a server and subsequently regularly updated with data from various sources. Azure Analysis Services is a platform-as-a-service offering, which means that Microsoft does all the operations work in the background, eg. backups and updates. However it is still in the model administrators‘ responsibility to regularly process data. And of course, nobody wants to do this manually all the time. Thus we want to schedule and run processing automatically on Azure. Especially the security part in Azure is a little tricky …

We will do the configuration for regular processing in three steps: First, creating an App Registration in the Azure Portal, then granting permissions for App Registration on Azure Analysis Services and finally creating an Azure Automation Account. So let’s get started:

Create an App Registration

To create an App Registration in the Azure Portal, you need to obtain the following information: <ApplicationRegistrationName>, <ClientID>, <Key>.

Obtain <ApplicationRegistrationName>

First, go to App Registration in Azure Portal and click on New application registration. Before creating a new app, ensure you have admin permissions for the Azure Active Directory. For the app registration you have to define a Name (this is the <ApplicationRegistrationName>). Choose Application type Web app/API.

Obtain <ClientID>

The Sign-on URL can be any URL as it doesn’t matter for our purpose.  After creating the Application, make sure to copy and note its ApplicationID (this is <ClientID>) as you will need it later.

Obtain <Key>

To obtain the key, go to Settings -> Keys and generate a new key. Ensure you copy and note its value. Now, you should have the proper values for <ApplicationRegistrationName>, <ClientID> and <Key>.

Grant permissions for App Registration on Azure Analysis Services

After creating the Application Registration, it is now time to grant permissions on Azure Analysis Services. This has to be done via the SQL Server Management Studio (SSMS) as it is not working in the Azure Portal as of today. Ensure you use at least SSMS version 17.3.

• Connect to your SSAS Instance via SSMS.
• To create a connection with the Server choose Active Directory – Universal with MFA support as authentication type.

If you are not able to see this option, you probably don’t use the right SSMS version (update!). You are now redirected to the usual Azure authentication. Make sure your account is declared as an admin for Azure Analysis Service.

In the next step, open the server properties in SSMS. Then, choose the security settings and click on add. Add your your registered application using he search function. If you cannot find your registered application, you will have to add the account manually in the following format:

app:<ClientID>@<tenantid>

example:

app:3b4d0ee9-fcf2-41ae-xxxx-xxxxxxxxx@539c9603-xxxx-xxxx-xxxx-xxxxxxxxx

For further information regarding Azure Analysis Services security, please refer to the official Microsoft documentation.

You can find the tenantID in the Azure portal: Go to Azure Active Directory -> Properties -> Directory ID. The Directory ID is your tenantID.

Finally, confirm and close everything.

Schedule refresh using Azure Automation

Create Automation Account

To create a new Azure Automation Account go to Automation Accounts in the Azure Portal. Add a new Azure Automation Account or use an existing one.

Add Modules

With your Automation Account selected go to Modules gallery and search for the following modules:

• Azure.AnalysisServices
• SqlServer
• AzureRM.profile

Add these modules to your Automation Account.

Create Credentials

After adding the modules, you have to create new Credentials:

• Go to Credentials and Add a credential
• Define any name, for example SSASModelRefresh
• As the User name enter your App Registration <ClientID>
• As the Password enter your App Registration <Key>

Create Runbook

Now, we have to create a new Runbook: 

• In your Automation Account, go to Runbooks and click on Add a runbook
• Create a new runbook and choose PowerShell as Runbook Type
• Open the Runbook and click on Edit

Use the following code and fill in the proper values for <TenantID>, <CredentialName>, <SSASServerName>, <SSASDBName>, RolloutEnvironment> and <CredentialName>:

Afterwards, click on Test pane and Start to execute your script. If everything is working the Azure Portal should look like this:

Your script is running as long as your models needs to be refreshed. You can now publish your Runbook and add a schedule via Schedules.