DSO Script to list the number of aggregations in a cube (or all cubes in a database)

The DSO script below will list the number of aggregations for either a single cube or all the cubes in the specified database. Copy the script out and save it to a file called ListOlapAggregations.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 ListOLAPAggregations.vbs []

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.

'\\=== Start of Script ===//
'----------------------------------------------------------------------------------------------
' Name       : ListOlapAggregations.vbs
' Author     : Darren Gosbell (DPG)
' Date       : 17 Oct 2005
' Description: Lists the aggregations from a specified Cube.
' Notes      : From a Command prompt type the following
'
'              cscript ListOlapAggregations.vbs
'
' Revision History:
' Date          Who Ref#    Description
' 17/10/2005    DPG n/a     Initial version
'----------------------------------------------------------------------------------------------

Option Explicit

    Dim dsoServer  'As DSO.Server
    Dim dsoDatabase  'As DSO.Database
    Dim dsoCube  'As DSO.Cube
    Dim dsoPart  'As DSO.Partition
    Dim dsoSrcPart  'As DSO.Partition
    Dim dsoAgg   'As DSO.Aggregation
    Dim dsoAggSource 'As DSO.Aggregation
    Dim dsoExistingAgg 'As DSO.Aggregation
    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
   
    Set dsoAgg = CreateObject("DSO.Aggregation")

    for each oCube in dsoDatabase.MDStores   
     if sCube = "" or sCube = oCube.Name then
      '\\ loop through all the partitions in the cube
      For Each dsoPart in oCube.MDStores
              wscript.echo "Cube: " & oCube.Name & " Partition: " & dsoPart.name & " - " & dsoPart.MDStores.Count & " aggregation(s)"          
      Next  '** dsoPart
     end if
    next '** oCube
   
wscript.echo "----------------------------------------"
wscript.echo "Listing of Aggregations complete!"
wscript.quit(0)

Print | posted on Monday, October 17, 2005 3:06 PM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)