Years ago I was using Windows Scripting Host (WSH) for majority of my scripting tasks. It is installed by default on all Windows PCs, VBScript language is quite easy to learn/use and you can do a lot with just a few lines of code. But since DTS was introduced, I started to use DTS more and more and now SSIS is default environment where I do all my scripting. This is because SSIS has very good error loging system and is so much more powerful. But I still use a few WSH scripts that gives me quick status of my SSAS databases. To test them in your environment just copy script to text file, name it with extension *.vbs and then run it with command “CScript YourScript.vbs” with required parameters.
I have no problems running these scripts on any of my workstation PCs. But when I tested these cscripts on my server machines I was getting error message “Microsoft VBScript runtime error: ActiveX component can’t create object ‘Microsoft.AnalysisServices.Server’”. Maybe I am getting this error message because my servers are 64bit, or I might require to manually register some dll(s). If anyone knows how to make it work on server, I would appreciate if you’ll leave your comments here.
Updated: Few hours after I posted this blog I got solution from Andreau to this problem. On 64 bit environment I have to execute script using command: “c:\windows\syswow64\cscript.exe YourScript.vbs”. Thanks Andreau!
Here are 2 scripts that you might find useful:
Script ListDBs.vbs. This script lists Analysis Services databases available on one server and list some basic information about each SSAS database.
‘ List all SSAS databases in specified server.
‘ Run example
‘ cscript ListDBs.vbs /ServerName:MyServerNameDim ServerName
Set Arguments = WScript.Arguments.Named
ServerName = Arguments.Item(”ServerName”)DIM StateOfObject
StateOfObject = Array(”Processed”,”PartiallyProcessed”,”Unprocessed”)Dim oServer, oDB, State
set oServer = CreateObject(”Microsoft.AnalysisServices.Server”)
oServer.Connect(ServerName)
WScript.Echo “=== SSAS databases on server ” & ServerName
For Each oDB in oServer.Databases
WScript.Echo 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 “”
Next
Note: As double quotes might give you problems, please use this text file to get script code.
To run this script you have to copy script into the text file, rename it to ListDBs.vbs and then use command to execute it:
cscript ListDBs.vbs /ServerName:Office1
Here Office1 is the name of your Analysis Services server.
Results of this script will look something like:
C:\Scripts>CScript ListDBs.vbs /ServerName:Office1
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.=== SSAS databases on server Office1
Adventure Works DW
State : Processed
Last Update : 9/11/2007 10:38:59 PM
Last Processed: 9/6/2007 11:04:39 PM
Estimated Size: 51.89 MBC:\Scripts>
Script GetInfoOnOneDB.vbs. This script lists cubes, measure groups, partitions (optionally) and dimensions in one Analysis Services database.
‘ List information (Cubes, measure groups, dimensions and optionally partitions) in one SSAS database
‘ Example to run:
‘ CScript GetInfoOnOneDB.vbs /ServerName:IDS-DB4 /DBName:iw61bIdsDM /ShowPartitions:YesDim 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
NextIf FoundDB = False Then ‘Database not found, terminate script
WSCript.Echo “***ERROR. Server: ” & ServerName & ” SSAS Database: ” & DBName & ” NOT FOUND!!!”
WScript.Quit(1)
End IfWScript.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 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)
Next ‘ oPart
End IfNext ‘ oMG
WScript.Echo “”
Next ‘ oCube
WScript.Echo “”WScript.Echo “=== Dimensions”
For Each oDim in oDB.Dimensions
WScript.Echo “Dim: ” & LEFT(oDim.Name & String(25,” “), 25) & “, ” & StateOfObject(oDim.State) & “, ” & CStr(oDim.LastProcessed)
Next
WScript.Quit(0)
Note: As double quotes might give you problems, please use this text file to get script code.
To run this script you have to copy script text into text file, rename it to ListDBs.vbs and then use command to execute it:
CScript GetInfoOnOneDB.vbs /ServerName:Office1 /DBName:”Adventure Works DW” /ShowPartitions:Yes
Here Office1 is the name of your Analysis Services server.
Results of this script will look something like:
C:\Scripts>cscript GetInfoOnOneDB.vbs /ServerName:Office1 /DBName:”Adventure Wor
ks DW” /ShowPartitions:Yes
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.===== Database information
Name : Adventure Works DW
State : Processed
Last Update : 9/11/2007 10:38:59 PM
Last Processed : 9/6/2007 11:04:39 PM
Estimated Size : 51.89 MBCubes in the database
Cube: Adventure Works, Processed, 9/6/2007 9:41:15 PM
MG: Internet Sales , 2.28 MB, Processed, 9/6/2007 9:41:10 PM
Partition: Internet_Sales_2001 , 0.03 MB, Processed, 9/6/2007 9:40:56 PM
Partition: Internet_Sales_2002 , 0.08 MB, Processed, 9/6/2007 9:40:59 PM
Partition: Internet_Sales_2003 , 0.95 MB, Processed, 9/6/2007 9:41:08 PM
Partition: Internet_Sales_2004 , 1.10 MB, Processed, 9/6/2007 9:41:08 PM
MG: Internet Orders , 1.73 MB, Processed, 9/6/2007 9:41:10 PM
Partition: Internet_Orders_2001 , 0.03 MB, Processed, 9/6/2007 9:40:55 PM
Partition: Internet_Orders_2002 , 0.08 MB, Processed, 9/6/2007 9:40:50 PM
Partition: Internet_Orders_2003 , 0.73 MB, Processed, 9/6/2007 9:41:08 PM
Partition: Internet_Orders_2004 , 0.85 MB, Processed, 9/6/2007 9:41:09 PM
MG: Internet Customers , 1.17 MB, Processed, 9/6/2007 9:41:09 PM
Partition: Customers_2001 , 0.01 MB, Processed, 9/6/2007 9:40:59 PM
Partition: Customers_2002 , 0.03 MB, Processed, 9/6/2007 9:40:55 PM
Partition: Customers_2003 , 0.51 MB, Processed, 9/6/2007 9:41:08 PM
Partition: Customers_2004 , 0.58 MB, Processed, 9/6/2007 9:40:57 PM
MG: Sales Reasons , 0.00 MB, Processed, 9/6/2007 9:40:55 PM
Partition: Internet_Sales_Reasons , 0.00 MB, Processed, 9/6/2007 9:40:50 PM
MG: Reseller Sales , 3.15 MB, Processed, 9/6/2007 9:41:10 PM
Partition: Reseller_Sales_2001 , 0.29 MB, Processed, 9/6/2007 9:41:08 PM
Partition: Reseller_Sales_2002 , 0.76 MB, Processed, 9/6/2007 9:41:09 PM
Partition: Reseller_Sales_2003 , 1.17 MB, Processed, 9/6/2007 9:41:09 PM
Partition: Reseller_Sales_2004 , 0.64 MB, Processed, 9/6/2007 9:41:09 PM
MG: Reseller Orders , 1.23 MB, Processed, 9/6/2007 9:41:10 PM
Partition: Reseller_Orders_2001 , 0.13 MB, Processed, 9/6/2007 9:41:09 PM
Partition: Reseller_Orders_2002 , 0.31 MB, Processed, 9/6/2007 9:41:09 PM
Partition: Reseller_Orders_2003 , 0.46 MB, Processed, 9/6/2007 9:41:09 PM
Partition: Reseller_Orders_2004 , 0.26 MB, Processed, 9/6/2007 9:41:08 PM
MG: Sales Summary , 2.85 MB, Processed, 9/6/2007 9:41:10 PM
Partition: Total_Sales_2001 , 0.08 MB, Processed, 9/6/2007 9:40:56 PM
Partition: Total_Sales_2002 , 0.42 MB, Processed, 9/6/2007 9:41:08 PM
Partition: Total_Sales_2003 , 1.09 MB, Processed, 9/6/2007 9:41:08 PM
Partition: Total_Sales_2004 , 1.07 MB, Processed, 9/6/2007 9:41:09 PM
MG: Sales Orders , 2.05 MB, Processed, 9/6/2007 9:41:10 PM
Partition: Total_Orders_2001 , 0.13 MB, Processed, 9/6/2007 9:41:08 PM
Partition: Total_Orders_2002 , 0.32 MB, Processed, 9/6/2007 9:41:08 PM
Partition: Total_Orders_2003 , 0.79 MB, Processed, 9/6/2007 9:41:08 PM
Partition: Total_Orders_2004 , 0.76 MB, Processed, 9/6/2007 9:41:06 PM
MG: Sales Targets , 0.00 MB, Processed, 9/6/2007 9:40:55 PM
Partition: Sales_Quotas , 0.00 MB, Processed, 9/6/2007 9:40:50 PM
MG: Financial Reporting , 0.39 MB, Processed, 9/6/2007 9:41:01 PM
Partition: Finance , 0.36 MB, Processed, 9/6/2007 9:41:00 PM
MG: Exchange Rates , 0.28 MB, Processed, 9/6/2007 9:40:55 PM
Partition: Currency_Rates , 0.28 MB, Processed, 9/6/2007 9:40:50 PMCube: Mined Customers, Processed, 9/6/2007 11:04:39 PM
MG: Internet Sales , 0.00 MB, Processed, 9/6/2007 11:04:38 PM
MG: Internet Orders , 0.00 MB, Processed, 9/6/2007 11:04:38 PM
MG: Internet Customers , 0.00 MB, Processed, 9/6/2007 11:04:38 PM
MG: Sales Reasons , 0.00 MB, Processed, 9/6/2007 11:04:38 PM
MG: Exchange Rates , 0.00 MB, Processed, 9/6/2007 11:04:38 PM
=== Dimensions
Dim: Promotion , Processed, 9/5/2007 10:43:16 PM
Dim: Product , Processed, 9/5/2007 10:43:45 PM
Dim: Customer , Processed, 9/5/2007 10:43:36 PM
Dim: Geography , Processed, 9/5/2007 10:43:39 PM
Dim: Reseller , Processed, 9/5/2007 10:43:49 PM
Dim: Sales Territory , Processed, 9/5/2007 10:43:28 PM
Dim: Employee , Processed, 9/5/2007 10:43:50 PM
Dim: Scenario , Processed, 9/5/2007 10:43:23 PM
Dim: Department , Processed, 9/5/2007 10:42:56 PM
Dim: Organization , Processed, 9/5/2007 10:43:15 PM
Dim: Account , Processed, 9/5/2007 10:43:09 PM
Dim: Date , Processed, 9/5/2007 10:43:42 PM
Dim: Source Currency , Processed, 9/5/2007 10:43:44 PM
Dim: Sales Reason , Processed, 9/5/2007 10:43:34 PM
Dim: Reseller Sales Order Deta, Processed, 9/5/2007 10:42:47 PM
Dim: Internet Sales Order Deta, Processed, 9/5/2007 10:43:35 PM
Dim: Clustered Customers , Processed, 9/5/2007 10:46:19 PM
Dim: Subcategory Basket Analys, Processed, 9/5/2007 10:46:19 PM
Dim: Sales Channel , Processed, 9/5/2007 10:43:48 PM
Dim: Destination Currency , Processed, 9/6/2007 9:40:29 PM
Dim: Sales Summary Order Detai, Processed, 9/5/2007 10:43:23 PMC:\Scripts>
Note: on the big databases, you might want to comment out code that get “Estimated Size” property value. This is because Analysis Services is quite slow to calculating value of this property.