I noticed that the scripts library at http://scripts.readify.net/ had an example on returning results from a SQL table. Not to be out done by the relational guys, I thought I would post an example of how to do the same thing against an Analysis Services 2005 cube. I can see that this could have some value in a production environment in that it could be used to automate the validation that a given tuples in the cube matched the results from a source system.
function get-cell { param([string] $server = $(throw "You must specify a server name")
, [string] $database
, [string] $cube = $(throw "You must specify a cube name")
, [string] $tuple = $(throw "You must specify a tuple"))
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") > $null
$connObj = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$connObj.ConnectionString = "Data Source=darren01\sql05;Initial Catalog=Adventure Works DW"
$connObj.Open()
$cmdObj = new-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand
$cmdObj.Connection = $connObj
$cmdObj.CommandText = "SELECT FROM $cube WHERE ($tuple)"
[Microsoft.AnalysisServices.AdomdClient.CellSet] $cs = $cmdObj.ExecuteCellSet()
$cs.Cells[0].Value
$connObj.Close()
}
## Below are some examples of calling this function
## example 1
$svr="darren01\sql05"
$db="Adventure Works DW"
$c = "[Adventure Works]"
$t = "[Measures].[Internet Order Quantity]"
get-cell $svr $db $c $t
## example 2
get-cell "darren01\sql05" "Adventure Works DW" "[Adventure Works]" "[Measures].[Internet Sales Amount],[Date].[Calendar].&[2003]"