Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Oct 20 '11 at 15:27

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

up vote 1 down vote accepted

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."

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.