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.

       

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

TIPS FOR EXAMS
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

HOW PROACTIVE CACHING WORKS
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.
UNSTEADY STATE
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.

Checkout SSAS Interview Questions

FOR EXAMPLE
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.

 

List of Related Microsoft Certification Courses:

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