Analysis Services and powershell

  • Article

It is usefull to be able to see analysis services configuration and browsing cube structure with powershell.

to achieve that, you first need to load the AMO assembly in Powershell :

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

And then create a server object :

$serverAS = New-Object Microsoft.AnalysisServices.Server

And connect to your Analysis Services server :

$serverAS.connect("OPIERI02\R2")

 Now, you can easily query your Analysis Services to know how it is configured :

$serverAS.serverproperties | select Name, Value

Or to know the list of databases that are stored on this Analysis Services Instance :

$serverAS.databases | select Name, EstimatedSize, StorageMode, LastProcessed

If, for a database, you want to have some information about the cube :

$db = $server.databases["MyDB"]

$db.cubes | select name, storagemode, lastprocessed

if, for the same database, you want info about dimensions

$db.dimensions | select name, isparentchild, lastprocessed, storagemode

 If you want to see information about measure group in your database, you have to browse the cube collection as follow :

foreach ($c in $db.cubes) {
write-host $c.Name
foreach ($mg in $c.measuregroups) {
write-host $mg.name, $mg.estimatedsize, $mg.lastprocessed
}
}

You want to know if proactive cache is enable for partition ?

$cube = $db.cubes[“myCube”]
$mg = $cube.MeasureGroups[“myMG”]
Foreach ($p in $mg.partitions) { Write-host $p.Name, $p.estimatedSize, $p.proactivecaching.enabled}

To process all the cube for a database :

Foreach ($c in $db.cubes) {$c.process("ProcessFull")}

and to process all dimensions :

Foreach ($d in $db.dimensions) {$d.process("ProcessFull")}

And you can also backup an analysis services  database with powershell:

$db.backup("myBackup.abf")

Enjoy !