none
2008 SSAS Storage Settings

    Question

  • We are building an SSRS report where the data is updated every 30 minutes. I would like to convert this report to read off of my cube versus the relational db but I have not worked with different storage settings very much.

    I think the setting I will use is Real-Time HOLAP, so I can have zero latency, but does anyone know how SSAS listens for server changes? Do I have to change any settings or do anything specific to enable this, or does 2008 SSAS do this automatically?

    Also, are there any gotcha's that I need to watch out for?

     

    Thanks,

    Friday, June 15, 2012 6:39 PM

Answers

  • SSAS subscribes to table notifications (so it only works with Microsoft SQL Server as the data source). The problem with this is that all SSAS knows is that there has been a change in the table, it then has to do a full process of all the data as it does not know what has changed.

    But I would not advise using HOLAP (either manual or automatic) the issue with HOLAP is that performance is really good at an aggregated level (whiles it's querying the MOLAP stores) then you "fall off a cliff" when you hit a detail level. Plus you can hit consistency issues where your aggregated data is stale as it's still reprocessing, but the detail data is fresh. I don't know anyone that is using HOLAP in production.

    If you used Automatic MOLAP you can specify a polling query which will let you incrementally process just the new data, but that can be a bit fiddly to configure and you will still not get zero latency, but you could get to a low level of latency (going lower than a few minutes could be problematic). 

    If you really need 0 latency then you should probably look at ROLAP and configure some indexed views to support the aggregations. There is a case study on the www.sqlcat.com site explaining how they got a ROLAP cube to have comparable performance to an equivalent MOLAP one.


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

    • Marked as answer by King Conch Wednesday, June 20, 2012 1:58 PM
    Monday, June 18, 2012 5:10 AM

All replies

  • Hi

    Can you explain by what you mean by SSAS listens for server changes?


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Friday, June 15, 2012 7:08 PM
  • This is a screen shot for the Storage Settings of a fact table:

    Friday, June 15, 2012 7:16 PM
  • SSAS subscribes to table notifications (so it only works with Microsoft SQL Server as the data source). The problem with this is that all SSAS knows is that there has been a change in the table, it then has to do a full process of all the data as it does not know what has changed.

    But I would not advise using HOLAP (either manual or automatic) the issue with HOLAP is that performance is really good at an aggregated level (whiles it's querying the MOLAP stores) then you "fall off a cliff" when you hit a detail level. Plus you can hit consistency issues where your aggregated data is stale as it's still reprocessing, but the detail data is fresh. I don't know anyone that is using HOLAP in production.

    If you used Automatic MOLAP you can specify a polling query which will let you incrementally process just the new data, but that can be a bit fiddly to configure and you will still not get zero latency, but you could get to a low level of latency (going lower than a few minutes could be problematic). 

    If you really need 0 latency then you should probably look at ROLAP and configure some indexed views to support the aggregations. There is a case study on the www.sqlcat.com site explaining how they got a ROLAP cube to have comparable performance to an equivalent MOLAP one.


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

    • Marked as answer by King Conch Wednesday, June 20, 2012 1:58 PM
    Monday, June 18, 2012 5:10 AM
  • Thanks Darren, I appreciate the detailed answer.
    Wednesday, June 20, 2012 1:59 PM