Proactive Caching at Work | Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)

In order to demonstrate how proactive caching works, this section also uses the sample Adventure Works Analysis Services project that comes with the product. The sample is located in the directory within your SQL Server installation (%SystemDrive%$\Program Files\Microsoft SQL Server\90\ Tools\Samples\AdventureWorks Analysis Services Project\Enterprise). To understand proactive caching functionality, do the following:

Open the sample "Adventure Works" Analysis Services project. This Analysis Services project contains a cube called Adventure Works that has several measure groups and dimensions. The measure group ResellerSales retrieves the data from the relational table FactResellerSales. The data from the FactResellerSales table has been partitioned within Analysis Services so that the ResellerSales measure group has four partitions, one for each year. You will see the behavior of proactive caching by adding rows to the last partition of the FactResellerSales measure group.
Click the Partitions tab of the Adventure Works cube and expand the ResellerSales measure group. You will see the four partitions of the ResellerSales measure group as shown in Figure 18-4. If you click in the Source column for year 2004, you will be able to see the SQL query that restricts the data for that year (OrderDateKey > 915 and OrderDateKey < 1280).

Figure 18-4

From here, we'll delete some rows from the ResellerSales table and do a bulk insert of these rows to see the behavior of proactive caching.
Open SQL Server Management Studio and execute the following query to your relational database Adventure Works DW. You will see 3004 rows are retrieved. Select the entire results set and right-click within the results pane to save the results in a text file. Name the file AdventureWorksFactResellerSales.CSV.
```
     SELECT *            FROM [dbo].[FactResellerSales]     WHERE OrderDateKey >= '1050' AND OrderDateKey <= '1280' 
```

Execute the following SQL statement to delete the rows from the table:

     DELETE FROM [dbo].[FactResellerSales]     WHERE OrderDateKey >= '1050' AND OrderDateKey <= '1280'

Deploy the Adventure Works project to your Analysis Services instance. Connect to your Analysis Services instance using SQL Server Management Studio and execute the following MDX query. You will see the results as shown in Figure 18-5.
```
     SELECT [Measures].[Reseller Sales Amount] ON 0,     NON EMPTY [Geography].[Country].MEMBERS ON 1     FROM [Adventure Works] 
```
Now you need to set the proactive caching settings for the ResellerSales partition so that changes in source data are automatically detected by Analysis Services and you get real-time data while querying the UDM. Connect to the Analysis Services instance in the Object explorer from SSMS. Navigate to the Reseller_Sales_2004 partition as shown in Figure 18-5. Right-click the partition and select Properties.

Figure 18-5
You will see the properties page for the partition. Select the proactive caching page as shown in Figure 18-6. You will see the various storage options for the partitions from Real-time ROLAP to MOLAP. In order to have this partition as MOLAP as well as enable proactive caching, you need automatic MOLAP; also you do need to set some additional proactive caching properties. Click the Custom setting radio button, then click on the Options button at the bottom right of the dialog.

Figure 18-6
You will see the Storage options dialog as shown in Figure 18-7. Enable proactive caching for the selected partition by clicking on the check box next to Enable proactive caching. Set the Latency to zero seconds, and select the option Bring Online Immediately. This ensures that you do see the updated results immediately. By default the silence interval and silence override interval are at 10 minutes. This means that the MOLAP cache will start rebuilding after 10 seconds of the notification of data change. Now you need to inform Analysis Services on the method of detecting change in the relational data source. Click the Notifications tab.

Figure 18-7
Select the SQL Server option and specify the tracking table as FactResellerSales as shown in Figure 18-8. This is a unique option specific to Microsoft SQL Server. The Analysis Services instance is able to detect changes in the data with the help of SQL Server notification. This is not available with other relational databases. You would have to choose the second option of specifying a query that will result in a value that indicates the change in data for other data sources. Click OK to accept changes made in the dialog.

Figure 18-8
You have now set up proactive caching successfully for the Reseller partition. To see the results, you just have to load the rows you deleted from the FactResellerSales table in step 4. Execute the following bulk insert SQL query to your relational database. Make sure you use the correct path!
```
     BULK INSERT dbo.FactResellerSales     FROM 'C:\Chapter18\AdventureWorksFactResellerSales.csv'     WITH     (             FIELDTERMINATOR =',',             ROWTERMINATOR = '\n',             FIRE_TRIGGERS     ) 
```
The preceding statement adds 3004 rows to the FactResellerSales table. Now you need to verify if you are able to see the real-time data by querying the UDM AdventureWorksDW. Go to the MDX query editor and execute the MDX query from step 5. You will see that the Reseller Sales for all the countries are higher than their original value, as shown in Figure 18-9. This is due to the 3004 new rows added to the FactResellerSales table. At the moment the MDX query is executed, the new MOLAP cache is not rebuilt. Therefore, Analysis Services retrieves the data for the 2004 partition from the relational data source, aggregates the data along with the data from the remaining three MOLAP partitions, and provides you the results.

image from book
Figure 18-9

You have now successfully been able to set the proactive caching option for a partition using the SQL Server notification option and are able to see real-time results. Similarly, you can manage other partitions and dimensions with proactive caching based on the real-time needs of your business users. Having learned about the basics of the proactive caching feature and how it works, now you can look at important scenarios where this feature would be useful for your business.