Extracting SSAS Database(s) XMLA w/ PowerShell

For some time, we have been trying to get Informatica’s Metadata Manager to extract information from our SSIS, SSRS and SSAS instances in production. However, we were facing issues with credentials and service account clashes that lead us to just extract the Metadata and deploy it on a server where we had full control.

The easist wasy of doing this, was to create a PowerShell script that extracts, saves and deploys the XMLA from the databases in question. So we would just be looping through a database collection on a number of servers, in order to get the required information.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL

$SourceServers = @( "<SOURCE SERVER LIST HERE>" ); #Source
$TargetServer = "<TARGET SERVER HERE>"; #Target

cls;

$TargetServer.Connect();

ForEach( $srv in $SourceServers ) {
    $server = New-Object Microsoft.AnalysisServices.Server
    $server.connect($srv)
    ForEach(  $database in $server.Databases ) {
        
        $stringbuilder = new-Object System.Text.StringBuilder
        $stringwriter = new-Object System.IO.StringWriter($stringbuilder)
        $xmlOut = New-Object System.Xml.XmlTextWriter($stringwriter)
        $xmlOut.Formatting = [System.Xml.Formatting]::Indented

        $ScriptObject = New-Object Microsoft.AnalysisServices.Scripter

        $MSASObject=[Microsoft.AnalysisServices.MajorObject[]] @($database)

        $ScriptObject.ScriptCreate($MSASObject,$xmlOut,$false)

        $ServerPath = $server -replace "\\", "_"

        $Path = "<INSERT DUMP LOCATION>" + ".xmla";        

        $stringbuilder.ToString() |out-file -filepath $Path 
        
        #$TargetServer.Execute(@StringBuilder.ToString());
    }    
}

Basically there are a few steps you need to do, in order to fit this script into your environment. Mind the list of sources, multiple servers can be added, although this has not been tested for name clashing on target box.

If you don’t need a physical file, for the archive, this step can obviously be skipped.

Finally, you may have to clear the database collection on the Target server, before running the script. This was done as a one off. This step is also diabled, commented out, in the script above. Less distruction on account of copy/paste.

Obviously if the cubes needs to be processed at the Target location, this needs to be added as well. We were just after the Metadata, hence no processing was needed.