WSH script to get SSAS DB info about aggregation designs

This Windows Scripting Host (WSH) script shows aggregation design information for each measure group (optionally partition). To run this script pass parameters as in the example below:

CScript.exe GetDBAggInfo.vbs /ServerName:Office1 /DBName:MyDBName /ShowPartitions:Yes

Copy following script into new file GetDBAggInfo.vbs:

' List aggregation information (Cubes, measure groups and optionally partitions) in one SSAS database
' Example to run:
' CScript.exe GetDBAggInfo.vbs /ServerName:Office1 /DBName:MyDBName /ShowPartitions:Yes
' On 64bit environment run instead of CScript.exe run c:\windows\syswow64\cscript.exe

Dim ServerName, DBName, ShowPartitions
Set Arguments = WScript.Arguments.Named
ServerName = Arguments.Item("ServerName")
DBName = Arguments.Item("DBName")
ShowPartitions = Arguments.Item("ShowPartitions")
If LCase(ShowPartitions) <> "yes" Then ShowPartitions = "No"

DIM StateOfObject
StateOfObject = Array("Processed","PartiallyProcessed","Unprocessed")

Dim oServer, oDB, oCube, oMG, oPart, oDim
set oServer = CreateObject("Microsoft.AnalysisServices.Server")
oServer.Connect(ServerName)

FoundDB = False
For Each oDB in oServer.Databases
 if LCase(oDB.Name) = LCase(DBName) Then
  FoundDB = True
  Exit For
 End If
Next

If FoundDB = False Then 'Database not found, terminate script
 WSCript.Echo "***ERROR. Server: " & ServerName & " SSAS Database: " & DBName & " NOT FOUND!!!"
 WScript.Quit(1)
End If

 WScript.Echo "===== Database information"
 WScript.Echo "Name           : " & oDB.Name
 WScript.Echo "State          : " & StateOfObject(oDB.State)
 WScript.Echo "Last Update    : " & CStr(oDB.LastUpdate)
 WScript.Echo "Last Processed : " & CStr(oDB.LastProcessed)
 WScript.Echo "Estimated Size : " & FormatNumber(CDbl(oDB.EstimatedSize) / 1024 / 1024) & " MB"  ' On big DB could be slow, so consider commenting this line out
 WScript.Echo ""

 WSCript.Echo " Cubes in the database"
 For Each oCube in oDB.Cubes
  WScript.Echo "Cube: " & LEFT(oCube.Name & String(15," "), 15) & ", " & StateOfObject(oCube.State) & ", " & CStr(oCube.LastProcessed)

  For Each oMG in oCube.MeasureGroups
  WScript.Echo " MG: " & LEFT(oMG.Name & String(25," "), 25) & ", " & FormatNumber(CDbl(oMG.EstimatedSize) / 1024 / 1024) & " MB, " & StateOfObject(oMG.State) & ", " & CStr(oMG.LastProcessed)

  if oMG.AggregationDesigns.Count = 0 Then
   WScript.Echo "  No Aggregation Designs found"
  End If

  For Each oAggD in oMG.AggregationDesigns
   WScript.Echo "  Aggregation Design: " & oAggD.Name
  Next 'oAggD

  If LCase(ShowPartitions) = "yes" Then
   For Each oPart in oMG.Partitions
     WScript.Echo "   Partition : " & LEFT(oPart.Name & String(25," "), 25) & ", " & FormatNumber(CDbl(oPart.EstimatedSize) / 1024 / 1024) & " MB, " & StateOfObject(oPart.State) & ", " & CStr(oPart.LastProcessed)
     WScript.Echo "   Agg Design: " & oPart.AggregationDesign.Name
   Next ' oPart
  End If

  Next ' oMG
  WScript.Echo ""
 Next ' oCube
 WScript.Echo ""

WScript.Quit(0)

 You can download this script from here.

Result if you run this script on Adventure Works DW databse:

C:\Utilities>cscript GetDBAggInfo.vbs /ServerName:OFFICE1 /DBName:"Adventure Works DW" /ShowPartitions:No
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

===== Database information
Name           : Adventure Works DW
State          : Processed
Last Update    : 4/23/2008 11:42:07 AM
Last Processed : 8/16/2007 11:15:06 AM
Estimated Size : 52.60 MB

 Cubes in the database
Cube: Adventure Works, Processed, 4/23/2008 11:08:52 AM
 MG: Internet Sales           , 2.28 MB, Processed, 8/16/2007 11:14:42 AM
  Aggregation Design: AggregationDesign
 MG: Internet Orders          , 1.78 MB, Processed, 8/16/2007 11:14:43 AM
  No Aggregation Designs found
 MG: Internet Customers       , 1.17 MB, Processed, 8/16/2007 11:14:43 AM
  No Aggregation Designs found
 MG: Sales Reasons            , 0.00 MB, Processed, 8/16/2007 11:14:34 AM
  No Aggregation Designs found
 MG: Reseller Sales           , 3.16 MB, Processed, 4/23/2008 11:08:52 AM
  Aggregation Design: AggregationDesign
 MG: Reseller Orders          , 1.24 MB, Processed, 8/16/2007 11:14:43 AM
  No Aggregation Designs found
 MG: Sales Summary            , 2.85 MB, Processed, 8/16/2007 11:14:43 AM
  Aggregation Design: AggregationDesign
 MG: Sales Orders             , 2.11 MB, Processed, 8/16/2007 11:14:42 AM
  No Aggregation Designs found
 MG: Sales Targets            , 0.00 MB, Processed, 8/16/2007 11:14:31 AM
  No Aggregation Designs found
 MG: Financial Reporting      , 0.39 MB, Processed, 8/16/2007 11:14:40 AM
  Aggregation Design: AggregationDesign
 MG: Exchange Rates           , 0.28 MB, Processed, 8/16/2007 11:14:34 AM
  No Aggregation Designs found

Cube: Mined Customers, Processed, 8/16/2007 11:15:06 AM
 MG: Internet Sales           , 0.00 MB, Processed, 8/16/2007 11:15:06 AM
  No Aggregation Designs found
 MG: Internet Orders          , 0.00 MB, Processed, 8/16/2007 11:15:06 AM
  No Aggregation Designs found
 MG: Internet Customers       , 0.00 MB, Processed, 8/16/2007 11:15:06 AM
  No Aggregation Designs found
 MG: Sales Reasons            , 0.00 MB, Processed, 8/16/2007 11:15:06 AM
  No Aggregation Designs found
 MG: Exchange Rates           , 0.00 MB, Processed, 8/16/2007 11:15:06 AM
  No Aggregation Designs found