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

The polling query is stored with the TableID data structure. Figure 24.7 shows a diagram
that illustrates this process.

DataSource

TableID1 Polling Query V1

TableID2 Polling Query V2 Processing Query

FIGURE 24.7

Polling queries and processing queries are stored along with the TableID data

structure.

The proactive caching thread iterates over the TableID data structures and determines
which tables need to be polled. For each table, it sends a polling query to the relational
database. In our example, the result of this query is the number of rows in the store
table, Analysis Services compares this number with the saved number ( V1 , in our case). A
difference in the numbers triggers a notification. After the scheduled time period,
Analysis Services sends the same query again. If you've inserted new rows into the store
table, the query will return a larger number of rows. You can use the RefreshInterval
property of the ProactiveCaching object to schedule how often Analysis Services sends
the polling query.

NOTE
For a partition, it is enough to provide a single polling query. However, for a snowflake
dimension, you might need to define several polling queries.

Incremental Updates Versus Full Updates

By default, Analysis Services performs full processing of partitions and incremental
processing of dimensions using ProcessUpdate . (For information about different process-
ing types, see Chapter 21, “Dimension and Partition Processing.”) You can configure
proactive caching to use incremental updates for partitions and the ProcessAdd for dimen-
sions. To do this, you need to provide the ProcessingQuery property of the
ProactiveCaching object for Analysis Services to use for the incremental update of a
dimension or partition. For a defining processing query, Analysis Services enables you to
write a parameterized SQL query in which the first parameter is an old value of the polling
query and the second parameter is the new value.

In the following example, you need to set up proactive caching to reprocess the Inventory
Fact 1997 partition when new records are inserted into the fact table. The values of
time_id fields for those new records will correspond to the time they were entered into