Configuring Proactive Caching in SQL Server

September 8, 2010

Introduction

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

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.

Real-time ROLAP

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.

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 similar to the one as shown in below image.

Figure 1 - Enabling Proactive Caching

1. Move the Standard Setting slider to storage mode as per your requirement.
2. 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.

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.

• 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.

Figure 2 - Proactive Caching - General settings

On the Notifications tab, there are three options out of which, as shown below, you can select any one at a time.

• SQL Server - With this option, SSAS uses SQL Server notification services/specialized trace mechanism to identify data changes.
• Client initiated - With this option, client can specify the XMLA (XML for Analysis) command (NotifyTableChange) to identify data changes.
• Scheduled polling - With this option, SSAS uses a series of queries to see (polling at defined interval) if there is any data change at the underlying relational database.

Figure 3 - Proactive Caching - Notification settings

Conclusion

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.

References

MSDN: Proactive Caching (Partitions)

MSDN: Choosing a Standard Storage Setting

» See All Articles by Columnist Arshad Ali