Configuring Proactive Caching in SQL Server

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.

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.

Enabling Proactive Caching

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.

Proactive Caching - General settings

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.

Proactive Caching - Notification settings

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

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Latest Articles