Configuring Proactive Caching in SQL Server
September 8, 2010
Proactive caching helps DBAs to better control the cube data automatic refresh frequency, offering near real-time data for reports. This article covers the settings for HOLAP and ROLAP, and configuring proactive caching.
Mostly data warehousing and Business Intelligence (BI) applications work on historical data, which are normally refreshed on a daily, weekly or monthly basis. Although there are some near real time data warehousing and BI applications, their query performance is painfully slow. To help in this scenario, Analysis Services (Microsoft SQL Server 2005 and later versions) provides an amazing feature called "Proactive Caching". This new feature provides a query performance similar to that of historical data, with near real time data and minimal impact on the overall system. As a BI developer, you just need to configure proactive caching, and SQL Server Analysis Services (SSAS) will ensure you get performance of a fully processed data warehouse on near real time data.
In my last article, I discussed proactive caching, what it is, how it works and different variants of settings if the storage mode is MOLAP. In this article I will be covering the settings for HOLAP and ROLAP, and how to configure proactive caching.
Real-time HOLAP mode is similar to the standard HOLAP storage mode in terms of the data storage location; Detail data is stored in the relational database in relation format whereas the calculated aggregations are stored in multidimensional format on the OLAP server. The difference between this mode and standard HOLAP storage mode is that proactive caching is enabled in this mode. In other words, in this mode the SSAS server listens for any data change notification coming from the underlying relational database. When this notification is received by SSAS, queries are switched to real time ROLAP and MOLAP aggregations are refreshed. Once this MOLAP aggregation refreshment is finished, the queries are again switched back to Real-time HOLAP. This mode has a Silence Interval of 0 seconds and no Silence Override Interval and hence it has zero latency.
You would use this storage mode when the relational data source is updated frequently and the latest data is required by the users.
Coming to the last storage mode available in proactive caching, we have Real-time ROLAP. Again, this mode is similar to standard ROLAP storage mode in terms of storage location. Both the detail data and aggregations are stored in the relational format in the underlying relational database. The difference is, in this mode, SSAS listens for change notification from the underlying relational database. When this notification is received by SSAS, it uses the changed data and, as a result of this, all query results are based on current data (zero latency).
Use this storage mode when the relational data source is updated frequently and the latest data is required by the users. In this mode, query performance is slower than all discussed modes as SSAS retrieves all of the detail data and aggregations from the underlying relational database.
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.
You use the Storage Settings dialog box in BIDS (Business Intelligence Development Studio) to set the proactive caching feature, storage location, and notification settings for a dimension, cube, measure group, or measure group partition. Though there are several paths to reach the Storage Settings dialog box, I am providing one of them below, to enable proactive caching for a partition.
When you use proactive caching, there are several settings, as discussed below, which you can manually tune, like the target latency, the notification listening intervals and the notification types. Please note some of the settings will not be available on some of the objects.
On the Notifications tab, there are three options out of which, as shown below, you can select any one at a time.
In this series of articles, I discussed Proactive Caching of SSAS 2005 and later versions. This new feature gives a similar query performance (as you had with historical data) with near real-time data, with minimal impact on the overall system. As a BI developer, you just need to configure proactive caching, and SQL SSAS will ensure you get the performance of a fully processed data warehouse on near real-time data without creating any SSIS packages or SQL Server Agent jobs.