Report Portal

PowerShell script to list info about SSAS databases

Read here first on how to use PowerShell.

Here is the PowerShell script that lists all SSAS databases and info about them (cubes and measure groups) from one instance. Copy code below and save it to file SSAS-All-DB-Info.ps1:

# Sample command to start:
# powershell.exe c:\projects\ps\ssas\ssas-All-DB-info.ps1 -ServerName Vidas-lenovo
# This script will list all SSAS databases and info about them (cubes and measure groups) from one instance

param($ServerName="localhost")

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

$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
 Write-Output ("Server '{0}' not found" -f $ServerName)
 break
}

foreach ($d in $server.Databases )
{
 Write-Output ( "Database: {0}; Status: {1}; Size: {2}MB" -f $d.Name, $d.State, ($d.EstimatedSize/1024/1024).ToString("#,##0") )

 foreach ($cube in $d.Cubes) {
  Write-Output ( " Cube: {0}" -f $Cube.Name )
  foreach ($mg in $cube.MeasureGroups) {
   Write-Output ( "  MG: {0}; Status: {1}; Size: {2}MB" -f $mg.Name.PadRight(25), $mg.State, ($mg.EstimatedSize/1024/1024).tostring("#,##0"))
# Uncomment following 3 lines if you want to show partition info  
#   foreach ($part in $mg.Partitions) {
#    Write-Output ( "   Partition: {0}; Status: {1}; Size: {2}MB" -f $part.Name.PadRight(35), $part.State, ($part.EstimatedSize/1024/1024).ToString("#,##0") )
#   } # Partition
  } # Measure group
# Uncomment following 3 lines if you want to show dimension info
#  foreach ($dim in $d.Dimensions) {
#   Write-Output ( "Dimension: {0}" -f $dim.Name)
#  } # Dimensions

 } # Cube

} # Databases

To run this job execute following command:

powershell.exe c:\projects\ps\ssas\ssas-all-db-info.ps1 -ServerName Vidas-lenovo

Here is sample result:

C:\Users\Vidas>powershell.exe c:\projects\ps\ssas\ssas-all-db-info.ps1 -ServerNa
me Vidas-Lenovo
Database: Adventure Works DW; Status: PartiallyProcessed; Size: 50MB
 Cube: Adventure Works
  MG: Internet Sales           ; Status: Processed; Size: 2MB
  MG: Internet Orders          ; Status: Processed; Size: 2MB
  MG: Internet Customers       ; Status: Processed; Size: 1MB
  MG: Sales Reasons            ; Status: Processed; Size: 0MB
  MG: Reseller Sales           ; Status: Processed; Size: 3MB
  MG: Reseller Orders          ; Status: Processed; Size: 1MB
  MG: Sales Summary            ; Status: Processed; Size: 3MB
  MG: Sales Orders             ; Status: Processed; Size: 2MB
  MG: Sales Targets            ; Status: Processed; Size: 0MB
  MG: Financial Reporting      ; Status: Processed; Size: 0MB
  MG: Exchange Rates           ; Status: Processed; Size: 0MB
 Cube: Mined Customers
  MG: Internet Sales           ; Status: Processed; Size: 0MB
  MG: Internet Orders          ; Status: Processed; Size: 0MB
  MG: Internet Customers       ; Status: Processed; Size: 0MB
  MG: Sales Reasons            ; Status: Processed; Size: 0MB
  MG: Exchange Rates           ; Status: Processed; Size: 0MB
Database: TSSUG_AS1; Status: PartiallyProcessed; Size: 0MB
 Cube: Orders
  MG: Orders                   ; Status: Unprocessed; Size: 0MB

C:\Users\Vidas>

This script was tested on SSAS 2008, but should work with SSAS 2005 to. Use at your own risk.

 

 

2007-2015 VidasSoft Systems Inc.