How to generate SSAS OLAP database creation script

From both deployed Multidimensional and Tabular database

0 1,407
Reading Time: 2 minutes

How to generate SSAS OLAP database creation script from deployed Multidimensional and Tabular database

In this article we will show you how to export whole SSAS Multidimensional and Tabular database objects from published databases with the help of PowerShell and Analysis Management Objects (AMO). This proccess is crutial for CI/CD and backup purpose without saving any business data inside exported file.

Step 1: Develop a PowerShell function to export OLAP database structure

In this phase we will develop a function that take SSAS Instance name, OLAP database name and destination folder path and file name for exporting database creation commands into that file. This function will export commands for both Multidimensional and Tabular models.

For Multidimensional Model databases this script will export database and all of it’s contained objects creation commands on a single XMLA zipped file, also for Tabular Model databases the exported file is a Json zipped file.

Function ExportOlapStructure
{
    Param
        (
        [Parameter(Mandatory=$true)][string]$InstanceName,
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$DestinationFolderPath,
        [Parameter(Mandatory=$true)][string]$DestinationFileName
        )

    # load the AMO and XML assemblies into the current runspace
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular") > $null
    [System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null

    # connect to the olap server
    $myServer = new-Object Microsoft.AnalysisServices.Server;
    $myServer.Connect($InstanceName);
    $myDatabase = $myServer.Databases.GetByName("$DatabaseName");
    
    if ($myServer.ServerMode -eq [Microsoft.AnalysisServices.ServerMode]::Multidimensional) {
        $myFileName="$DestinationFileName.xmla"
        $myFilePath="$DestinationFolderPath\$myFileName"
        $myXmlWriter = new-object System.Xml.XmlTextWriter("$myFilePath", [System.Text.Encoding]::UTF8)
        $myXmlWriter.Formatting = [System.Xml.Formatting]::Indented
        [Microsoft.AnalysisServices.Scripter]::WriteCreate($myXmlWriter,$myServer,$myDatabase,$true,$true)
        $myXmlWriter.Close()
        }
    elseif ($myServer.ServerMode -eq [Microsoft.AnalysisServices.ServerMode]::Tabular) {
        $myFileName="$DestinationFileName.json"
        $myFilePath="$DestinationFolderPath\$myFileName"
        [Microsoft.AnalysisServices.Tabular.JsonScripter]::ScriptCreate($myDatabase,$false) | Out-File -FilePath "$myFilePath" -Encoding utf8
    }
    $myServer.Disconnect()

    $myZipFilePath="$DestinationFolderPath\$myFileName.zip"
    Compress-Archive -Path $myFilePath -DestinationPath $myZipFilePath -CompressionLevel Optimal -Force
    Remove-Item -Path $myFilePath
}

 

Step 2: Call developed function for exporting SSAS OLAP database

Now we will call our function to generate OLAP Multidimensional database create commands, in this sample our SSAS Multidimensional server name is “Server01” and Our SSAS instance name is “SqlDeep”, also our database name is “SqlDeep_MDM”

#Exporting an OLAP Multidimensional database
ExportOlapStructure -InstanceName "Server01\SqlDeep" -DatabaseName "SqlDeep_MDM" -DestinationFolderPath "U:\Databases\ReleaseStore\LocalBackup" -DestinationFileName "Dev_MDM";

 

At final step we will call our function to generate OLAP Tabular database creation commands, in this sample our SSAS Tabular server name is “Server02” and Our SSAS instance name is “SqlDeep”, also our database name is “SqlDeep_TBM”

#Exporting an OLAP Tabular database
ExportOlapStructure -InstanceName "Server02\SqlDeep" -DatabaseName "SqlDeep_TBM" -DestinationFolderPath "U:\Databases\ReleaseStore\LocalBackup" -DestinationFileName "Dev_TBM";

 

All-in-One PowerShell script

Now you can use this script as PowerShell job in Microsoft SQL Server Agent or as a Publish step in AzureDevops or any CI/CD platforms.

Function ExportOlapStructure
{
    Param
        (
        [Parameter(Mandatory=$true)][string]$InstanceName,
        [Parameter(Mandatory=$true)][string]$DatabaseName,
        [Parameter(Mandatory=$true)][string]$DestinationFolderPath,
        [Parameter(Mandatory=$true)][string]$DestinationFileName
        )

    # load the AMO and XML assemblies into the current runspace
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular") > $null
    [System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null

    # connect to the olap server
    $myServer = new-Object Microsoft.AnalysisServices.Server;
    $myServer.Connect($InstanceName);
    $myDatabase = $myServer.Databases.GetByName("$DatabaseName");
    
    if ($myServer.ServerMode -eq [Microsoft.AnalysisServices.ServerMode]::Multidimensional) {
        $myFileName="$DestinationFileName.xmla"
        $myFilePath="$DestinationFolderPath\$myFileName"
        $myXmlWriter = new-object System.Xml.XmlTextWriter("$myFilePath", [System.Text.Encoding]::UTF8)
        $myXmlWriter.Formatting = [System.Xml.Formatting]::Indented
        [Microsoft.AnalysisServices.Scripter]::WriteCreate($myXmlWriter,$myServer,$myDatabase,$true,$true)
        $myXmlWriter.Close()
        }
    elseif ($myServer.ServerMode -eq [Microsoft.AnalysisServices.ServerMode]::Tabular) {
        $myFileName="$DestinationFileName.json"
        $myFilePath="$DestinationFolderPath\$myFileName"
        [Microsoft.AnalysisServices.Tabular.JsonScripter]::ScriptCreate($myDatabase,$false) | Out-File -FilePath "$myFilePath" -Encoding utf8
    }
    $myServer.Disconnect()

    $myZipFilePath="$DestinationFolderPath\$myFileName.zip"
    Compress-Archive -Path $myFilePath -DestinationPath $myZipFilePath -CompressionLevel Optimal -Force
    Remove-Item -Path $myFilePath
} 

#Exporting an OLAP Multidimensional database
ExportOlapStructure -InstanceName "Server01\SqlDeep" -DatabaseName "SqlDeep_MDM" -DestinationFolderPath "U:\Databases\ReleaseStore\LocalBackup" -DestinationFileName "Dev_MDM";

#Exporting an OLAP Tabular database
ExportOlapStructure -InstanceName "Server02\SqlDeep" -DatabaseName "SqlDeep_TBM" -DestinationFolderPath "U:\Databases\ReleaseStore\LocalBackup" -DestinationFileName "Dev_TBM";

 

Author Profile

Senior Database Developer

Author Profile

Database administration is an exciting job combining knowledge, art and experience; you must live with it to love it, despite all the difficulties.
Currently I'm working as a Microsoft SQL Server DBA and BI consultant.

Leave A Reply

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More