Automated Refresh of Azure Analysis Service
Azure Analysis Service based Tabular Model Automated Refresh Using Azure Automation Account
1 : Create Azure 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
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 “
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.
