DevOps : Deploying a Tabular cube with Powershell

Introduction

One step in a SQL data warehouse DevOps project is to deploy a SSAS tabular project on an instance. In this blogpost I'm going to show you a script that I'm using for deploying SSAS tabular cubes. As inspiration for creating the deployment script, I used information from a blogger Harbinger Singh. I had to make some changes to the script to make it work in my situation.

Steps

In the script, I've created a couple of blocks of code :

1. An array of cubes, I want to deploy to the server. This will help me control which cubes to deploy. Another option is to loop over the content of a folder and deploy the cubes.
2. Create a loop and loop through the array.
3. Check if the cube is present and print a warning if it can't find the cube.
4. Adjust the .asdatabase file database connectionstrings. I've multiple connections to databases and they must be changed.
5. Adjust the .deploymenttargets file database connectionstring.
6. Generate a .configsettings file. This file is not generated with the build of a SSAS tabular model.
7. Adjust .configsettings file database connectionstrings with the desired connectionstrings.
8. Not every cube uses a connectionstring to two databases. There is check whether there is a DWH_Control connectionstring in the .configsettings file. 
9. Adjust .deploymentoptions file database connectionstrings.
10. Create the xmla script with AnalysisServices.Deployment wizard.
11. The last step is to deploy the xmla script to the server with Invoke-ASCmd.

The code

The complete script is written below.

#---------------------------------------------------------------------
# AllCubes.SSASTAB.Dev.Script
#
#---------------------------------------------------------------------
# General variables
$path           = "C:\<directory>"
$SSASServer     = "localhost"
$DwDBnameDM     = "DWH_Datamart"
$DwDBnameCTRL   = "DWH_Control"
$DwServerName   = "localhost"

# Structure bimname, CubeDB, modelname
$CubeArray = @(
             ("<filename1>" , "<cubeDB1>"           , "<modelname1>"),
             ("<filename2>" , "<cubeDB2>"           , "<modelname2>")
)

