SSAS: Automating the Scripting of an SSAS database

I've been meaning to post this for a little while, and a recent post on the SSAS forum at ssas-info.com prompted me to finally get around to it.

Basically the small Powershell script below will attach to the specified SSAS server and script all of the databases out to an XMLA file. In this example I also add a timestamp in the form of YYYYMMDD to the end of the file.

$serverName = "localhost\sql08"
$outputFolder = "C:\data\"

## load the AMO and XML assemblies into the current runspace
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null
$dateStamp = (get-Date).ToString("yyyyMMdd")

## connect to the server
$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect($serverName)
foreach ($db in $svr.Databases)
{
    write-Host "Scripting: " $db.Name
    $xw = new-object System.Xml.XmlTextWriter("$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla", [System.Text.Encoding]::UTF8)
    $xw.Formatting = [System.Xml.Formatting]::Indented
    [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$svr,$db,$true,$true)
    $xw.Close()
}
$svr.Disconnect()

This is mainly useful if there are potentially changes made to your live database that are not reflected in your project. Normally I would advise that people make changes to their database projects and re-deploy, but not everyone does that. And there are some things (like roles or partitioning) that may be dynamically generated outside of your project.

I have not put any error checking or anything like that in this script, I basically wanted to show how the Scripter object can be used from Powershell (or C# with a few minor syntax changes), but simply changing the server name and output folder should be enough to get you started. I think that the account that runs this script should only need the "read definition" rights on the database.

This script should work with SSAS 2005 or above. With SQL 2008 or higher, you could setup a SQL Agent job with a Powershell job step which executes this script on a regular basis. With 2005 you would have to use a command line step and shell out to Powershell.

New on Geeks with Blogs