How To Implement Proactive Caching in SQL Server Analysis Services (SSAS) 2005

Problem
We have chosen MOLAP storage in order to maximize the query performance of our cubes.  Since we have a number of cubes we are now focused on coming up with a strategy for keeping the cubes up to date as the data in our warehouse changes frequently.  Can you give us the details on how we go about implementing the Proactive Caching feature in SQL Server Analysis Services 2005?

Solution

SSAS supports three storage modes:

• MOLAP - stores detailed data and aggregations in a compressed, proprietary format; i.e. a complete copy of the data is made but query performance is excellent
• HOLAP - stores aggregations same as MOLAP, detailed data is accessed as required from the relational data source
• ROLAP - accesses detailed data and aggregations from the relational data source

Note that with MOLAP or HOLAP storage, the cube becomes out of date as soon as the relational data source changes.  Proactive Caching is a feature in SSAS that allows you to specify when to process a measure group partition or dimension as the data in the relational data source changes.  When Proactive Caching is implemented,  SSAS will handle keeping the cube up to date on its own, per the parameters you specify.  The alternative to Proactive Caching is to develop an SSIS package that processes the dimensions and measure group partitions; you would execute the SSIS package periodically.