Powershell script to list info about one Analysis Services database
User Rating: / 9
PoorBest 
Written by Vidas Matelis   
Sunday, 09 November 2008 01:39

This powershell script will print information about cubes, measure groups, dimensions and optionally partitions in one Analysis Services database. Copy this script to the file ssas-one-db-info.ps1:

# ###################################################################
# Author: Vidas Matelis (http://www.ssas-info.com)
# This script will print cube, measure group, dimension and optionally partition info from one database
# Sample Command to start this script:
# powershell.exe c:\projects\ps\ssas\ssas-One-DB-info.ps1 -ServerName Vidas-lenovo -DBName 'Adventure Works DW' -ShowPartitions Y
# ###################################################################

param($ServerName="localhost", $DBName="", $ShowPartitions="N")

## 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
}

$DB = $server.Databases.FindByName($DBName)
if ($DB -eq $null) {
 Write-Output ("Database '{0}' not found" -f $DBName)
 break
}

Write-Output("----------------------------------------------------------------")
Write-Output("Server  : {0}" -f $Server.Name)
Write-Output("Database: {0}" -f $DB.Name)
Write-Output("DB State: {0}" -f $DB.State)
Write-Output("DB Size : {0}MB" -f ($DB.EstimatedSize/1024/1024).ToString("#,##0"))
Write-Output("----------------------------------------------------------------")

foreach ($cube in $DB.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"))
  if ($ShowPartitions -eq "Y") {
   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
  } # If ($ShowPartitions... 
 } # Measure group

} # Cube

Write-Output("")
foreach ($dim in $DB.Dimensions) {
 Write-Output ( "Dimension: {0} Status: {1}" -f $dim.Name.PadRight(35), $dim.State)
} # Dimensions

To execute this script you can run following command:

powershell.exe c:\projects\ps\ssas\ssas-One-DB-info.ps1 -ServerName Vidas-lenovo -DBName 'Adventure Works DW' -ShowPartitions Y

Here is sample how printout will look like:

C:\Users\Vidas>powershell.exe c:\projects\ps\ssas\ssas-one-db-info.ps1 -ServerName Vidas-Lenovo -DBName 'Adventure Works DW'
----------------------------------------------------------------
Server  : VIDAS-LENOVO
Database: Adventure Works DW
DB State: PartiallyProcessed
DB Size : 35MB
----------------------------------------------------------------
Cube: Adventure Works
 MG: Internet Sales           ; Status: Unprocessed; Size: 0MB
 MG: Internet Orders          ; Status: Unprocessed; Size: 0MB
 MG: Internet Customers       ; Status: Unprocessed; Size: 0MB
 MG: Sales Reasons            ; Status: Unprocessed; Size: 0MB
 MG: Reseller Sales           ; Status: Unprocessed; Size: 0MB
 MG: Reseller Orders          ; Status: Unprocessed; Size: 0MB
 MG: Sales Summary            ; Status: Unprocessed; Size: 0MB
 MG: Sales Orders             ; Status: Unprocessed; Size: 0MB
 MG: Sales Targets            ; Status: Unprocessed; Size: 0MB
 MG: Financial Reporting      ; Status: Unprocessed; Size: 0MB
 MG: Exchange Rates           ; Status: Unprocessed; Size: 0MB
Cube: Mined Customers
 MG: Internet Sales           ; Status: Unprocessed; Size: 0MB
 MG: Internet Orders          ; Status: Unprocessed; Size: 0MB
 MG: Internet Customers       ; Status: Unprocessed; Size: 0MB
 MG: Sales Reasons            ; Status: Unprocessed; Size: 0MB
 MG: Exchange Rates           ; Status: Unprocessed; Size: 0MB

Dimension: Promotion                           Status: Processed
Dimension: Product                             Status: Processed
Dimension: Customer                            Status: Processed
Dimension: Geography                           Status: Processed
Dimension: Reseller                            Status: Processed
Dimension: Sales Territory                     Status: Processed
Dimension: Employee                            Status: Processed
Dimension: Scenario                            Status: Processed
Dimension: Department                          Status: Processed
Dimension: Organization                        Status: Processed
Dimension: Account                             Status: Processed
Dimension: Date                                Status: Processed
Dimension: Source Currency                     Status: Processed
Dimension: Sales Reason                        Status: Processed
Dimension: Reseller Sales Order Details        Status: Processed
Dimension: Internet Sales Order Details        Status: Processed
Dimension: Clustered Customers                 Status: Processed
Dimension: Subcategory Basket Analysis         Status: Processed
Dimension: Sales Channel                       Status: Processed
Dimension: Destination Currency                Status: Processed
Dimension: Sales Summary Order Details         Status: Processed 

You at your own risk.

 

 
Comments (2)
2 Tuesday, 09 July 2013 10:22
Bob McAusland
Script gives me exactly what I need. Thanks.
1 Friday, 21 September 2012 14:21
Manu Kanwarpal
Thanks a lot for the script, works beautifully!

 

Pyramid Analytics