• Home
  • Blog
  • MSBI
  • How to Implement Proactive Caching in SSAS - MSBI

How to Implement Proactive Caching in SSAS - MSBI

Storage Settings

While creating the partitions we must specify either of the below storage settings.
a) ROLAP (Related OLAP): In this case data and aggregations store under relational sources.
b) MOLAP (Multidimensional OLAP): Here data and aggregations store under multidimensional sources.
c) HOLAP (Hybrid OLAP): Here the data stores under relational sources and aggregations store under multidimensional sources.

If you would like to become an SSAS Certified professional, then visit Mindmajix - A Global online training platform: “ SSAS Certification Training Course ".This course will help you to achieve excellence in this domain.

        Multidimensional sources

Standard Partition Storage Settings

Standard Partition Storage Settings
Proactive Caching

The feature helps the cube in a sink with the relational data sources.
–> It takes the latency time, schedule time, and event table to capture the changes from relational data sources cube databases.
As noted, with MOLAP and HOLAP storage modes, SSAS caches data (MOLAP storage mode only) and aggregations (both MOLAP and HOLAP) on the server.
When you take a data “snapshot” by processing a cube, the data becomes outdated until you process the cube again. The amount of OLAP data latency that is acceptable will depend on your business requirements.
In some cases, your end users might require up-to-date or even real-time Information.
will depend on your business requirements.
In some cases, your end users might require up-to-date or even real-time Information.
A New feature of SSAS 2005, PROACTIVE CACHING, can help you solve data Latency Problems

Proactive caching is especially useful when the relational database is transaction-oriented and data changes at random
when data changes are predictable
Step 1:

  • Such as when you use an extract, transform, and load (ETL) process to load data
  • Consider processing the cube explicitly. When the data source is transaction-oriented and you want minimum latency, consider configuring the cube to process automatically by using proactive caching

When you enable PROACTIVE CACHING, the server can listen for data change notifications and can update dimensions and measures dynamically in an “AUTOPILOT” mode.
STEADY STATE: In steady mode, no changes are happening to the relational data.
Step 1: Client applications submit multi-dimension expressions(MDX) queries to the cube, pls check in the diagram before the page.
Step 2: The cube satisfies the Queries from a MOLAP cache. The server listens for a data change notification event, which could be one of the following three types
SQL SERVER: This option uses the Microsoft server trace events that the relational engine raises when data is changed (SQL Server 2000 and later)
CLIENT INITIATED: In this case, a client application notifies SSAS when it changes data by sending a Notify table change XML
For Analysis (XMLA) command
SCHEDULED POLLING: Ninth this option, the server periodically polls the required tables for changes.
STEP 4: At some point, a data change occurs in the data source as shown in figure
STEP 5: This change triggers a notification event to the SSAS server.
STEP 6: The server starts two stopwatches. The silence Interval stopwatch measures the time elapsed between two consecutive data change events. This will reduce the number of false starts for building the new cache until the database is quiet again.

MindMajix YouTube Channel

Checkout SSAS Interview Questions

If data changes are occurring in batches, you do not want to start rebuilding the cache with each data change event.
Instead, you can optimize proactive caching by defining a silence interval that allows a predefined amount of time for the batch changes to complete.
After data in the relational database is changed, the server knows that the MOLAP  cache is out of date and starts building a new version of the cache
STEP 6: The latency stopwatch specifies the maximum latency period of the MOLAP cache,  the administrator can also predefine the maximum latency period. During the latency period, queries are still answered by the old MOLAP cache.
When the latency period is exceeded, the server discards the old cache. While the new version of the MOLAP cache is being built, the server satisfies client queries from the ROLAP database
Step7: When the new MOLAP cache is ready, the server activates it and redirects client queries to it.
PROACTIVE CACHING enters a steady-state again until the next data change event takes place.

Explore SSAS Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!


List of Related Microsoft Certification Courses:

 SSIS Power BI
 SSRS SQL Server
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator



Course Schedule
MSBI TrainingJul 27 to Aug 11View Details
MSBI TrainingJul 30 to Aug 14View Details
MSBI TrainingAug 03 to Aug 18View Details
MSBI TrainingAug 06 to Aug 21View Details
Last updated: 03 Apr 2023
About Author

As a content writer and storyteller, Raunaak Mitra regards himself to be a prodigy in writing. He firmly believes that language is borderless, and anyone can write as long as they have a strong narrative capability and the ability to emote feelings into words. Authors like F. Scott Fitzgerald and R. K. Narayan have influenced him to keep the writing as simple as possible for anyone to understand. Other than being well-versed about technological trends like AI, Machine Learning, AR, VR, Gaming, Microprocessors, Cloud Computing, Industry 4.0, literally any technological advancement (old or new), he also shares a knack to curate fiction on sci-fi, satire, and thriller genres.

read less
  1. Share:
MSBI Articles