Copying OLAP Partition Aggregations using DSO in Analysis Services 2000

As you can see by the dates in the comments below I have had this script kicking around in my tool box since 2002. It was used in the context of the development of a very large cube with lots of partitions. As we tweaked the cube structure during the development stage it would invalidate the existing aggregations, making it  necessary to often have to redesign the aggregations. The wizard in Analysis Manager does this on a partition by partition basis and while you can choose to copy the aggregations from one partition to another you had to manually repeat this process for every partition in the cube and in our big cubes we had over 10 partitions so it was a very labour intensive process.

To execute this script simply copy the script below into a file called CopyOlapAggregations.vbs and run it using cscript as indicated in the notes section below.

'\\=== START OF SCRIPT ===//
'--------------------------------------------------------------------------
' Name       : CopyOlapAggregations.vbs
' Author     : Darren Gosbell (DPG)
' Date       : 7 May 2002
' Description: Clones the aggregations from one of the partitions to all of the
'              other partitions in the cube.
' Notes      : From a Command prompt type the following
'
'              cscript CopyOlapAggregations.vbs
'
' Revision History:
' Date          Who Ref#    Description
' 07/05/2002    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 sPartition  'As String
    Dim sMsg  'As String
    Dim vbCrLf  'As String

    Const sbclsRegular = 0
    Const olapLockWrite = 8
    vbCrLf = chr(13) & chr(10)

    '\\ Get arguments from command line
    If wscript.arguments.count = 4 then
     sServer = wscript.arguments(0)
        sDatabase = wscript.arguments(1)
        sCube = wscript.arguments(2)
        sPartition = wscript.arguments(3)
    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" & vbCrLf _
               & "   - the Name of the source Partition" & 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

    '\\ 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 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)

   
    '\\ Get a reference to the Source partition
    If dsoCube.MDStores.Find(sPartition) Then
        Set dsoSrcPart = dsoCube.MDStores(sPartition)
    Else
        wscript.echo "ERROR: The partition " & sPartition & " could not be found"
        wscript.quit(1)
    End If
    wscript.echo "Found Source Partition:" & sPartition & ", in Cube:" & scube & ", in Database:" & sDatabase

   
    Set dsoAgg = CreateObject("DSO.Aggregation")

    '\\ loop through all the partitions in the cube
    For Each dsoPart in dsoCube.MDStores
        If dsoPart.Name <> sPartition Then '\\ If this partition is not the source partition
            wscript.echo "Processing Partition: " & dsoPart.name
            dsoPart.LockObject olaplockWrite, "Copying Aggregations..."
        
     '\\ Remove any existing aggregations
            wscript.echo "  About to remove " & dsoPart.MDStores.Count & " existing aggregations"
            For Each dsoExistingAgg in dsoPart.MDStores
            '    wscript.echo "   removing existing aggregate " & dsoExistingAgg.Name
                dsoPart.MDStores.Remove dsoExistingAgg.Name
            Next 'dsoExistingAgg           

     '\\ Copy in aggregations from the Source Partition
            For Each dsoAggSource In dsoSrcPart.MDStores
     '    wscript.echo "  adding " & dsoAggSource.name
                Set dsoAgg = dsoPart.MDStores.AddNew(dsoAggSource.Name, sbclsRegular)
                dsoAggSource.Clone dsoAgg
            Next '** dsoAggSource

     '\\ Update the Partition
            dsoPart.Update
            dsoPart.UnlockObject
      
            wscript.echo "  " & dsoPart.MDStores.Count & " Aggregates copied from the '" _
                & sPartition & "' partition to the '" & dsoPart.Name & "' partition."

        End If
    Next  '** dsoPart
   
wscript.echo "Copying of Partition Aggregates successful!"
wscript.quit(0)
'\\=== END OF SCRIPT ===//

Update 12 Apr 2010: corrected the title of this Article

Print | posted on Thursday, September 8, 2005 7:22 AM

Comments on this post

# re: Copying OLAP Partitions using DSO in Analysis Services 2000

Requesting Gravatar...
Finally I found an answer, cool!
Here are my BI views, if you like it
Left by Miro Kosecek on Nov 04, 2009 12:29 AM
comments powered by Disqus