Powershell script to list info about one Analysis Services database
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: 0MBDimension: 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.
Tags: script, powershell, document