Cache prevents showing real-time data?

Hello.

I'm tying to make my cube show real-time data. Besides MOLAP partitions for historical data I have small ROLAP partition (no proactive caching, no aggregations at all).

When underlying data is changed, MDX queries not always show these changes.

I've found that switching data cache off (UseDataCacheRegistry=false) helps.

Unfortunately I've found no way to switch it off for one measure group only, but for the whole AS.

Could you please advice me something better?

Thank you.

Is SSAS 2005/OLAP the correct solution for you. If you run Reporting Services, TSQL-based, reports directly against your star schema or data warehouse, you will have real time data?

Regards

Thomas

|||

Thomas Ivarsson wrote:

Is SSAS 2005/OLAP the correct solution for you. If you run Reporting Services, TSQL-based, reports directly against your star schema or data warehouse, you will have real time data?

Regards

Thomas

Thank you for participating.

We actually already have pretty big and successfull datawarehouse based on AS.

Now users want real-time data too. Presented in the same way as historical data. I can't argue with this requirement as this is actually best opportunity from user perspective.

It is stated that AS is capable of showing real-time data. And I've managed to make it so.

I only wonder if there is any better (recommended) way which I have missed?

If I only could switch off data cache for ROLAP partition only..

Thanks again.

|||

Well, just wondering why you disabled proactive caching, because that feature should (in theory) enable you to display real-time data for the partition, without entirely switching server caching off. It sounds like you're trying to achieve the behavior of the standard Real-time ROLAP mode:

http://msdn2.microsoft.com/en-us/library/ms175646.aspx

>>

SQL Server 2005 Books Online

Choosing a Standard Storage Setting

Standard Storage Setting

Description

Real Time ROLAP

OLAP is in real time. Detail data and aggregations are stored in relational format. The server listens for notifications when data changes and all queries reflect the current state of the data (zero latency).

This setting would typically be used for a data source with very frequent and continuous updates when the very latest data is always required by users. Depending on the types of queries generated by client applications, this method is liable to give the slowest response times.

>>

Of course, the key to this working correctly is setting up the data change notification mechanism:

http://msdn2.microsoft.com/en-us/library/ms183681.aspx

>>

SQL Server 2005 Books Online

Notifications (Storage Options Dialog Box) (SSAS)

Use the Notifications tab of the Storage Options dialog box in Business Intelligence Development Studio to set the notification method and related settings for a dimension, cube, measure group, or partition.

...

Enable proactive caching

Enables proactive caching.

Note:

If this option is not selected, all options except Storage mode are disabled.

>>

This TechNet paper explains how the Proactive Cache settings work in more detail:

http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx

>>

SQL Server 2005

Real-time Business Intelligence Using Analysis Services

Published: April 1, 2005

By Paul Sanders

This paper covers features in SQL Server 2005 Analysis Services that overcome many of the barriers to gathering and analyzing real-time business intelligence data faced by companies today.

...

Notification Schemes

There are three notification schemes by which Analysis Services can be notified of updates to the underlying source:

?

Trace Events. This only applies where the underlying data source is SQL Server. Analysis Services will register to receive trace events on the necessary tables. This scheme requires that the service account of Analysis Services has Administrator privileges on the SQL Server database. Event delivery is not guaranteed if the database is unavailable (e.g., due to network issues) at the time of update.

?

Client Initiated. A client can simply send a notification message to Analysis Services, indicating that a specified table has been updated. This is relevant in cases where the application that is actually performing the update is aware of the impact on the Analysis Services cubes

?

Polling. The most generally applicable approach is to use polling. Polling queries are defined for each table, returning a single value (i.e., a single row, with one column), a change to which indicates an update to the table. For example, a query returning the maximum value of the LastUpdated timestamp column would be a suitable polling query. Along with the query, the designer also specifies the frequency with which Analysis Services should send the polling query.

>>

|||

Now I understand. Thank you.

Without data cache queries become way too slow. To make use of cache AND show real-time data we need some kind of notification to refresh cache.

I'll try to setup proactive caching with notifications once more.