How To Implement Proactive Caching in SQL Server Analysis Services (SSAS) 2005
User Rating: / 0
Written by Ray Barley   
Monday, 25 August 2008 02:27

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?


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. 

Comments (1)
1 Wednesday, 10 August 2011 15:57
Can you please confirm this?

My understanding is that the "Proacive Caching" works only with SQL Server DB in SSAS 2008

Thanks in Advance!



Pyramid Analytics