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.