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

SQL Server
Analysis Services subscribes to SQL Server trace events that notify it of every change in the
entire SQL Server database. Alternatively, you can select a table or set of tables to listen to.
There are several disadvantages to using SQL Server notifications. To subscribe to SQL
Server trace events, the service account of Analysis Services must have system administra-
tor privileges.
Delivery of the trace events is not guaranteed. Under such a heavy load, SQL Server might
skip some events.
Client Initiated
This is the default option in the Proactive Caching dialog box. The client application can
send a NotifyTableChange XML/A command to Analysis Services to force notification for
a single or multiple TableID values. For example, when you design an update script for
your data warehouse, after your commands for updating relational tables, you add a
command that sends a XML/A NotifyTableChange request to Analysis Services. Listing
24.2 shows an example of the command that notifies Analysis Services of updates in the
objects that depend on the store table.
LISTING 24.2 NotifyTableChange XML/A Request
<NotifyTableChange
xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>FoodMart 2005</DatabaseID>
<DataSourceID>FoodMart 2005</DataSourceID>
</Object>
<TableNotifications>
<TableNotification>
<DbSchemaName>dbo</DbSchemaName>
<DbTableName>store</DbTableName>
</TableNotification>
</TableNotifications>
</NotifyTableChange>
Scheduled Polling Notification Mechanism
You can instruct Analysis Services to send an SQL query to the relational database to
detect changes. The query needs to return a single value. Analysis Services compares this
value to the value obtained in the previous execution of the query. If the new value is
different, notification for proactive caching is triggered. In our example for the Store
dimension, you can use the following polling query to retrieve the number of rows in the
store table:
Select count (*) from dbo.store