Getting a Cell from Analysis Services 2005 using PowerShell

<< I'm an MVP! | Home | Some new blogs >>

Getting a Cell from Analysis Services 2005 using PowerShell

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]"