Running XMLA Queries the Powershell way

I have been experimenting a bit more recently to see what I can do with Powershell and Analysis Services. The following small script executes an MDX query using an XMLA connection. I have borrowed the xsl files from one of Chris Harrington's excellent ThinOlap samples.

  1 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Xmla") 
  2 [Microsoft.AnalysisServices.xmla.xmlaclient]$xmlac = new-Object Microsoft.AnalysisServices.Xmla.XmlaClient 
  3 $xmlac.Connect("localhost\sql05") 
  4 write-output "Connected to server"  
  5 $XmlResult = "" # Initialise the variable so that it can be passed by [ref] 
  6 $qry = "SELECT {Measures.[Internet Sales Amount], Measures.[Internet Order Quantity]} ON COLUMNS, Product.Category.Members ON ROWS FROM [Adventure Works]" 
  7 $props = "<PropertyList><Catalog>Adventure Works DW</Catalog><Format>Native</Format></PropertyList>" 
  8 $xmlac.executestatement($qry,[ref] $xmlresult,0,$props,"") 
  9  
 10 write-Output "Query Executed" 
 11 $x = [xml]$xmlresult #cast the string result to an xml document 
 12  
 13 [System.Xml.Xsl.XslCompiledTransform] $xsl = new-Object System.Xml.Xsl.XslCompiledTransform 
 14 $xsl.Load("c:\data\xamd.xsl") 
 15 [System.Xml.XmlWriterSettings] $xws = new-Object system.Xml.XmlWriterSettings 
 16 $xws.ConformanceLevel = 'Auto' 
 17 [System.Xml.XmlWriter] $xw = [System.Xml.XmlWriter]::Create("c:\data\output.htm",$xws) 
 18 $xsl.Transform($x.CreateNavigator(),$xw) 
 19  
 20 # Cleaning up  
 21 $xw.Close 
 22 $xmlac.Disconnect() 
 23 $xmlresult = "" 
 24 $x = "" 
 25 write-Output "Operation Complete"

Now this is interesting and produces nicely formatted html results, however there are many different ways of running MDX queries against Analysis Services. One of the things I found interesting was the possiblity of running some of the XMLA discover commands.

Consider the following script:

  1 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Xmla") 
  2 [Microsoft.AnalysisServices.xmla.xmlaclient]$xmlac = new-object Microsoft.AnalysisServices.Xmla.XmlaClient 
  3 $xmlac.Connect("localhost\sql05") 
  4 $XmlResult = "" # Initialise the variable so that it can be passed by [ref] 
  5 $xmlac.Discover("DISCOVER_CONNECTIONS", "", "", [ref] $XMLResult, 0, 0, 1) 
  6 $x = [xml]$xmlresult 
  7 $xmlac.Disconnect() 
  8 #output discovered connections 
  9 $x.return.root.row |Format-Table CONNECTION_ID , CONNECTION_USER_NAME, CONNECTION_LAST_COMMAND_START_TIME  

Now this gives a list of the current connections to the server, information which is not as easy to get at other ways. You can run the above command from SSMS, and there is even a template you can use, but what you get back in the results pane is xml data. Running the command in this way outputs the information in an easy to digest, text based table, with a bit of tweaking on line 9, the script could easily be altered to sort the connections in various ways, including based on the longest running query, or sorted by user name.

All in all, I think this is a handy technique and I plan to setup some generic scripts so that I can build a little Powershell toolkit for Analysis Services.

The xamd.xsl file was originally distributed as part of the Microsoft XMLA SDK (which is still available for download), but you can also get a copy of it from the following folder on my onedrive:

Update 11 Aug 2006: I had to change the posting name as the .Text engine was having trouble displaying the old name, so for those of you reading the feed there is not actually any new content, just a new name.

Update 8 Feb 2007: Noticed an error in the initialization of the $props variable, that must have snuck in when the script was pasted in originally.

Update 25 Feb 2014: Added a link to download the xamd.xsl file

Print | posted on Wednesday, June 14, 2006 8:25 PM

Comments on this post

# re: Running XMLA Queries the Powershell way

Requesting Gravatar...
It's great example, but where I can found c:\data\xamd.xsl??
Left by Mateusz on Feb 25, 2014 2:03 AM

