Share via

Automating Azure Analysis Service Processing using Azure Automation Account

  • Article

 

Analysis Services has been progressing day-by-day with new exciting features and there is an ask from the users to automate the Azure Analysis Services Processing. There are few ways which we can automate the processing.

  • Using the conventional way what we have for the On-Prem. Using SQL Agent Job/ Using AMO Objects/ Using PowerShell.

Since we are dealing with Azure, we need to think about automation which wouldn’t be dependent on the On-Prem VM to execute a PowerShell script or any On-Prem SQL Server Instance to run the SQL Server Agent Job.

Also, there are scenarios where we need to deal with the 2-factor authentication where we either get prompted for the phone authentication or need to re-enter the credential while connecting to the Azure Analysis Services.  Now think about a scenario where we are scheduling the job that would run un -attended, there might be a possibility that it prompts for the authentication if the AD token expires while scheduling it with on-prem schedulers. There is different way to tackle that, however we will not discuss this here.

We have an azure automation functionality where we can schedule the PowerShell Script to automate the functionality with the Azure Analysis Services.  Here are the steps we need to follow –

 

Objective: We will create partition for a fact table and process it: TabDemo in my Azure Analysis Services Instance:  asazure://southeastasia.asazure.windows.net/azureasdemo

 

Steps:

1. Creating Azure Automation Account and adding the SQL PowerShell Module

a. Login to https://portal.azure.com

b. Search for “Automation Account

c. Create an automation account.

d. Now you would be able to see the automation account which you just created. The name is “samasutomationaccount”

e. You need to Import the SQLServer PowerShell Modules first.

f. Click on the “Browse Gallery” and search with “SQLServer”.

g. Click on the Import and then OK button at right button corner of your screen.

h. You would be able to see the SQLServer module has been imported in your automation account gallery.

SQLServer Module:

You can download load the SQL Server Module from the link if you want to use it in the on prem: https://www.powershellgallery.com/packages/SqlServer/21.0.17178

Here are the commands you can use for the Analysis Services:

/en-us/sql/analysis-services/powershell/analysis-services-powershell-reference

Add-RoleMember cmdlet Add a member to a database role. Add
Backup-ASDatabase cmdlet Backup an Analysis Services database. Database.Backup
Invoke-ASCmd cmdlet Execute a query or script in XMLA or TSML (JSON) format. Execute
Invoke-ProcessASDatabase Process a database. Process
Invoke-ProcessCube cmdlet Process a cube. Process
Invoke-ProcessDimension cmdlet Process a dimension. Process
Invoke-ProcessPartition cmdlet Process a partition. Process
Invoke-ProcessTable cmdlet Process a table in a Tabular model, compatibility model 1200 or higher. Process
Merge-Partition cmdlet Merge a partition. Merge
New-RestoreFolder cmdlet Create a folder to contain a database backup. RestoreFolder
New-RestoreLocation cmdlet Specify one or more remote servers on which to restore the database. RestoreLocation
Remove-RoleMember cmdlet Remove a member from a database role. Remove
Restore-ASDatabase cmdlet Restore a database on a server instance. Restore

2. Creating Credential:

a. We need to define a credential here which we would be using in the Powershell code later.

b. You need to specify the credential which has Admin access in Azure AS Instacne and then click on Create. The name of the credential I created is “SamCred”

 

3. Creating Runbook:

a. Select the Runbook

b. Click on the Add run book

c. Enter the below details:

Choose Powershell as Runbook Type and then click on CREATE

4. Create the Powershell Cmdlet script to automate partition creation and processing.:

a. The main objective code is to automate the creation of the partition for the current month and delete 36th month older partition.

Go to the Runbook we created earlier. Click on Edit

b. Enter the Below Power shell Script:

##Getting the credential which we stored earlier.
$cred = Get-AutomationPSCredential -Name 'SamCred'

## Providing the Server Details
$ServerName = "asazure://southeastasia.asazure.windows.net/azureasdemo"
$DatabaseName = "TABDEMO"
$TableName ="FactInternetSales"

## Getting the current Month and Year
$a= Get-Date
##$startMonth=$a.Month
$startMonth=10
$startYear=$a.Year
if ( $startMonth-eq 12)
{
$endMonth="01"
$endYear=$startYear+1
}
if ( $startMonth -ne 12)
{
$endMonth =$startMonth+1
$endYear=$startYear
}
## Pad 0 at the starting if month is in signle digit
if ( $startMonth -ne 10 -or $startMonth -ne 11 -or $startMonth -ne 12)
{
$startMonth=$startMonth.ToString("00")
}

if ( $endMonth -ne 10 -or $endMonth -ne 11 -or $endMonth -ne 12)
{
$endMonth=$endMonth.ToString("00")
}
$startMonth
$endMonth

##creating the partition for the current month and current year ( You can script out the JSON code from SSMS)

