none
"Real Time OLAP" connection string parameter not working for calculations

    Question

  • Hi all,

    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?

    Thursday, April 18, 2013 7:20 AM

All replies

  • Hi Adam,

    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. 

    If you have any feedback on our support, please click here.

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, April 22, 2013 1:35 AM
    Moderator
  • Hi Adam,

    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:
    http://social.msdn.microsoft.com/forums/en-us/sqlanalysisservices/thread/c584ad58-689b-49cc-822f-882d590a1562

    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.

    Thanks,
     Orsi
     Microsoft Online Community Support

    Sunday, June 02, 2013 11:08 AM
    Answerer