Monday, April 8, 2013

How to deploy Analysis Service XMLA with Powershell

One of the ways to deploy SSAS OLAP database is to use .XMLA file. And this post explains a simple way of doing deployment using Powershell and XMLA. In order to deploy XMLA in Powershell, we use ADOMD to submit XMLA data to OLAP server. As shown in the example below, first create an .NET connection instance from AdomdConnection class and specifiy data source to target server (which is localhost in this case). Please also note that you can specify port number (8080) after the server name, if SSAS server listens to non-default port (by default SSAS listens to TCP 2383).
#
# NOTE: If machine did not enable Powershell script execution policy yet, 
#       first use this command to enable it (Run as Administrator) :    
#       PS> Set-ExecutionPolicy RemoteSigned
#

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") | out-null

# Open Server Connection
$serverName = "Data Source=localhost:8080"
$conn = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $serverName
$conn.Open()

# Read XMLA
$xmla = Get-Content .\MyOLAP.xmla

# Execute XMLA
$cmd = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand $xmla, $conn
$cmd.ExecuteNonQuery();
Once the connection is open, read .xmla file and save file content to a powershell variable. And simply executes XMLA content against the connection by using ExecuteNonQuery() method. As a side note, I used to use a batch file to deploy OLAP database with the following command.
Microsoft.AnalysisServices.Deployment.exe .\StarliteOLAP.asdatabase /s
This worked fine until I noticed that production server has non-default port number. There seems a bug in Microsoft.AnalysisServices.Deployment.exe (SSAS 2008) which cannot deploy when port number is specified (technically when colon is specified). When trying to run the command with :8080 is specified in target server, the following error occurred.
The 'Name' property cannot contain any of the following characters: . , ; ' ` : / * | ? " & % $ ! + = ( ) [ ] { } < >