# re: Running XMLA Queries the Powershell way

Requesting Gravatar...
@Mateusz - this file is part of the XMLA 1.1 SDK from Microsoft, but I've also edited this post to include a link to download a copy.
Left by Darren Gosbell on Feb 25, 2014 8:58 PM

# re: Running XMLA Queries the Powershell way

Requesting Gravatar...
Hi,
Greetings...
I want to update cube with java code, but I tried all the possibilities, can you please help me with PS script for updating cube.
Left by AVS pappu on Nov 25, 2014 3:42 AM

# re: Running XMLA Queries the Powershell way

Requesting Gravatar...
@AVS pappu - the code in this post is about executing a query. To update a database it would be simpler to use the AMO library. You could either use AMO objects and methods like in this post http://geekswithblogs.net/darrengosbell/archive/2008/03/17/using-powershell-to-update-the-case-of-attribute-names.aspx or this one http://www.databasejournal.com/features/mssql/article.php/3794531/Using-Windows-PowerShell-and-AMO-to-create-an-SSAS-Database.htm.

Or once you have a connection using AMO you can pass an XMLA string to the server's .Execute() method.
Left by Darren Gosbell on Nov 25, 2014 6:46 AM

# re: Running XMLA Queries the Powershell way

Requesting Gravatar...
Thanks a lot sir !!!
I will try again. will post my findings.

Thanks again...
Left by AVS pappu on Nov 25, 2014 4:25 PM

# re: Running XMLA Queries the Powershell way

Requesting Gravatar...
Hi Darren,
I am getting error while using execute command, please can you help me.
param
([string] $Servername, [string] $databasename, [string] $description)

## Add the AMO namespace
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL

$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("http://localhost/OLAP/msmdpump.dll")
$server.databases("ExcaliburMD1")
if ($server.name -eq $null) {
Write-Output ("Server '{0}' not found" -f $ServerName)
break
}
else
{
#$dbstring=$server.Databases |select-object name| select-string -simplematch $databasename
$server.Execute("UPDATE CUBE [XS] SET ( [Month].[Month Code].&[201303],[KPI].[KPI].&[NUM_EQUIPMENT],[KPI].[KPI Code].&[NUM_EQUIPMENT],[Scenario].[Scenario].&[6] ) = 999 USE_EQUAL_ALLOCATION")

$qry = "UPDATE CUBE [XS] SET ( [Month].[Month Code].&[201303],[KPI].[KPI].&[NUM_EQUIPMENT],[KPI].[KPI Code].&[NUM_EQUIPMENT],[Scenario].[Scenario].&[6] ) = 900 USE_EQUAL_ALLOCATION"
#$props = "<PropertyList><Catalog>ExcaliburMD1</Catalog><Format>Native</Format></PropertyList>"
$server.EXECUTE($qry)
Left by AVS pappu on Nov 25, 2014 7:58 PM

# re: Running XMLA Queries the Powershell way

Requesting Gravatar...
Sorry when you said update the cube I thought you wanted to alter the structure. Issuing an UPDATE command to do write back does not need the AMO library. You can do that with the ADOMD client library.

Something like the following should work:

$servername = "localhost"
$databasename = "adventure works"

## Add the ADOMD namespace
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.adomdclient") > $NULL
$connStr = "data source=$servername;initial catalog=$databasename"
$cnn = new-object Microsoft.AnalysisServices.adomdclient.adomdconnection($connStr)

$cmd = new-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand
$cmd.Connection = $cnn
$cmd.CommandText = "UPDATE CUBE [XS] SET ( [Month].[Month Code].&amp;[201303],[KPI].[KPI].&amp;[NUM_EQUIPMENT],[KPI].[KPI Code].&amp;[NUM_EQUIPMENT],[Scenario].[Scenario].&amp;[6] ) = 999 USE_EQUAL_ALLOCATION"
$cnn.Open()
$cmd.ExecuteNonQuery();
$cnn.Close()
Left by Darren Gosbell on Nov 25, 2014 10:33 PM

# re: Running XMLA Queries the Powershell way

Requesting Gravatar...
Thanks a lot sir !! its working.
:-)
Left by AVS pappu on Nov 26, 2014 4:08 AM

Your comment:

 (will show your gravatar)