Deploy a SQL Server Analysis Services Project using PowerShell

Step 1: Build SQL Server Analysis Services solution using devenv /build, for example

“<path>\” AdventureWorksSSAS.sln /build Development

In the bin folder of your solution, you should find <projName>.asdatabase, <projName>.deploymentoptions, <projName>.deploymenttargets and <projName>.configsettings XML files

Step 2: Generate XMLA script using Microsoft.AnalysisServices.Deployment.exe

Microsoft.AnalysisServices.Deployment.exe <path>\AdventureWorksSSAS.asdatabase /s:GenerateXmla.log /o:SSASDatabase.xmla

Step 3. Execute XMLA on target server using PowerShell script.


# Read the target server from .deploymenttargets file generated in Step 1

$DeploymentTargetsFile = <path to <projName>.deploymenttargets>

$deploymentTargets = [xml](Get-Content $DeploymentTargetsFile)

# Connect to the server
$serverName = “Data Source=” + $deploymentTargets.DeploymentTarget.Server
$conn = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $serverName

# Read XMLA
$xmla = Get-Content $XmlaPath

# Execute XMLA
$cmd = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand $xmla, $conn
return $cmd.ExecuteNonQuery();

And that’s it 🙂