A cloud with gears, how original!
Cloud

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

Lesezeit
5 ​​min

Hinweis:
Dieser Blogartikel ist älter als 5 Jahre – die genannten Inhalte sind eventuell überholt.

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

SSASModelRefresh Screenshot

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. SQL Server Connection Screenshot

  • 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 Registration Property Screenshots

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

Azure Automation Accounts Screenshot

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 RunbookRunblock Screenshot

  • 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:

Final Working Azure Portal Screenshot

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.

15 Kommentare

  1. This is great. Are all these steps need to be performed by the Admin of the subscription? If not, what must be done by Admin and what steps can be performed by Contributor?

    1. Hi Sireesha,
      you will need to be a subscription admin to create the App Registration. All other tasks should not require a subscription admin role.
      Sascha

      1. Thanks Sascha. So, two steps: Create App Registration and Grant Permissions, should be performed by the Admin. How about allowing me (Contributor) to create/use Azure Run As Account in my Automation? Should the Admin do something to allow that? Who all can create/use the „Run As Account“

  2. I’ve found this article incredible useful but still I have a doubt.
    Does the refresh include the tabular model data processing?
    E.g. Tabular model loads data from a SQL Server that gets constantly filled up with new rows and the model is not in direct query mode.

    1. Hi Cassandra,
      yes, this method includes refreshing model data. We run this after ETL execution to refresh the newest data within the tabular model.
      Sascha

  3. I was able to implement the complete steps. The Runbook ran successfully but the model didn’t refreshed. The changes in the data is not reflecting in the PowerBI Dashboard.
    Can you please help!

  4. This is Great and usefull, but I have some calrification, My datasource is on-premise Oracle database
    I don’t have any Analysis service engine in On-premise.
    With help of Visual studio (SSDT) we developed SSAs and deployed in Azure Analysis service, but data not refreshing, may i know how to proceed.

  5. Very nice article, We have the similar requirement, we are manually refreshing SSAS model Databases in Azure, we want to automate this refresh/sync, Hopefully these steps gets to what we want as it more rely on security and access details.

  6. Hello,
    can this process be used to backup a database in Azure Analysis Services as well, i.e.produce the .abf file?
    Also, is it recommended to use a managed identity instead of a service principal id for logging into Azure Analysis Services?
    Thanks!

    1. Hi Peter,
      Were you able to find the place where the Key is stored? The portal has been redesigned since the screen shot was made, and I’m unable to follow the instructions to find the Key.
      Has it been renamed to „Client secrets“ in „Certificates & secrets“?

Hat dir der Beitrag gefallen?

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert