Automation · Continuous Integration · Deployment · SSAS

Deploy a SQL Server Analysis Services Project using PowerShell

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

“<path>\devenv.com” 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.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices.AdomdClient”)

# 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
$conn.Open()

# Read XMLA
$xmla = Get-Content $XmlaPath

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

And that’s it 🙂

2 thoughts on “Deploy a SQL Server Analysis Services Project using PowerShell

  1. Thanks for the script. But i’m having problem with non-english characters like é, à . Do you have a suggestion (Executing the XMLA in SSMS worked correctly)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s