Implementation of Proactive Caching in Sql Server 2005
Reposted from Amit Gupta's blog with the author's permission.
In the post Analysis Services Storage Modes, I have explained different storage modes in Analysis Services like ROLAP, MOLAP and HOLAP. In ROLAP data will not be obsolete because for each analysis services query, we connect to relational database but in MOLAP and HOLAP, data becomes old after the cube processing(if there are DB changes). Now if there are changes happening on relational database then it can come in Analysis Server only on reprocessing. Proactive caching feature of Analysis Services provides the way to detect new changes and reprocess cube to reflect new changes by its own.Proactive Caching tries to minimize latency and maximize performance.Proactive caching can be set for partitions as well as for dimensions.
Before starting "How to implement Proactive caching", lets understand few terminologies which will be useful in configuring different strategies in Proactive caching.Proactive Caching can be configured by using SSMS(Sql Server management studio) as well as BIDS.
1) If you are using SSMS then browse cube till partition level, right click properties and select "Proactive Caching".
2) If you are using BIDS solution then open Partition tab, select one partition and click "Storage Settings".
Different Standard Storage Settings:
Different Parameters and their explanation:
Cache Settings:
For "Low Latency MOLAP", Silence Interval has been set 10 seconds. Whenever relational database change happens and notification comes to Analysis Server. Silence Interval start its stopwatch and if no other Database changes comes before 10 seconds then it will start reprocessing cube with new changes. If new database changes comes <10 seconds then Silence interval Stopwatch will be reset to zero so if frequent changes are coming then analysis services will wait till all the changes complete.
2) Silence Override Interval:
As soon as first database change comes after last reprocessing of cube then analysis services start one more stopwatch "Silence Override Interval". It basically overrides "Silence Interval" for reprocessing cube. For the same example, if frequent database changes are happening and silence interval is getting reset each time then SSAS will forcibly process cube after it passes "Silence Override interval" so in this example, cube will be processed after 10 minutes.
3) Latency: Old MOLAP cache will be dropped after time interval specified in Latency. Latency ensures the time interval after which data will not be old. If new cache is not available after Latency period then queries will be addressed by relational database and user will see significant performance drop in query response time.
4) Update the cache frequently(Rebuild Interval): MOLAP cache will be rebuild after specified "Rebuild interval" irrespective of database changes. It means if database changes doesn't occur then also MOLAP cache will be rebuild.
5) Bring Online Immediately: If you select this option then queries will be addressed by relational DB when new cache is not up and old cache has been dropped.
6) Enable ROLAP Aggregation: If you select this option then Aggregation objects will be created in relational database.
a) Indexed Views in SQL Server 2005 and SQL Server 2008
b) Materialized Views in Oracle
7) Apply Settings to Dimension: If you select this option then same cache settings will be applied to all related dimensions from partition. This will not be available for HOLAP standard setting.
Here is the chart which compares different Standard Storage Settings in SSAS. You can customize and create your own settings by clicking on "Custom Settings" and then specifying different parameters.
Storage Mode
|
Enable Proactive Caching
|
Silence Interval
|
Silence Override Interval
|
Latency
|
Rebuild Interval
|
Bring Online Immediately
|
|
MOLAP
|
MOLAP
|
No
|
|||||
Scheduled MOLAP
|
MOLAP
|
Yes
|
1 Day
|
||||
Automatic MOLAP
|
MOLAP
|
Yes
|
10 Sec
|
10 Min
|
|||
Medium Latency MOLAP
|
MOLAP
|
Yes
|
10 Sec
|
10 Min
|
4 hours
|
Selected
|
|
Low Latency MOLAP
|
MOLAP
|
Yes
|
10 Sec
|
10 Min
|
30 Min
|
Selected
|
|
Real Time HOLAP
|
HOLAP
|
Yes
|
0 Sec
|
0 Sec
|
Selected
|
||
Real Time ROLAP
|
ROLAP
|
Yes
|
0 Sec
|
Selected
|
- SQL Server
- Client Initiated
- Scheduled Polling
1) SQL Server Notification: Once this option is selected then SQL Server 2005 notification services will be used to identify changes in underlying table. We can specify table names under "Specify Tracking Tables". If table names has not been mentioned then SSAS tries to find out relevant tables by its own.
2) Client Initiated Notifications: This option is usually selected when you are planning to use client based notification process.
3) Scheduled Polling Notifications: This option tries to identify changes by running SQL Queries mentioned in "Polling Query", "Processing Query" fields.
Proactive caching is mostly useful in which UDM is based on OLTP data and has requirement of low latency. In Data warehouse and Data Mart, data will be pulled in specified time(ETL batch processing) then Proactive caching doesn't give much, i will prefer to use other option (SSIS based solution) to pull the data.
 |
Amit Gupta is working in BI space from last 7 years and he has started working in MSBI from Jun 2006. He specializes in business intelligence services and solutions and holds Microsoft certifications in BI space. His personal blog site can be found at http://www.msbiconcepts.com/. |
Tags: management, rolap