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.

Loading

6 thoughts on “Extracting SSAS Database(s) XMLA w/ PowerShell

  1. Pingback: SSAS Compare by Red-Gate | T-SQL.dk

  2. Pingback: Continuous Delivery w/ Analysis Services and Visual Studio Online – T-SQL.dk

  3. Gordon DeGrandis Reply

    The script works great for multidimensional SSAS models but when trying against SSAS tabular models the script does not export the JSON definition of the tabular model but only the header of the tabular model model in XML.
    I have looked for documentation or a script to allow me to do this but I could not find any. Can you help?

  4. Pingback: Extracting SSAS Tabular Models w/ PowerShell – T-SQL.dk

  5. zeke Reply

    This is very interesting, Thank you for sharing your article. I really appreciate your efforts and I will be waiting for your further post thanks once again.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.