Analysis Services has always has been very good cube performance and it handles sparsity exceptionally well. However, with writeback there have been some considerations that needed to be taken into account when creating solutions. These are discussed here.

Below is diagram of how the cube writeback functionality has progressed within SQL Server versions 2000 -> 2005 -> 2008. We would encourage all uses to upgrade to SQL 2008 to take advantage of the performances gain as it opens up a raft of applications and how they are designed. For more information please contact OLAP Office to discuss further.

SQL 2008 Cube Writeback Explanation

• Values are retrieved directly into the worksheet from the cube.
• If a change is made to a cube multi-dimensional cell then 
  • the cube is updated with the actual value
  • the incremental change between the old cube net value and new cube net value is reflected in the "writeback" table. This is the audit trail of transactions that tells you who has changed what and when.

SQL 2000/2005 Cube Writeback Explanation

• Values are retrieved into the worksheet by summing 
  • the retrieved value from the cube and
  • the retrieved incremental changes to the respective multi-dimensional cells.
• If a change is made to the cube then the incremental change between the old net value and new value is reflected in the "writeback" table. This is the audit trail of transactions that tells you who has changed what and when.

Note: To maintain cube performance where writeback is involved with SQL 2000 and 2005 you need to keep the writeback tables at a minimum to optimize performance. This is not the case for 2008.