Powershell & MDX

Darren Gosbell talks about Powershell & MDX 

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  

Source: Darren Gosbell [MVP] - Random Procrastination