Post reply

Proactive Caching

  • December 19, 2011 at 8:26 am

    #265198

    Hi,

    I need some info on Proactive Caching.

    Have a scenario where in Users will be inserting the data (its not that frequent) and they want to see the changes

    reflected in the cube and latency can be upto 5 - 10 minutes.

    So, which storage mode can we set inorder to accomplish this.

    Can we use Automatic MOLAP?

    Thanks in Advance,

    Samyuktha

  • December 19, 2011 at 11:18 am

    #1423232

    No, I don't think automatic MOLAP will meet your latency requirements of 5-10 minutes. With automatic molap, the server never switches to real-time OLAP, and queries may be stale while the new cache is built. For this, you'll perhaps want to look into custom the proactive-cache settings.

    Here's a link to get you started --> http://msdn.microsoft.com/en-us/library/ms174499.aspx

    Another option, if your cube is large you may want to consider a custom partitioning scheme to help with this requirement. Since the updates are only inserts, you could separate the current day (or week) into its own partition and use ROLAP on top of an INDEXED view...assuming the changes are to fact table data only. Otherwise, query performance could suffer greatly because of the proactive caching settings you will be using to meet your latency requirements. If the new cache isn't finished being built by the end of the time window (5-10 minutes in your case) then user queries will start being forwarded to the relational tables (read: ROLAP performance) to meet the latency requirements.

    You have a lot of options here.

    Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com

  • December 19, 2011 at 11:22 am

    #1423234

    Thanks a lot!

    But,there will be Updates and deletes too along with Inserts.

    What is your suggestion.Which storage mode can we use.

  • December 19, 2011 at 12:06 pm

    #1423259

    samyuktha.vallabhaneni (12/19/2011)

    Thanks a lot!

    But,there will be Updates and deletes too along with Inserts.

    What is your suggestion.Which storage mode can we use.

    will the updates, deletes, inserts only involve recent data? For example, will they be updating data from 3-6 months ago?

    Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com

  • December 19, 2011 at 12:08 pm

    #1423260

    I think so,but not sure about this.

  • December 19, 2011 at 12:09 pm

    #1423261

    iPolvo (12/19/2011)

    samyuktha.vallabhaneni (12/19/2011)

    Thanks a lot!

    But,there will be Updates and deletes too along with Inserts.

    What is your suggestion.Which storage mode can we use.

    will the updates, deletes, inserts only involve recent data? For example, will they be updating data from 3-6 months ago?

    I think so,but not sure

  • December 19, 2011 at 12:43 pm

    #1423277

    samyuktha.vallabhaneni (12/19/2011)

    iPolvo (12/19/2011)

    samyuktha.vallabhaneni (12/19/2011)

    Thanks a lot!

    But,there will be Updates and deletes too along with Inserts.

    What is your suggestion.Which storage mode can we use.

    will the updates, deletes, inserts only involve recent data? For example, will they be updating data from 3-6 months ago?

    I think so,but not sure

    if they could be updating/deleting/adding data anywhere in the cube AND want a 5-10min latency, then they will have to sacrifice query speed. Obviously you shoudl test this and properly set expectations...maybe they will change their minds on the 5-10 min requirements.

    Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply