Deploying an SSAS Database via Powershell

This is a repost from my older blog, written in May 2009.  It garnered a fair amount of interest (relatively), so I’m keeping it alive here to help as many people as possible.

I’m trying to automate the deployment of a SSAS deployment so it can be handed off easily to another team. Using a combination of XMLA and Powershell scripts, I got it to work. This is with SQL Server 2008 and may not work with other versions.

Here are the steps:

Generate the XMLA script to deploy the database

1. Build your SSAS project
2. Open the Analysis Services Deployment Wizard. You can find this in the Start Menu -> Microsoft SQL Server 2008 -> Analysis Services
3. Select the *.asdatabase file in the \bin folder of your SSAS project
4. Click through the wizard until it prompts to Create deployment script. Check this option and select a path to save the script to
5. This will generate a *.xmla file. You can test it by opening it in SSMS and executing it, and it should deploy the SSAS database successfully.

Write a Powershell Script

My script looks a little something like this:

$cwd = get-location
[System.IO.Directory]::SetCurrentDirectory($cwd)
$query = Get-Content $args[1]

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Xmla") 
[Microsoft.AnalysisServices.xmla.xmlaclient]$xmlac = new-object Microsoft.AnalysisServices.Xmla.XmlaClient 
$xmlac.Connect($args[0]) 
Write-Host ("Connected to " + $args[0])

$xmlResult = ""
$properties = new-object "System.Collections.Generic.Dictionary``2[[System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]"
$xmlac.Send($query, $properties)
$xmlac.Disconnect()

Write-Output $xmlResult

It takes two arguments: the server to connect to, and the XMLA script to execute. It runs the script on the server and writes the result back to the shell.