Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

How To Implement Proactive Caching in SQL Server Analysis Services SSAS


By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | > Analysis Services Measure Groups

Problem
We have chosen MOLAP storage in order to maximize the query performance of our cubes.  Since we have a number of cubes we are now focused on coming up with a strategy for keeping the cubes up to date as the data in our warehouse changes frequently.  Can you give us the details on how we go about implementing the Proactive Caching feature in SQL Server Analysis Services 2005?

Solution

SSAS supports three storage modes:

  • MOLAP - stores detailed data and aggregations in a compressed, proprietary format; i.e. a complete copy of the data is made but query performance is excellent
  • HOLAP - stores aggregations same as MOLAP, detailed data is accessed as required from the relational data source
  • ROLAP - accesses detailed data and aggregations from the relational data source

Note that with MOLAP or HOLAP storage, the cube becomes out of date as soon as the relational data source changes.  Proactive Caching is a feature in SSAS that allows you to specify when to process a measure group partition or dimension as the data in the relational data source changes.  When Proactive Caching is implemented,  SSAS will handle keeping the cube up to date on its own, per the parameters you specify.  The alternative to Proactive Caching is to develop an SSIS package that processes the dimensions and measure group partitions; you would execute the SSIS package periodically. 

A typical scenario for Proactive Caching would be that you have a measure group partition where the latest data is added periodically then at some point the partition is merged with an existing partition.  The data for the partition is often determined based on the transaction date.  For instance you want the partition updated hourly during the day with new transactions, then at night you will merge the contents of the partition into a current week partition.  This approach allows you to process the partition often, keeping it up to date with the relational data source, while minimizing the amount of data that needs to be processed each time.  For additional details about measure group partitions, please refer to our earlier tip How To Define Measure Group Partitions in SQL Server Analysis Services (SSAS) 2005.

Proactive Caching supports three notification methods which inform SSAS that the relational data source has changed:

  • SQL Server (2000 and later)
  • Client Initiated
  • Scheduled Polling

SQL Server notification can only be used if the relational data source is a SQL Server 2000 or later database.  SQL Server raises trace events when data changes.  In order to catch these trace events, SSAS must connect to the SQL Server with administrator rights.  With SQL Server notifications, measure group partitions are always processed using Full Process which discards the contents of the partition and rebuilds it; dimensions are processed using Process Update which picks up inserts, updates, and deletes in the relational data source.

The client initiated notification is performed by sending a NotifyTableChange XMLA command to the SSAS server.  For example an SSIS package that updates the data warehouse could use the Analysis Services Execute DDL Task to send the NotifyTableChange XMLA command to the SSAS server every time the data warehouse update processing is completed. 

Scheduled polling simply queries the relational data source periodically to determine if the data has changed.

In this tip we will walk through the setup of Proactive Caching using the Adventure Works DW SSAS sample database that comes with SQL Server 2005.     

Implementing Proactive Caching

To begin launch SQL Server Management Studio (SSMS) from the Microsoft SQL Server 2005 program group and connect to an Analysis Services server.    

Step 1: Drill down to the partitions for the Internet Sales measure group in the Adventure Works cube:

Step 2: Right click on the Internet_Sales_2004 partition and select properties from the context menu; click Proactive Caching in the Select a page list box to display the following dialog:

There are a number of predefined settings as shown above.  The default setting is MOLAP with Proactive Caching disabled; i.e. SSAS does not automatically update the partition.  You can move the slider to any one of the standard settings and go with it; you can also start with a standard setting then customize it by clicking the Options button at the bottom of the dialog (not shown above).

Step 3: Move the slider in the Proactive Caching settings dialog to Scheduled MOLAP then click the Options button at the bottom of the dialog (not shown above); you will see the following Cache Settings on the General tab:   

For the Scheduled MOLAP setting, we see that the Update the cache periodically option is the only one selected, with the rebuild interval set to 1 day.  Thus the partition will be processed every day.

There are a number of other options on the Cache Settings dialog.  As you move the slider to the various standard settings you will see these how options change.  At this point let's describe these various options and settings:

  • Silence interval is the amount of time to wait after an update notification is received before processing the partition.  This allows for other updates before starting to process the partition.
  • Silence override interval is the maximum time to wait after an update notification is received before processing the partition.  Without the silence override interval you could conceivably have changes that keep occurring and the partition would never get processed.
  • Latency is the maximum amount of time that you want to use the MOLAQP cache after changes have occurred in the relational data source and before/while the partition is processed.  In other words you may continue to use the existing MOLAP cache for a period of time while it is being rebuilt.  Once the latency is reached the MOLAP cached is dropped and queries can be satisfied by going back to the relational data source.
  • Bring online immediately (when checked) will satisfy queries from the relational data source while the MOLAP cache is being rebuilt.
  • Enable ROLAP aggregations (when checked) will create materialized views for aggregations in the relational data source.

Step 4: Click the Notifications tab to review the available options:

For both SQL Server and Client initiated notifications you must specify the table(s) to be tracked; i.e. which table(s) do you care about if they change.

For the Client initiated notification you could use the Analysis Services Execute DDL Task in an SSIS package and send XMLA like the following:

