Proactive Caching and Real-Time Updates - Microsoft SQL Server 2008 Analysis Services - page 444

the system. Therefore, you can define your polling query to retrieve the latest value of the
time_id column:
Select Max(time_id) from inventory_fact_1997
The following processing query returns the records that have been inserted into the table
since the previous poll:
Select * from inventory_fact_1997
Where ? <= time_id and time_id > ?
While it is sending this query to the relational database, Analysis Services sends along two
parameters, represented by question marks. These values are the old and new values of
the time_id .
General Considerations for Proactive Caching
Proactive caching is not a magical caching system—it processes objects to create a cache.
Because proactive caching processes MOLAP caches behind the scenes, processing
consumes a lot of resources. It's a good idea to minimize the number of times and the
number of objects for which you define proactive caching.
For example, if you have a lot of data, we recommend that you partition your measure
group and define proactive caching only for the partition that holds data for the latest
period of time. Another way (or additional way) to speed up processing is to use incre-
mental updates rather than full updates.
It can take some time for you to understand the pattern of behavior—how often updates
are being triggered—in your application. Make sure that you use the same proactive
caching timings across the objects in a single cube so that Analysis Services can group the
processing of those objects into a single batch. Doing so will improve the performance of
you system.
You can also use cube objects to specify proactive caching settings. These settings are used
as defaults when you create partitions. You should stay away from setting proactive
caching on a cube object; otherwise, you might end up with proactive caching enabled for
every new partition you create.
Monitoring Proactive Caching Activity
You can use the SQL Server Profiler application to monitor proactive caching activity. (For
information about using SQL Server Profiler, see Chapter 38, “Using Trace to Monitor and
Audit Analysis Services.”) Figure 24.8 shows an example of trace events captured during
proactive caching.
In this example, we set proactive caching for the Store dimension to the scheduled
MOLAP scenario. The first event you see in the figure indicates executions of the Alter
command, which changes proactive caching settings for the Store dimension.