Exporting data from Analysis Services using PowerShell

Often we here on the BI team at PointBridge need to document existing Analysis Services implementations. This can be somewhat time consuming, so we developed a quick PowerShell script that uses Analysis Management Objects (AMO) to iterate through all existing AS databases on a particular server an return some metrics to tab-delimited output.

## Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

## Connect and get the edition of the local server
$connection = "localhost"
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($connection)

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

Write-Output ("ITEMTYPE`tNAME`tSIZE`tSOURCE`tENABLED")

foreach ($db in $server.Databases )
{
Write-Output ( "Database`t{0}`t{1}" -f $db.Name, $db.EstimatedSize )

foreach ($cube in $db.Cubes) {
Write-Output ( "CUBE`t{0}" -f $Cube.Name )
foreach ($mg in $cube.MeasureGroups) {
Write-Output ( "Measure Group`t{0}`t{1}" -f $mg.Name, $mg.EstimatedSize )
foreach ($part in $mg.Partitions) {
Write-Output ( "Partition`t{0}`t{1}" -f $part.Name, $part.EstimatedSize )
} # Partition
foreach ($measure in $mg.Measures) {
Write-Output ( "Measure`t{0}`t`t{1}" -f $measure.Name, $measure.Source )
} # Measure
} # Measure group
$db.Dimensions | % { Write-Output ("Dimension`t{0}" -f $_.Name) ; $_.Attributes | % { "DimAttribute`t" + ($_.Name) + "`t`t" + ($_.KeyColumns) + "`t" + ($_.AttributeHierarchyEnabled) }; }

} # Cube
} # Databases

OK. So it’s not the most readable stuff on the planet (I tried to clean it up a bit. I really did!), but it’ll get the job done.