SQL Server 2005 datacube processing in high transaction system

Question

We currently have a highly transactional SQL 2005 database which is growning by around 1.2 million rows weekly. On top of this we have a SSAS datacube which is set to process every two hours for reporting.

Over the last couple of months we have been having increasing failures of the datacube processing due to the underlying measures and dimensions changing as the cube processes. Given this we are looking to re-architect the system in order to remove the problem by moving the data transactionally over to a separate reporting database and then processing the datacube on the reporting database.

Can someone with experience of this comment on if it will fix the problem or will the underlying highly transactional database still cause problems in the replication of the measures/dimensions.

Answer 1

Perhaps it would be worth looking into SNAPSHOT transaction isolation level to guarantee read consistency (either at the level of individual statements with READ_COMMITTED_SNAPSHOT or a whole transaction with ALLOW_SNAPSHOT_ISOLATION)?

This should allow the reader to have the consistent view of the database while it is transferring data to datacube, even though other transactions might be modifying the same data concurrently. Also note that according to MSDN: "when the cursors are opened under the snapshot isolation level locks are not taken, which can reduce blocking on the server."

Answer 2

Dimensions need to have all the values that the cube has. To avoid having cube value that doesn't exist in the dimensions, I did the following:

• have a separate database as a staging database
• periodically fill this staging db with data from transaction db
• the cubes are generated from this staging db
• dimensions are refreshed first, then the cubes
• NO data modifications in the staging db between refreshing dimensions and refreshing cubes