Category: sql server analysisservices


juanvg2109 on Thu, 23 Mar 2017 10:27:21


I am newbie in SSAS. I have heard that you can work in MOLAP or ROLAP mode in SSAS. 

MOLAP: create a physical cube with all aggreations precalculated.

ROLAP: create a logical cube and the aggregations are calculated on-line in the SQL.

I have designed a cube. I have built and deployed it. I would like to know which option use to have a MOLAP Cube or a ROLAP cube,

Anyhelp will be greatly appreciated.



DIEGOCTN on Thu, 23 Mar 2017 10:48:33

Go to SSMS ==> Connect Analysis Service ==> Your Project (Expand) ==> Right click on the cube ==> Property==>Proactive  Caching:

Please mark as answer if this post helped you

philfactor on Thu, 23 Mar 2017 14:08:50

If you review the partition storage mode documentation, it should help you decide which choice (MOLAP, ROLAP, HOLAP) is best to use for your particular requirements and environment:
Partition Storage Modes and Processing

Hope that helps,

Phil Streiff, MCDBA, MCITP, MCSA

Darren Gosbell on Thu, 23 Mar 2017 19:44:58

Start with MOLAP. It's the most common choice and gives the best query time performance and subsequently the best end user experience.

The main reason to go ROLAP are if you need near real time results (ie. within a minute or two of the data hitting the relational source you need to see it in the cube) and you need a super fast and well tuned and designed back end system to cope with the large aggregate queries.

In 99% of cases the answer to "should I use ROLAP?" is no. And in 18 years I've never seen anyone use HOLAP in a production solution (the query performance is too inconsistent). 

juanvg2109 on Thu, 23 Mar 2017 21:46:10

I am not sure if MOLAP is best solution because I have fact tabla with 10 millions rows and 20 dims, two of them with 100.000 rows. I think the number of aggregates is very high and the cube processing will take long...

Darren Gosbell on Thu, 23 Mar 2017 22:30:09

I don't see any real issues with those figures, the fact table is actually on the small side. A well tuned system should be capable of processing at least 50,000 rows per second which means you can do a full process of 10 million records in under 3 and a half minutes. I've seen some slow big data systems which could only deliver 2,000 rows per second, but even then this would process in 83 minutes which should not be an issue if you are doing nightly processing.

So the question is - would your rather do this big query once overnight to load the MOLAP storage or potentially have to scan 10 million records in real time every single time a user drags an element onto a pivot table when using ROLAP? 

juanvg2109 on Thu, 23 Mar 2017 22:51:04

Thanks Darren, 

My question not only go for fact table. My cube has 20 dims, which means a lot of combinantions and agregations. As i I said some of the dims are not small (100.000). It suppose a lot of cells in the hypercube and its upper levels.., this is the problem I told.

I will test performance...