AAS Model Refresh | Azure Automation or PowerShell (Azure/Windows)

Azure Automation To Do AAS Model Refresh: In this article we will see how to do “FULL” process for Azure Analysis Services (AAS). The first option will be with PowerShell (Azure/Windows) and the Second will be using Azure Automation.


#1) Create Service Principal (SP)?

Create Service Principal using App Registration option from Azure portal by following these below steps.

Step 1: Go to azure portal and search for “App Registration” and give the name to your App (SP) and click on register as shown in below image.

Azure Automation

Step 2: Get these below details such as AppID and TenentID from this below image and then go to “Certificates & Secrets” to get the Value (Password).

Azure Automation

Step 3: Click on New client secret and give description (name) to it and hit add.

Azure Automation

Step 4: Now, we have Value, and it will be visible ONLY once and this will act as your password so copy and paste it somewhere.

Azure Automation

Note: I got these below details from above mentioned 4-steps and if you need more information please visit this doc: Create a Microsoft Entra app and service principal in the portal – Microsoft identity platform | Microsoft Learn

Service Principal (Details)

App ID (Client ID): 5a13e5bd-b6bf-4220-a1e0-e1843d57c445

Tenent ID: 72f988hg-76g7-41ab-97ab-2d7cd077db47

Secret Value: aQs8Q~JcTHYrtRDvcs-ON.pt~ythfgdRTGB6Zc4p (It will be visible only one time)

#2) Assign SP As AAS Admin?

Once You have created (SP), now we have made that SP as Admin on AAS to perform any kind of refresh. Now, here we have 2-method to add AAS Admins (SSMS & AAS Admins)

Note: The Service Principal can’t authenticate by using AppID or TenentID, so we have to give our username in this below format. More details, follow this doc: https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-addservprinc-admins

Follow this format to assign SP as AAS Admin: app:{service-principal-client-id}@{azure-ad-tenant-id}

UserID: app:5a13e5bd-b6bf-4220-a1e0-e1843d57c445@727363bf-862-41af-91hy-2d7hg022db47

Password: aQs8Q~JhghgJgC-ON.pt~rWHGVfJkijKSM6Zc4p (SP Secret Value)

Method 1: Go to SSMS and you can add this above UserId to make SP as server Admin.

Azure Automation Runbook

Method 2: Go to Azure Analysis Services (AAS) admin portal and you can add the same here as well!

Azure Automation Runbook

#3) Gateway (On-Prem & Azure)

Note: If you’re doing full refresh/process to the AAS models by using PowerShell script then you have to configure on-prem gateway. Follow this doc to configure: https://kingfishertechtips.in/on-prem-sql-db-tabular-model-to-azure-aas/

Also if you to not installed AAS client libraries you can check this: https://learn.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions

Refresh Using PowerShell (Azure/Windows)?

AAS Model Refresh Using PowerShell ISE (Use Tenant_ID, Secret_Value, AppId (ClientID) from above prerequisites to this below script). Please use PowerShell as administrator and run this below script.

PowerShell Script

$PWord = ConvertTo-SecureString -String “aQs8Q~JhghgJgC-ON.pt~rWHGVfJkijKSM6Zc4p” -AsPlainText -Force

$Credential = New-Object -TypeName “System.Management.Automation.PSCredential” -ArgumentList “5a13e5bd-b6bf-4220-a1e0-e1843d57c445“, $PWord

Connect-AzAccount -Credential $Credential -ServicePrincipal -TenantId “727363bf-862-41af-91hy-2d7hg022db47

Invoke-ProcessASDatabase -Server “asazure://westcentralus.asazure.windows.net/vickeyaas:rw” -DatabaseName “Tab_by_SQL_to_AAS” -RefreshType “Full”

Azure Automation

Refresh Using Azure Automation?

To do automation of any PowerShell script we have create automation account and then import modules related to AAS model process. So first we will create Automation account and then I will show you how to import the required module to your automation account.

Below we have 4 – Steps to follow and automate the process one-by-one?

$1) Azure Automation Account?

Step 1: Create Azure Automation Account first, go to azure portal and search for “Automation”.

Azure Automation

Step 2: Select the resource group or create new one and then give a name with region specified and click on “Review and Create“.

Azure Automation Runbook

$2) Pass Service Credential in Azure Automation?

Step 1: Now from the left pan click on “credential” and then click on “Add a credentials” and give the name and under Username provide “AppID (ClientID)” and in the Password give “Secret Value” and then click on Create.

Azure Automation Runbook

Note: If you are using same script as of mine then in the script file, I am passing one “ServicePrincipal” as one of the parameters, so I am giving the exact name to call my Service Principal which we created in PREREQUISTS. If you don’t want use service principal, then to get the script which we did for PowerShell refresh, and you can directly run it.

$3) Importing Module?

Step 1: Browse this below document and follow the steps listed below to import the modules to your Automation Account. Browse this PowerShell Gallery | Home and search for “sqlserver” module as shown.

Azure Automation Runbook

Step 2: Click on Azure Automation and then Deploy it. Then it will take you to new page and ask you to sign-in with your azure credentials.

Azure Automation Runbook

Step 3: Now, we have to select the automation account you want to deploy this module and hence proceed with the wizard setup.

Azure Automation Runbook

$4) Runbooks?

Step 1:  Go back to your azure automation account and click on “Runbooks” and then click on “Create a runbook” from the top menu to automate the process.

Azure Automation Runbook

Step 2: I will import my custom script into azure runbook, I have provided the script file in the this download link! You can import from that here and give the required details and click on Import from bottom of the screen.

Azure Automation Runbook

Note: The “sqlserver” module which we have imported supports only PowerShell 5.1, so I am using Runtime Version as 5.1 as shown below.

Step 3: Once deployed you will be able to see this Edit option, click on “Edit in portal” and proceed further.

Azure Automation Runbook

Step 4: Don’t change anything if you’re using the same script as mine. Just click on “Publish” and your Runbook is ready to do the stuff.

Azure Automation

Step 5: Once published you will get this “Start” button option, click on that to start the automation process.

Azure Automation Runbook

Step 6: In the next step you will be asked to provide the Database, Server name and Type of process, see below image and perform all the steps.

Azure Automation

Step 7: If everything is working fine in runbook and all things are correctly setup then the runbook will execute and then the process will complete as shown below. My ASS is affected means there is successful change in my AAS models.

Azure Automation

Other MSBI Posts: TSG Archives » Kingfisher Tech Tips

Vickey Rajpoot
Vickey Rajpoot

Hello there! I'm dedicated Microsoft Data & AI Engineer at LTIMindtree, where I thrive technology into actionable insights. Dive into my world by visiting my YouTube channel & Webiste, "Kingfisher Tech Tips".

Articles: 74

Leave a Reply

Your email address will not be published. Required fields are marked *