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 ===//