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)

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

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.