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?
Thank you for your question.
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
TechNet Community Support
I presume the calculations is correct if you define it in the query scope?
The calculated measure is cached in the formula engine cache. You could try to run ClearCache XMLA command.
But I would suggest using Proactive Caching.
See similar discussion here:
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.
Microsoft Online Community Support