$Query = "{
`"createOrReplace`": {
`"object`": {
`"database`": `"TABDemo`",
`"table`": `"FactInternetSales`",
`"partition`": `"FactInternetSales_"+ $startMonth+$startYear+"`"
},
`"partition`": {
`"name`": `"FactInternetSales_"+$startMonth+$startYear+"`",
`"source`": {
`"query`": [
`"SELECT * FROM [dbo].[FactInternetSales] WHERE ORDERDATEKEY >= N'"+ $startYear+$startMonth+"01"+ "' AND ORDERDATEKEY < N'"+ $endYear+$endMonth+"01" +"'`"
],
`"dataSource`": `"SqlServer localhost AdventureWorksDW2014`"
}
}
}
}
"
#$Query
##Creating the parition

Invoke-ASCmd -Server $ServerName -Credential $cred -Query $Query
##Processing the partition

$PartName= "FactInternetSales_"+$startMonth+$startYear
$PartName
$result=Invoke-ProcessPartition -Server $ServerName -Database $DatabaseName -TableName $TableName -PartitionName $PartName –RefreshType Full -Credential $cred

##Deleting the Old partition

if ( $startMonth-eq 01)
{
$prevMonth="12"
$prevYear=$startYear-2
}
if ( $startMonth -ne 01)
{
$prevMonth=$startMonth-1
$prevYear=$startYear-3
}
if ( $prevMonth -ne 10 -or $prevMonth -ne 11 -or $prevMonth -ne 12)
{
$prevMonth=$prevMonth.ToString("00")
}
$prevMonth

$delQuery="
{
`"delete`": {
`"object`": {
`"database`": `"TABDemo`",
`"table`": `"FactInternetSales`",
`"partition`": `"FactInternetSales_"+$prevMonth + $Prevyear +" `"
}
}
}
"

#$delQuery

Invoke-ASCmd -Server $ServerName -Credential $cred -Query $delQuery

$error[0].Exception.Message
$error[0].Exception.StackTrace

 

c. Click on the Test Pane. And then hit on the start to test.

d. Schedule the runbook.

e. Click on the Add Schedule and enter the details:

 

This is how you would be able to Schedule the Processing.  To know more about azure automation, please refer the link below:

/en-us/azure/automation/automation-intro https://azure.microsoft.com/en-in/pricing/details/automation/

 

 

Author:      Samarendra Panda - Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Kane Conway – Support Escalation Engineer, SQL Server BI Developer team, Microsoft

Comments

  • Anonymous
    September 12, 2017
    Install-Module SQLServer -FInstall-Module Azure.AnalysisServices -F$appID="531e0bXXXXXXXXXXXXXXXXX1d9de230"$appKey="c6jt6TXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXPyU="$TenantId="72f9XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX1db47"$PWord = ConvertTo-SecureString -String $appKey -AsPlainText -Force$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $AppId, $PWordLogin-AzureAsAccount -Credential $Credential -ServicePrincipal -TenantId $TenantId -RolloutEnvironment "southcentralus.asazure.windows.net"Invoke-ProcessTable -Server "asazure://southeastasia.asazure.windows.net/samssas" -TableName "FactInternetSales" -Database "TABDEMO" -RefreshType "Full" Happy Learning~Sam
  • Anonymous
    September 20, 2017
    Samanrendra, I'm following your article running the command in the following way:$AzureCred = Get-AutomationPSCredential -Name 'azurecredential'Invoke-ProcessASDatabase -Server $serverName -DatabaseName $databaseName -RefreshType Full -Credential $AzureCredbut I'm always getting error: Exception has been thrown by the target of an invocation. (Exception has been thrown by the target of an invocation. (AADSTS75005: The request is not a valid SAML 2.0 protocol message.Any idea why this could be happening?
    • Anonymous
      September 24, 2017
      Hello Francisco, It seemed to the problem with the SSO/ ADFS . ADFS uses SAML token for the single sign on. Can you please check running the same from the On-Prem Powershell window and see If you are able to run the Processing?https://azure.microsoft.com/en-in/documentation/articles/active-directory-single-sign-on-protocol-reference/Samples Script from on-prem:This is just to check if you are able to process the database from on-prem$adminID = Read-Host "Provide Azure AD admin account " ## It would be email address"$PlainPWord= Read-Host "Provide the pasword" -AsSecureString$tableName= "FactInternetSales"$serverName="asazure://southeastasia.asazure.windows.net/samssas"$databaseName ="TABDEMO"$PWord = ConvertTo-SecureString -String $PlainPWord -AsPlainText -Force$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $adminID, $PWordInvoke-ProcessTable -Server $serverName -DatabaseName $databaseName -RefreshType Full -Credential $Credential -TableName $tableName$_.Exception.Message
  • Anonymous
    October 30, 2017
    I am trying to automate the AAS database processing using the service principal and password. I run into the error "The 'ABCD' database doesn't exist on the server'. Can you let me know what I might be missing here ?Steps I followed -1. App created and assigned to AAS, made the App as an admin too.2. Created credential based on app ID and key3. Calling the credential in the powershell script4. Logging onto Azure and the calling Invoke-ProcessTable command to process just a single table
  • Anonymous
    May 24, 2018
    when I drag Invoke-ProcessPartition on runbook. I got below command with parameters. It looks like for multiple dimension cube since it contains measure group. In tabular model, I don't think there is measure group. Can you please advise what is command for Azure Analysis Service Tabular model cube for processing partition?Invoke-ProcessPartition [-WhatIf ] [-Server ] -ProcessType -Name -MeasureGroupName -Database -CubeName [-Credential ] ` [-Confirm ]