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,"") 
 10 write-Output "Query Executed" 
 11 $x = [xml]$xmlresult #cast the string result to an xml document 
 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) 
 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 

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