SQL Server 2005 datacube processing in high transaction system 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.
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