SSAS Cube - zero downtime even during cube processing

We recieved a requirement from our client that the cube should be available all the time - even during its processing.

I gathered from different texts that a cube can be queried while it is being processed except the last part of the processing when it actually commits the changes. This last part can take few seconds or even few minutes, during which all the new queries are put on a pending query queue. First of all, if the last part of processing takes few minutes, then it would not be accepted by the client; secondly, I am not sure if there are any side effects associated with the pending queries.

I need some guidance on what are the best practices in such situations. Can cube partitioning be helpful in this context?

This question is probably asked here:
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads

Hi kajaldas007,

I am looking the solution for the same problem - SSAS cube availability.

Did you find a solution for this? If yes, Could you post the solution?

Regards,
Praveen

You need to set your partition setting to be Real Time ROLAP. The below MSDN article describes the different types of partition settings and how ROLAP offers zero latency at the expense of performance, because it is a direct read of the underlying DW tables.

https://msdn.microsoft.com/en-us/library/ms175646.aspx

If performance is an issue (it always is isn't it?) then your next option is Low Latency MOLAP. This will switch from MOLAP to ROLAP during processing, meaning that you will not have the latest data until the processing finishes, but you won't loose the already existing data during processing.

I typically use Automatic MOLAP and have the cube listening for updates to my DW tables (I utilize this for Facts and Dimensions). This way I do not have to build and maintain a processing step in my ETL process. I am also always in MOLAP mode, which means performance is not impacted during processing. There is however latency in the data, because the new data does not become available until processing of the new cache is complete and is swapped out with the old cache.