Hello,

I have a set of multi-partition cubes in MS Analysis Services. Every
partition in a single cube has a counterpart in every other cube. Now I am
trying to synchronize cube updates by processing all corresponding
partitions in a single transaction. I am using DSO in C++.

Start Transaction
Process Cube 1, Partition 4
Process Cube 2, Partition 4
Process Cube 3, Partition 4
Process Cube 4, Partition 4
Process Cube 5, Partition 4
Process Cube 6, Partition 4
Commit Transaction

This works fine most of the time, but at some point partition update times
rise from 10 seconds 3610 seconds or the like. Eventually nothing happens
anymore.

Start Transaction
Process Cube 1, Partition 4, Finished (Time: 10s)
Process Cube 2, Partition 4, Finished (Time: 3610s)
Process Cube 3, Partition 4, Finished (Time: 3611s)
Process Cube 4, Partition 4

Some points of note at this point in time:
- the Analysis Manager loses its connection to the Analysis Service when
trying
to access the server (right click in Analysis Manager)
- the CPU usage is 0% (completely idle)
- the memory usage is at 1.17 GB (very high)
- the SQL Service Manager still reports Analysis Service as "Running"
- Pausing/Continuing the Analysis Service using SQL Service Manager has no
effect
- Stopping the Analysis Service using SQL Service Manager frees 737 MB of
memory
and DSO reports a connection error
- timeouts are set to 0 (infinite)
- Windows XP reported an automatic adjustment of swap file size, possibly
causing
temporary unsuccessful memory allocations

Now I wonder, if it's generally a bad idea to do multiple merges in a single
transaction spanning multiple cubes, or if it's simply a memory issue. Btw,
the cubes are "tiny" with around 500000 fact table rows of test data.

I'd also be interested to know, which type of lock if any I should use while
merging, and on which partitions (source, target, both).

Any response would be very appreciated. I am currently considering falling
back to merging each partition in its own transaction, but I am not very
happy with that.

Best regards, David

A correction:

This should better read:

Start Transaction
Cube 1, Merge Partition 3 into Partition 4
Cube 2, Merge Partition 3 into Partition 4
Cube 3, Merge Partition 3 into Partition 4
Cube 4, Merge Partition 3 into Partition 4
Cube 5, Merge Partition 3 into Partition 4
Cube 6, Merge Partition 3 into Partition 4
Commit Transaction

I don't have a problem with the processing of multiple partitions in a
single transaction. The problem only occurs when trying to do multiple
merges in a single transaction.

David