Report Portal

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: 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.

 

Tags: script, powershell, document

 

2007-2015 VidasSoft Systems Inc.