For dimensions, do not use proactive caching with a polling query but no processing query

Best Practices Recommendations

To increase performance, each polling query should typically have a corresponding processing query. A polling query is typically a query that returns a value that Analysis Services can use to determine whether changes have been made to a table or other relational object. Basically, the polling query tells the proactive caching mechanism when to read in new data. In contrast, a processing query is a query that returns the changes that were made to a table since the last time that the table was polled. Analysis Services can then use this result to incrementally update the MOLAP cache for the object. Therefore, the processing query tells the proactive caching mechanism what data is new. By setting the processing query, you can configure the proactive caching mechanism to read and process only the new data, instead of rereading and reprocessing the whole table.

To select the scheduled polling option or to configure the polling query and processing query settings, use the Notifications tab of the Storage Options dialog box. For more information, see Storage Options Dialog Box (Analysis Services - Multidimensional Data).

For More Information

For more information, see Proactive Caching (Dimensions) in SQL Server Books Online.