Proactive Caching (Behind the scene)

Reposted from Amit Gupta's blog with the author's permission.

I have written on Proactive caching earlier in my posts. Proactive caching provides developer automatic management of cube processing when changes happens on relational database. Proactive caching feature provides different standard settings on how frequently you want to detect changes and get real time data. This article will be covering internal processing which happens behind the scene. 

Let me take example of "Automatic  MOLAP" where different parameter values are:

1) Silence Interval = 10 seconds
2) Silence Override Interval = 10 minutes

Snapshot1

I have set "SQL Server Notification" on "Targets" table.

Now deploy changes on cube and we are ready for receiving changes from relational database. Lets configure "SQL Server Profiler" on analysis server so we can track all the changes happening on the server.

We will change Target table data and see events in profiler:

UPDATE [dbo].[TARGETS]
SET InternetSalesRevenue = 15000000
WHERE CalendarYear = 2005

You can read about it in Darren's post "Proactive Caching - SQL Server Notification Permissions".

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/.