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";
Senior Database Developer
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.