DSO Script to list Cubes Sizes

The following DSO Script lists the size for either all the cubes in a database, or for a single cube. Copy the script out and save it to a file called ListCubeSizes.vbs

To run the script issue the following command from a command shell. You will need to be logged on as a user with OLAP Administrator priviledges to get this script to run.

cscript ListCubeSizes.vbs <Server> <Database> [CubeName]

If your server, database or cube names have spaces in them, surround them in quotes. eg. “Foodmart 2000“

If you don't specify a particular cube name, the script will run over all cubes in the database.

 

'----------------------------------------------------------------------------------------------
' Name       : ListCubeSizes.vbs
' Author     : Darren Gosbell (DPG)
' Date       : 24 Feb 2006
' Description: Lists the aggregations from a specified Cube.
' Notes      : From a Command prompt type the following
'
'              cscript ListCubeSizes.vbs
'
' Revision History:
' Date          Who Ref#    Description
' 24/02/2006    DPG n/a     Initial version
'----------------------------------------------------------------------------------------------

Option Explicit

    Dim dsoServer  'As DSO.Server
    Dim dsoDatabase  'As DSO.Database
    Dim dsoCube  'As DSO.Cube  
    Dim sServer  'As String
    Dim sDatabase  'As String
    Dim sCube   'As String
    Dim sMsg  'As String
    Dim vbCrLf  'As String
 Dim oCube 
 
    Const sbclsRegular = 0
    Const olapLockWrite = 8
    vbCrLf = chr(13) & chr(10)

    '\\ Get arguments from command line
    If wscript.arguments.count = 3 Or wscript.arguments.count = 2 then
     sServer = wscript.arguments(0)
        sDatabase = wscript.arguments(1)
        if wscript.arguments.count = 2 then
            sCube =  ""
        else
         sCube = wscript.arguments(2)
        end if
    Else
        sMsg = wscript.scriptname & " expects 4 arguements to be supplied " & vbCrLf _
               & "       - the Name/IP Address of the Analysis Services Server" & vbCrLf _
               & "     - the Name of the Database" & vbCrLf _
               & "   []    - the Name of the Cube (Optional)" & vbCrLf _
               & vbCrLf _
               & "This script will then copy all the aggregations in the source partition " & vbCrLf _
               & "to all the other partitions in the cube." & vbCrLf
        wscript.echo sMsg
        wscript.quit(1)
    End If


    '\\ Connect to the server
    Set dsoServer = CreateObject("DSO.Server")
    dsoServer.Connect sServer
    If Err.Number <> 0 then
        wscript.echo "ERROR: Could not connect to the Server: " & sServer & vbCrLf & err.Description
        wscript.quit(1)
    End If
    wscript.echo "Connected to " & sServer
    wscript.echo "----------------------------------------"

    '\\ Get a reference to the Database
    If not dsoServer.MDStores.Find(sDatabase) then
        wscript.echo "ERROR: Could not find the " & sDatabase & " on " & sServer
        wscript.quit(1)
    End If
    Set dsoDatabase = dsoServer.MDStores(sDatabase)

    '\\ Get a reference to the Cube
    if sCube <> "" then
     if Not dsoDatabase.MDStores.Find(sCube) then
         wscript.echo "ERROR: Could not find the " & sCube & " in the " & sDatabase & " database"
         wscript.quit(1)
     End IF
     Set dsoCube = dsoDatabase.MDStores(sCube)
    end if
   
    for each oCube in dsoDatabase.MDStores   
     if sCube = "" or sCube = oCube.Name then
      wscript.echo "Cube: " & oCube.Name & " Size: " & FormatNumber(((oCube.EstimatedSize /1024) /1024),2) & " Mb"          
     end if
    next '** oCube
   
wscript.echo "----------------------------------------"
wscript.echo "Listing of Cubes Sizes complete!"
wscript.quit(0)

Print | posted on Friday, February 24, 2006 5:54 PM

Comments on this post

comments powered by Disqus