Configuring Proactive Caching in SQL Server

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