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
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
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
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.
- In the Cube Browser, open your cube and select the
- Expand the measure group and select the partition for
which you want to enable proactive caching.
- 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
- Move the Standard Setting slider to storage mode as per
- 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
- 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
- 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
- Client initiated - With this option, client can specify
the XMLA (XML for Analysis) command (NotifyTableChange) to identify data
- 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
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.
a Standard Storage Setting
See All Articles by Columnist