Automated Refresh of Azure Analysis Service

Azure Analysis Service based Tabular Model Automated Refresh Using Azure Automation Account

1 : Create Azure Automation Account

Azure Automation Accounts
Provide details to your Automation Account

2: Create a runbook — PowerShell based runbook

3: import and deploy below modules to automation account

SqlServer & Azure analysis service

https://www.powershellgallery.com/packages/SqlServer/21.1.18230

https://www.powershellgallery.com/packages/Azure.AnalysisServices/0.5.4

Import and Deploy Module by just one click

4: Create Credential for automation account:

In your automation account go to credentials :

EnterName: Anynameofyourchoicel

Username : 7ba&****-****-***-898f-e************

Password: EKd9U************?2tH1_************

5: Provide access to the tenant on your Analysis Service.

Open Analysis service using SQL Server Management Studio

Right click on analysis service and select properties

Go to the security

Do a manual entry for tenant ID → app:<tenant_ID>

6: Edit the run book and enter below code:

$AzureCred = Get-AutomationPSCredential -Name “Anynameofyourchoicel”

Add-AzureAnalysisServicesAccount -RolloutEnvironment ‘<region>.asazure.windows.net’ -ServicePrincipal -Credential $AzureCred -TenantId “<tenantID of your azure active directory>”

Invoke-ProcessASDatabase -server “asazure://<region>.asazure.windows.net/<analyservicename>” -DatabaseName “<Cube Name>” -RefreshType Full

7: In the Edit mode of runbook, click on Test Pane and execute the above script

8: You should see the message like below “completed “

Successful Execution of Runbook

9: At last you can link a schedule to execute this script at recurrent interval.. Hourly, Daily, Every 4 Hours etc

10: You’re Done… Thanks for your time.

Lead Software Developer → JAVA, Scala, Python → Data Visualization(Power BI) → Azure AI Engineer