cls
Write-Host "------------------------------------"
foreach ($element in $CubeArray) {

    $bim            = $element[0]
    $CubeDB         = $element[1]
    $CubeModelName  = $element[2]

    $AsDBpath             = "$path\$bim.asdatabase"
    $DepTargetpath        = "$path\$bim.deploymenttargets"
    $ConfigPath           = "$path\$bim.configsettings"
    $DeployOption         = "$path\$bim.deploymentoptions"
    $SsasDBconnection     = "DataSource=$SsasServer;Timeout=0"
    $DwDbDMConnString     = "Provider=SQLNCLI11.1;Data Source=$DwServerName;Integrated Security=SSPI;Initial Catalog=$DwDBnameDM"
    $DwDbCTRLConnString   = "Provider=SQLNCLI11.1;Data Source=$DwServerName;Integrated Security=SSPI;Initial Catalog=$DwDBnameCTRL"
    $IsDMConnStrPresent   = [bool]0
    $IsCTRLConnStrPresent = [bool]0

    if (!(Test-Path $AsDBpath))  {
      Write-Warning "$AsDBpath absent from location"
      Write-Host "------------------------------------"
      continue
    }

    #Adjust .asdatabase file database connectionstring
    $json = (Get-Content $AsDBpath -raw) | ConvertFrom-Json
    $json.model.dataSources | % {if($_.name -eq 'DWH_DataMart'){$_.connectionString=$DwDbDMConnString ; $IsDMConnStrPresent=[bool]1 }}
    $json.model.dataSources | % {if($_.name -eq 'DWH_Control'){$_.connectionString=$DwDbCTRLConnString ; $IsCTRLConnStrPresent=[bool]1 }}
    $json | ConvertTo-Json  -Depth 10 | set-content $AsDBpath

    #Adjust .deploymenttargets file database connectionstring
    $xml  = [xml](Get-Content $DepTargetpath)
    $xml.Data.Course.Subject
    $node = $xml.DeploymentTarget
    $node.Database = $CubeDB
    $node = $xml.DeploymentTarget
    $node.Server = $SsasServer
    $node = $xml.DeploymentTarget
    $node.ConnectionString = $SsasDBconnection
    $xml.Save($DepTargetpath)

    # generate .configsettings as this file is not generated with the build. 
    if (($IsDMConnStrPresent) -and ($IsCTRLConnStrPresent))  {
        '<ConfigurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0">
            <Database>
            <DataSources>
              <DataSource>
                <ID>DWH_DataMart</ID>
                <ConnectionString>Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DWH_Datamart</ConnectionString>
                <ManagedProvider>
                </ManagedProvider>
                <ImpersonationInfo>
                  <ImpersonationMode xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">ImpersonateServiceAccount</ImpersonationMode>
                  <Account xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Account>
                  <Password xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Password>
                  <ImpersonationInfoSecurity xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">Unchanged</ImpersonationInfoSecurity>
                </ImpersonationInfo>
              </DataSource>
              <DataSource>
                <ID>DWH_Control</ID>
                <ConnectionString>Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DWH_Control</ConnectionString>
                <ManagedProvider>
                </ManagedProvider>
                <ImpersonationInfo>
                  <ImpersonationMode xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">ImpersonateServiceAccount</ImpersonationMode>
                  <Account xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Account>
                  <Password xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Password>
                  <ImpersonationInfoSecurity xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">Unchanged</ImpersonationInfoSecurity>
                </ImpersonationInfo>
              </DataSource>
            </DataSources>
          </Database>
        </ConfigurationSettings>' | Out-File -FilePath $path\$bim.configsettings
    }
    else {
        '<ConfigurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0">
          <Database>
            <DataSources>
              <DataSource>
                <ID>DWH_DataMart</ID>
                <ConnectionString>Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DWH_Datamart</ConnectionString>
                <ManagedProvider>
                </ManagedProvider>
                <ImpersonationInfo>
                  <ImpersonationMode xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">ImpersonateServiceAccount</ImpersonationMode>
                  <Account xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Account>
                  <Password xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
                  </Password>
                  <ImpersonationInfoSecurity xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">Unchanged</ImpersonationInfoSecurity>
                </ImpersonationInfo>
              </DataSource>
            </DataSources>
          </Database>
        </ConfigurationSettings>' | Out-File -FilePath $path\$bim.configsettings
    }

    #Adjust .configsettings file database connectionstring
    $xml = [xml](Get-Content $ConfigPath)
    $xml.Data.Course.Subject
    $nodeDM = $xml.ConfigurationSettings.Database.DataSources.DataSource | ? { $_.ID -eq $DwDBnameDM }
    $nodeDM.ConnectionString = $DwDbDMConnString
    $nodeCTRL = $xml.ConfigurationSettings.Database.DataSources.DataSource | ? { $_.ID -eq $DwDBnameCTRL }

    # In case here is not a DWH_Control Connectionstring in the .configsettings file
    if (![string]::IsNullOrEmpty($nodeCTRL))
    {
        $nodeCTRL.ConnectionString = $DwDbCTRLConnString
        $xml.Save($ConfigPath)
    }

    #Adjust .deploymentoptions file database connectionstring
    $xml = [xml](Get-Content $DeployOption)
    $xml.Data.Course.Subject
    $node = $xml.DeploymentOptions
    $node.ProcessingOption = "DoNotProcess"
    $xml.Save($DeployOption)

    # Create the xmla script with AnalysisServices.Deployment wizard
    Write-Host "Deploying Cube : $CubeDB"
    $path = $path
    cd $path 
    $exe = "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe"
    $param1 = $bim + ".asdatabase"
    $param2 = "/s:" + $bim + ".txt"
    $param3 = "/o:" + $bim + ".xmla"
    $param4 = "/d"
    &($exe)($param1)($param2)($param3)($param4)

    Write-Host "Importing SQL modules..."
    # import modules 
    if ((Get-Module -ListAvailable | where-object {($_.Name -eq 'SqlServer') -and ($_.Version.Major -gt 20) } |Measure).Count -eq 1){
        # implementation of new sql modules migated into new location
        Import-Module SqlServer -DisableNameChecking
    }
    else{
        # fallback for SQLPS 
        Import-Module SQLPS -DisableNameChecking -Verbose
    }

    Write-Host "Invoking deployment script... This may take several minutes."
    Invoke-ASCmd -Server:$SsasServer -InputFile $path\$bim.xmla | Out-File $path\$bim.xml
    Write-Host "Please check $path\$bim.xml as this is output of this deployment"
    Write-Host "Done."
    Write-Host "------------------------------------"
}

Final thoughts

Although it is quite a script, it is fairly easy to setup and deploy a cube with a Powershell script. In order to use it in Azure DevOps you have to replace some of the variables with the Azure DevOps variables to make it work as you desire.

Hennie