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