<Command>
  <NotifyTableChange>
    <Provider>SQLOLEDB</Provider>
    <DataSource>localhost</DataSource>
    <InitialCatalog>AdventureWorksDW</InitialCatalog>   
    <TableNotifications>
      <TableNotification> 
        <DBTableName>FactInternetSales</DBTableName>
        <DBSchemaName>dbo</DBSchemaName>
      </TableNotification> 
    </TableNotifications>
  </NotifyTableChange>
</Command>

Step 5: Click the Scheduled polling radio button on the Notifications tab to review/setup this notification option:

The Polling interval specifies how often to poll the relational data source for changes.  The Polling Query is the query to be run to determine if there are any new rows.  The query must return one row with a single column; e.g. the MAX of a datetime column like LoadedDate.  SSAS maintains the value returned to determine when it changes, signifying that there is new data to load.  Click Enable incremental updates to extract just the new rows from the relational data source.  This requires entering the Processing Query which must properly identify the new rows using the single column returned in the Polling query.  Sample queries are shown below (you can't see the entire query in the dialog):

-- Add LoadedDate column to table
ALTER TABLE dbo.FactInternetSales
ADD LoadedDate DATETIME NOT NULL DEFAULT GETDATE()
-- Polling query
SELECT MAX(LoadedDate) LoadedDate 
FROM dbo.FactInternetSales
-- Processing query
SELECT * FROM dbo.FactInternetSales
WHERE LoadedDate > ? AND LoadedDate <= ?

Since the FactInternetSales table didn't have a suitable column to use for the queries, a DATETIME column was added.

You can experiment with the remaining Standard settings on the Proactive Caching properties dialog to get an idea of the basic capabilities.   In addition you can customize them to meet your specific requirements.

Next Steps

  • If you don't already have the AdventureWorks SSAS sample projects and databases available, you can download them here to get the starting point for this tip.  Click the AdventureWorksBICI.msi link.  Also click on the Release Notes link for the details on attaching the relational database.  The default install location for the project is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project; you will see Enterprise and Standard folders.  We used the project in the Enterprise folder.
  • While Proactive Caching is a very interesting feature, you should consider carefully whether it's the right solution versus creating an SSIS package to process your dimensions and measure group partitions.


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 18, 2014 - 10:13:38 AM - Raymond Barley Back To Top

Try searching the Analysis Services forum: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlanalysisservices

 


Tuesday, February 18, 2014 - 12:48:43 AM - Sanjeewan Back To Top

Hello,

I have implemented Proactive cache in one of my project it was working fine for first two month , After that it stops automatically , What can be the reason what should I do Where should I do

My setting are as below

Storage Mode : MOLAP

Cache Setting

General :-

Cache Setting

Update the Cache when data changes

Silence Interval                                                 : 10 Sec

Silence Override Interval              : 10 Min.

Notification

SQL Server:

Tracking Tables : Respective tables             

 


Thursday, September 12, 2013 - 8:19:18 AM - sam Back To Top

Hi Ray,

 

I have problem in my calculated member. Whenever this member involve in calculation or query it take large time to execute. I am trying to narrow down execution time.
I have to remove IIF condition from the members and start using scope instead.
CREATE Member CurrentCube.[Measures].[AvgAmount]
as  IIF( ISLeaf([Customer].[ParentCustomer].currentmember),
[Measures].[Value],
(SUM([CCube^Customer].[ParentCustomer].CURRENTMEMBER.CHILDREN) /
COUNT([Customer].[ParentCustomer].CURRENTMEMBER.CHILDREN))) ,
 Format_String = "#.0000000;-#.0000000;0;0",
 Non_Empty_Behavior =[Measures].[Amout];

I have created hierarchy of customer which is [ParentCustomer] here.  I want to see avg amount of all the children under the parent customer but when I am looking child level which does not have any children in it should only show the [Measures].[Amout].

Thanks in advance

Regards,
Sam


Friday, August 23, 2013 - 5:44:11 AM - samir Back To Top

Ok thanks let me go through the document.

Thanks Again

sam


Thursday, August 22, 2013 - 8:40:02 AM - Ray Barley Back To Top

You need to take a look at the Analysis Services Performance Guide to get some ideas on the kinds of optimizations you should consider: http://sqlcat.com/sqlcat/b/whitepapers/archive/2011/10/10/analysis-services-2008-r2-performance-guide.aspx

In particular look at chapter 4 which goes through performance monitor tracing and SQL profiler tracing.


Thursday, August 22, 2013 - 2:06:31 AM - samir Back To Top

Hi,

I need your help, I have Budgeting solution developed in vb.net. I have performance problem in mdx query. I have following specification on my SSAS

1 Cube

5 dimension

1 mesure group 1 Measure

Year wise pertition each partition has more then 1,00,000 rows.

my solution is also working for both standerd version and Enterprise version for Enterprise version I used Proactive caching

I have number of formulla and 1 AVG calculated member

my big reports still taking more then 5 mins to load. If u can help me that would be appriciated.

Thanks

Samir


Learn more about SQL Server tools