Microsoft Azure Documentation

Deploy Azure Analysis Services Models using PowerShell

This guide provides step-by-step instructions and PowerShell scripts to automate the deployment of your Azure Analysis Services models. Automating deployments is crucial for CI/CD pipelines, enabling consistent and repeatable deployments.

Prerequisites

• An Azure subscription.
• An Azure Analysis Services instance deployed.
• The Analysis Services PowerShell module installed. You can install it using:

  Install-Module -Name SqlServer -Scope CurrentUser

• A deployed Analysis Services model project (e.g., within Visual Studio or SSDT) that can be exported to a TOM compatible file.
• Appropriate permissions to connect to and deploy to your Azure Analysis Services instance (e.g., Administrator role).

Steps to Deploy a Model

1. Export Your Model Definition

Before you can deploy, you need a deployable representation of your model. The most common way is to use the TOM (Tabular Object Model) XMLA file generated from your model project.

• In Visual Studio with Analysis Services projects, right-click your project and select "Deploy".
• After successful deployment to a local instance or a development Azure AS instance, you can extract the TOM definition.
• Alternatively, you can script out the model definition directly from an existing Azure Analysis Services database using TOM or Tabular Editor.

Assume you have a file named MyCubeModel.asdatabase which contains the model definition in TOM XMLA format.

2. Create the Deployment PowerShell Script

The following script uses the Invoke-ASCmd cmdlet to execute XMLA commands against your Analysis Services instance. This script connects to your server, drops the existing database (optional but common for full redeploys), and then deploys the new model definition.

Save the following code as a .ps1 file (e.g., Deploy-AASModel.ps1):

param(
    [Parameter(Mandatory=$true)]
    [string]$ServerName,

    [Parameter(Mandatory=$true)]
    [string]$DatabaseName,

    [Parameter(Mandatory=$true)]
    [string]$ModelDefinitionFile,

    [Parameter(Mandatory=$false)]
    [switch]$DropDatabaseIfExist
)

# --- Connection and Authentication ---
# For Azure AS, you typically need to authenticate using Azure AD.
# The SqlServer module handles this automatically if you're logged in via Azure CLI or PowerShell.
# Ensure you are logged in: Connect-AzAccount

Write-Host "Connecting to Azure Analysis Services server: $ServerName..."

# --- Check if database exists ---
$dbExists = $false
try {
    $databases = Invoke-ASCmd -Server $ServerName -Query "SHOW GRANULARITY OF DATABASES"
    if ($databases -match "\[$DatabaseName\]") {
        $dbExists = $true
        Write-Host "Database '$DatabaseName' already exists."
    }
} catch {
    Write-Warning "Could not retrieve database list. It might not exist or permissions are insufficient. Error: $($_.Exception.Message)"
}

# --- Drop database if specified and exists ---
if ($DropDatabaseIfExist -and $dbExists) {
    Write-Host "Dropping existing database '$DatabaseName' on '$ServerName'..."
    $dropXmla = @"

  
    
      <Database Name="$DatabaseName" ID="$DatabaseName">
        <DropDatabase>true</DropDatabase>
      </Database>
    
  

"@
    try {
        Invoke-ASCmd -Server $ServerName -InputObject $dropXmla
        Write-Host "Database '$DatabaseName' dropped successfully."
        $dbExists = $false # Database is now gone
    } catch {
        Write-Error "Failed to drop database '$DatabaseName'. Error: $($_.Exception.Message)"
        exit 1
    }
} elseif ($DropDatabaseIfExist -and !$dbExists) {
    Write-Host "Database '$DatabaseName' does not exist, no need to drop."
}

# --- Deploy the new model definition ---
if (-not (Test-Path $ModelDefinitionFile)) {
    Write-Error "Model definition file not found: $ModelDefinitionFile"
    exit 1
}

$modelXmla = Get-Content $ModelDefinitionFile -Raw

Write-Host "Deploying model from '$ModelDefinitionFile' to database '$DatabaseName' on '$ServerName'..."

try {
    # Use Alter command with AllowCreate=true to create or update the database
    $deployXmla = @"

  
    
      $modelXmla
    
  

"@
    Invoke-ASCmd -Server $ServerName -InputObject $deployXmla
    Write-Host "Model deployment to '$DatabaseName' on '$ServerName' completed successfully."

} catch {
    Write-Error "Model deployment failed. Error: $($_.Exception.Message)"
    exit 1
}

Write-Host "Deployment process finished."

Connect-AzAccount

.\Deploy-AASModel.ps1 `
    -ServerName "your-aas-server-name.asazure.windows.net" `
    -DatabaseName "YourDatabaseName" `
    -ModelDefinitionFile "C:\path\to\your\MyCubeModel.asdatabase" `
    -DropDatabaseIfExist