DSO Script: Listing all source tables for an Analysis Services 2000 Database

I created the following script in response to a question on the microsoft.public.sqlserver.olap news group. Someone had posted a question about how to list all the source tables for an Analysis Services database that they had inherited. Using a one of my other scripts as a shell it was not too hard to pull this together, hopefully other will find it useful also.

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 ListSourceTables.vbs []

To save the output to a file simply use command line redirection

eg. cscript ListSourceTables.vbs MyServer > SourceTables.csv

would list all of the databases from the “MyServer“ Analysis Server to a file called SourceTables.csv

Copy the following script to a file called ListSourceTables.vbs

'\\ ============= START SCRIPT ===============

' Name       : ListSourceTables.vbs
' Author     : Darren Gosbell (DPG)
' Date       : 1 Oct 2005
' Description: Lists all of the Source tables for cubes and dimensions.
' Notes      : From a Command prompt type the following
'              cscript ListSourceTables.vbs []
'              (if you don't specify a database name all databases will be listed)
' Revision History:
' Date          Who Ref#    Description
' 01/10/2005    DPG n/a     Initial version

Dim dsoServer'As DSO.Server
Dim dsoCube 'As DSO.Cube
Dim mdCube  'As DSO.MDStore
Dim dsoDim  'As DSO.Dimension
Dim dsoDb   'As DSO.Database
Dim vbCrLf  'As String

vbCrLf = chr(13) & chr(10)

'\\ Get arguments from command line
If wscript.arguments.count = 2 then
 sServer = wscript.arguments(0)
    sDatabase = wscript.arguments(1)
ElseIf wscript.arguments.count = 1 then
 sServer = wscript.arguments(0)
 sDatabase = ""
    sMsg = wscript.scriptname & " expects 1 or 2 arguements to be supplied " & vbCrLf _
           & "       - the Name/IP Address of the Analysis Services Server" & vbCrLf _
           & "     - the Name of the Database" & vbCrLf _
           & vbCrLf _
           & "This script will then list all of the source tables for " & vbCrLf _
           & "the cubes and dimensions in the database." & vbCrLf
    wscript.echo sMsg
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
End If   


if sDatabase = "" then

    For Each dsoDb In dsoServer.MDStores
        ProcessDatabase dsoDb
    Next 'dsoDb
 Set dsoDB = dsoServer.MDStores(sDatabase)
 if dsoDb IS Nothing then
  wscript.echo "Error accessing Database: " & sDatabase & vbCrLf
  wscript.echo "Please check that the name is correct"
  ProcessDatabase dsoDb
 End If
End If

'\\ Processes each object in the database
Sub ProcessDatabase(dsoDb)
    clsCube = 9
 sbclsRegular = 0

    For Each dsoCube In dsoDb.MDStores
        Set mdCube = dsoCube
        If mdCube.ClassType = clsCube And mdCube.SubClassType = sbclsRegular Then
            PrintCubeSource dsoCube
            For Each dsoDim In mdCube.Dimensions
         If dsoDim.SubClassType = sbclsRegular Then
                 PrintDimSource dsoDim
                End If
            Next 'dsoDim
        End If
    Next 'dsoCube
End Sub

'\\ The following routines control what columns are printed out
Sub PrintHeader()
End Sub

Sub PrintCubeSource(dsoCube) 'As DSO.Cube)
    wscript.echo """CUBE"",""" & dsoCube.Name & """," & dsoCube.SourceTable & "," & dsoCube.SourceTableFilter
End Sub

Sub PrintDimSource(dsoDim) ' As DSO.Dimension)
    wscript.echo """DIM,""" & dsoDim.Name & """," & dsoDim.SourceTable & "," & dsoDim.SourceTableFilter
End Sub

Print | posted on Saturday, October 1, 2005 6:50 PM