As some may know, there is a connection string parameter "Real Time OLAP" that can be used when connecting to Analysis Services to prevent ROLAP queries from being cached and ensuring they always hit the underlying source.
This works, but it seems only for non-calculated measures. For example, take "Sales Count" as a measure, and "Gross Profit" as a calculated measure.
SELECT [Sales Count] ON 0 FROM [MyCube]
repeated queries yields repeated queries to the underlying SQL database, whereas:
SELECT [Gross Profit] ON 0 FROM [MyCube]
only the first query hits the SQL database - the rest are cached.
This is incredibly frustrating that calculated measures (with storage mode set to ROLAP) don't repeatedly hit the underlying data source.
And yes, I know you can use proactive caching to clear the cache, but I don't want the overhead of SQL notifications every few seconds for my data that is constantly loading.
Why doesn't this work as it should and is there a workaround?
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.