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