Question on OLAP cubes storage modes

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the SSAS2005 OLAP Cubes storage modes. We know SSAS2005 supports 3 different storage modes: ROLAP, MOLAP, HOLAP.

Do all these three storage modes of cubes store data in another physica analysis services databases which are inrelative from their data warehouse (in case they are built on top of the data warehouse)? (so it does not matter at all even we remove the data warehouse?)

Thank you very much in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Pl refer to BOL for detail explanation.

ROLAP - Data is in data warehouse (relational database). The data warehouse cannot be removed in this case

MOLAP - The data warehouse can be removed as both the data and aggregations are stored in the SSAS server. But if you want to see the updated data, then u may need the data warehouse.

HOLAP - The data warehouse cannot be removed in this case

|||

Hi, Arun,

Thank you very much for your kind and very helpful advices.

But the question is: in MOLAP mode, will it to a big extent the MOLAP cubes will slow down in processing once the underlying data warehouse is updated (in this case, any changes to the underlying data warehouse is informed to the MOLAP Cube, even we have set paralle cube partitions for that)

With best regards,

Yours sincerely,

|||

Depends on type of update you are performing. One way is to enable proactive caching to update the data based on some criteria - but the time taken depends on the amount of data read (plus also other criteria)

for ex, in one of our cubes, we use SQL CLR procs to perform incremental updates to partitions. It is very fast and takes only few seconds to update the MOLAP partitions. We trigger the sproc using messages from SQL broker.

|||

Hi, Arun,

Thank you very much for your kind advices. So as you kindly advised, in you case, you first of all perform updates to the source relational database partitions with SQL CLR procedures. Then again perform the updates to MOLAP partitions which takes only a few seconds. Is that right? It is a very interesting way to do that and I would say very advanced as well (assume I am right in understanding your advices for that.).

Thanks a lot again.

With best regards,

Yours sincerely,

|||

Its partially right. The updates to data warehouse (relational database) happens through many scenarios - SSIS packages, stored procedures etc.

But, I call a SQL server SP towards end of each of the events. That SP would post message to SQL broker Queue. (The purpose of doing this is to make all the process asynchronous). The target queue has an activation SP that will then invoke the neccesary CLR SP which would do all updates to SSAS.

The CLR SP uses AMO to do all updates to the cube. It also fires some XMLA queries to do some quick updates.

The above process works very well and the advantage is that its quite fast.

|||

Hi, Arun,

Thanks a lot for your kind advices and detailed explanation.

I guess it is really up to the specific problem. Anyway, very good advices.

Thanks again.

With best regards,

Yours sincerely,