Proper partitioning can improve dramatically the writeback process when dealing with large data sets

Author: Nicholas Dritsas

Reviewers: Richard Tkachuk, Akshai Mirchandani

CASE:

Customer is using SQL 2008 and SSAS's writeback abilties to do 52-weeks rolling sales forecasting.  Their fact table has reached 250 million rows and they want to add 40 million records into the writeback table each week using weight allocation when updating 2 measures and executing 20,000 update cube statements per week.  This process takes 11 hours currently using MOLAP writeback storage.  They have 5 dimensions, with the biggest been the item dimension with 130,000 members and 7 levels.  They do not have properly defined aggregations or attribute relationships yet.  Overall, as number of records in writeback table is increased, performance is progressively slower.  The number of records in a writeback partition will have an impact on both query performance and writeback performance.