none
Proactive caching doesn't work

    Question

  • Hi everyone,

    Not a while ago, I asked question regarding data refresh in MOLAP. The answer was to set proactive caching for MOLAP to get latest data in database. I set automatic proactive caching with time step 30 minutes, however, I still don't get updated data until I reprocess cube. What can be an issue?

    Regards,

    Yerkhan

    Monday, July 09, 2018 9:44 AM

Answers

  • Not a while ago, I asked question regarding data refresh in MOLAP. The answer was to set proactive caching for MOLAP to get latest data in database.

    Unfortunately this answer was probably wrong. The use cases for proactive caching are very limited and while it's relatively simple to configure, it's difficult to monitor and difficult to diagnose problems when there is an issue.

    If you just want to re-process your cube every 30 minutes you would be much better off to just schedule a process script using something like a SQL Agent job. You will get much better logging of any errors, plus you can also configure notifications when errors occur and it is also much easier to pause and re-start.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 10, 2018 12:13 AM
    Moderator

All replies

  • Hi Yerkhan

    Could you please let us know if you are using Automatic MOLAP?

    Also can you please verify if you have followed all the steps related to proactive caching as described in this link?

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Monday, July 09, 2018 7:36 PM
  • Not a while ago, I asked question regarding data refresh in MOLAP. The answer was to set proactive caching for MOLAP to get latest data in database.

    Unfortunately this answer was probably wrong. The use cases for proactive caching are very limited and while it's relatively simple to configure, it's difficult to monitor and difficult to diagnose problems when there is an issue.

    If you just want to re-process your cube every 30 minutes you would be much better off to just schedule a process script using something like a SQL Agent job. You will get much better logging of any errors, plus you can also configure notifications when errors occur and it is also much easier to pause and re-start.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 10, 2018 12:13 AM
    Moderator
  • Hi Yerkhan,

    Thanks for your question.

    If your cube is small, then you can try to use Proactive caching to get the latest data automatically. Just like Ram said, you might need to verify the steps to create Proactive caching. You can also refer to below blog about how to implement proactive caching in SSAS:
    https://mindmajix.com/msbi/how-to-implement-proactive-caching-in-ssas

    If your cube is big, then I would not suggest you to use Proactive caching. Just like Darren said, it's difficult to monitor and difficult to diagnose problems when there is an issue. You might also meet performance issue in the underlying database if you enable Proactive caching, because Proactive caching need to detect the changes in the underlying database.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 10, 2018 4:34 AM
    Moderator
  • Darren,

    Thanks for your answer. What if I change cube from MOLAP to real-time ROLAP? Will it help?

    Regards,

    Yerkhan

    Tuesday, July 10, 2018 11:18 AM
  • >>Darren,

    Thanks for your answer. What if I change cube from MOLAP to real-time ROLAP? Will it help?

    What is the volume of data we are talking about in those measure groups and for how many partitions you want to set it as ROLAP?

    Please keep in mind that ROLAP has the worst performance as both the aggregation and data is stored in the back end and each time the query is fired from MDX side the data has to be aggregated over the fly.

    As Darren mentioned the best way is to just process the partition every 30 minutes using SQL Server agent job and it would give you the maximum performance as you will still be on MOLAP and will also see the latest data.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Tuesday, July 10, 2018 12:39 PM
  • Thanks for your answer. What if I change cube from MOLAP to real-time ROLAP? Will it help?

    Well that will eliminate your issue with pro-active caching not working for you, but will typically make your queries tens to hundreds of times slower. 

    As I've already mentioned if you are happy with 30 minute latency in your data then simply setting up a scheduled job to process your MOLAP storage is the best way to go. It gives you the best mix of performance and control and is the way most people refresh the data in their cubes.


    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 10, 2018 1:01 PM
    Moderator
  • Darren,

    Thanks. Will do it that way.

    Regards,

    Yerkhan

    Friday, July 13, 2018 10:09 AM
  • Darren,

    Is it possible to process cube after data is updated?

    Regards,

    Yerkhan

    Friday, July 13, 2018 10:41 AM
  • Hi Yerkhan,

    Yes. You can process the cube after data is updated. You can use process update on all dimensions and process Data +Process index on partitions. This is one of the best processing strategy followed.

    If you are looking to process only partitions then you using xmla and you can do a process add or process data+process index on those partitions once the data is updated in the back end database.

    We need more details as to how many partitions you are trying to process, what is the data size in those partitions? currently how long does it take to process your cube and partitions?

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Friday, July 13, 2018 10:51 AM
  • Ram,

    Currently, it takes about 8-10 minutes to process cube. However, I will remove most of the measures and dimensions, since I don't use them.

    "You can use process update on all dimensions and process Data +Process index on partitions. This is one of the best processing strategy followed."

    So, it will automatically process cube after data is updated?

    Regards,

    Yerkhan

    Friday, July 13, 2018 11:18 AM
  • >>So, it will automatically process cube after data is updated?

    Nope. You need to create an SQL server agent job to process the selected partition every 30 minutes.

    You can follow these links on how to process a cube using SSIS task and you can schedule the SSIS package via SQL agent job every 30 minutes.

    https://bennyaustin.wordpress.com/2013/08/19/processcube/

    https://docs.microsoft.com/en-us/sql/integration-services/control-flow/analysis-services-processing-task?view=sql-server-2017

    HTH,

    Ram



    Please vote as helpful or mark as answer, if it helps


    Friday, July 13, 2018 11:49 AM