RSS Feed
WSH Scripting for Analysis Services 2005
September 25th, 2007 by Vidas MatelisYears 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.
Posted in SSAS | 10 Comments »
September 25th, 2007 at 10:11 pm
Have you seen what you can do with the discover stored procedures I wrote for the Analysis Services Stored Procedure project?
Try running the following from an MDX window in SSMS.
// Will list all the database on the server
CALL ASSP.DiscoverXMLMetaDataFull(“\Databases\Database”);
GO
/* Will list the details of all the cube objects in the current database */
CALL ASSP.DiscoverXMLMetaData(“Database\Cubes\Cube”);
GO
/* will list all the details of all the partition objects in the current database */
CALL ASSP.DiscoverXMLMetaData(“Partition”);
GO
/* will list all the details of all the partition objects in the current database
along with the name, state and lastProcess details for the relevant cubes. */
CALL ASSP.discoverXmlMetaData(“\Database\Cubes\Cube|Name,State,LastProcessed,LastUpdated\MeasureGroups\MeasureGroup\Partitions\Partition”);
September 25th, 2007 at 10:12 pm
Sorry I forgot to add that you would need to install ASSP http://www.codeplex.com/ASStoredProcedures if you don’t already have it.
September 26th, 2007 at 2:12 am
Hi,
on x64bit enviromnent when run cscript.exe by default is used the 32bit enviroment and not the 64bit. try use the exact path c:\windows\system32\cscript.exe
September 26th, 2007 at 4:36 am
Hi, sorry my previouse commet was wrong because it is opposite.
In order to run succesffuly your .vbs you have to use the c:\windows\syswow64\cscript.exe and not the defualt one under c:\windows\system32.
Further can you use a different editor when publish the script or code. In above sample the “quote” ” and not ” are wrong. Keep your eyes open if your use Office 2007 :-)
September 26th, 2007 at 6:50 am
Darren,
I have not tried ASSP yet, but I’ll try to install and review this option latter.
September 26th, 2007 at 6:53 am
Andreau,
Thank you very much for your suggestion on using c:\windows]syswow64\cscript.exe for script execution. This works!
Also, I added link to text files with script. This issue with double quotes is related to software I use to publish blog. I double checked my code, and even source code has simple double quotes, wordpress shows them as different quotes and this of course will cause problems for script.
Thanks again!
November 27th, 2007 at 7:53 pm
I found the examples on this site very useful, I like to make use of vbscript as well so that I can have a lot of control over formatting etc.., however, i am trying to add partition proactive caching settings to an automated build script for production depoyments, currently i’m using the following mdx query…
CALL ASSP.discoverXmlMetaData(“Partition\ProactiveCaching”)
which returns valuable information, I need to be able to return this information to the relational side of sql server using an olap linked server… it seems like the CALL ASSP. etc.. is returning the information in a tuple format, does anyone have any examples on how to get a 2 dimensional result set that I can query within a normal stored proc to look for a given value in a given field? will sp_prepare_xml work? (might be spelling that wrong (basically the xml proc that turns xml into a table))
thanks,
Jonathan Hicks
May 1st, 2008 at 1:17 pm
Wow Vidas, thanks for the vbs script!
I can’t imagine that Microsoft did not offers us anything to list processed/unprocessed cubes like in SQL2000 Analysis manager.
I’ll also try ASSP but i am a BI consultant working on multiple different clients and I dont like the fact of “installing” third party tools/code to achieve a so basic needs, however, i’ll try theses SP right now!
Thanks again!
Simon Deslauriers
October 23rd, 2008 at 7:41 am
Hi,
I am facing problem while accessing registry on 64-bit machine using VB Script.
When our application try to fetch registry information, it looks under “HKEY_LOCAL_MACHINE\Software\WOW6432Node\” because of our script runs in 32-bit compatibility mode. (i.e. from c:\windows\syswow64\cmd.exe) and calls “Cscript” to fetch data, but it doesn’t return any data.
So, We need to fetch data from “64bit registry Hive” for the same key by modifying existing VB Script.
So Is it possible this?
Please suggest any way to do this.
Thanks,
Lax
November 9th, 2008 at 7:10 pm
[…] WSH Scripting for Analysis Services 2005 […]