Proactive Caching is a feature in SSAS that allows one to specify when to process a measure group partition or dimension as the data in the relational data source changes.
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.
Configuring proactive caching
The proactive caching feature ensures a cube or a measure group partition or dimension reflects current data on its own. Depending on your choice of proactive caching enabled storage mode, interception of data change notification, storage location and update frequency will vary.
1. In the Cube Browser, open your cube and select the Partitions tab.
2. Expand the measure group and select the partition for which you want to enable proactive caching.
3. Click the Storage Setting link to open the Partition Storage Settings dialog box. 4. Move the Standard Setting slider to storage mode as per your requirement.
STANDARD SETTINGS:
A. MOLAP
B. Scheduled MOLAP
C. Automatic MOLAP
D. Medium Latency MOLAP
E. Low Latency MOLAP
F. Real Time HOLAP
G. Real Time ROLAP
5. The Custom Setting allows you to explicitly enable proactive caching (if you don't want to use Standard Setting), set storage mode, and notification options.
6. When you use proactive caching, there are several settings, as shown below, which you can manually tune
• Silence Interval - SSAS waits for a set amount of time after receiving a change notification to see if there are more change notifications coming. With this setting, SSAS ensures as many as possible can be incorporated in a single refresh cycle.
• Silence Override Interval - Because of Silence Interval, if there are continuous changes happening, your cube will never be processed. To override this we have the Silence Override Interval setting.
• Latency - Latency is the maximum amount of time SSAS waits to create a new MOLAP cache. Once latency has been reached, SSAS drops the existing MOLAP caches and starts making another one; during this time frame (during processing) queries are routed to relational data source.
• Update the cache periodically - This is the additional setting, which if selected, SSAS will ensure the MOLAP cube is refreshed periodically irrespective of any data change notification.
• Bring online immediately - If you select this option, queries are routed to the underlying relational data source while the MOLAP cache is being rebuilt.
• Enable ROLAP aggregations - If you select this option, indexed views are created at the relational database for aggregations.
• Apply settings to dimensions - If you select this option, the same proactive caching setting will be applied on related dimensions as well.
Proactive Caching supports three notification methods which inform SSAS that the relational data source has changed:
• SQL Server (2000 and later)
• Client Initiated
• Scheduled Polling
SQL Server notification can only be used if the relational data source is a SQL Server 2000 or later database. SQL Server raises trace events when data changes. In order to catch these trace events, SSAS must connect to the SQL Server with administrator rights. With SQL Server notifications, measure group partitions are always processed using Full Process which discards the contents of the partition and rebuilds it; dimensions are processed using Process Update which picks up inserts, updates, and deletes in the relational data source.
The client initiated notification is performed by sending a NotifyTableChange XMLA command to the SSAS server. For example an SSIS package that updates the data warehouse could use the Analysis Services Execute DDL Task to send the NotifyTableChange XMLA command to the SSAS server every time the data warehouse update processing is completed.
Scheduled polling simply queries the relational data source periodically to determine if the data has changed.
