Talk:ROLAP

ROLAP vs. MOLAP

ROLAP vs. MOLAP is a complicated discussion. Please do not add opinions to this article -- please respect the Neutral Point of View.

This discussion is even more complicated by the fact that there are many ROLAP and MOLAP products, and they are all different. Therefore limitations of some particular ROLAP or MOLAP tool should not be generalized to the whole subject area. Please only state the facts and omit speculations and opinions.

Discussion between Arcann and Wikiolap

Specifically, many MOLAP proponents like to state that ROLAP suffers from poor query performance. However, they generally admit that ROLAP is more scalable. However, more scalable implies that ROLAP will in fact have better performance at large scales. Arcann 16:30, 29 December 2005 (UTC)

Actually, ROLAP scalability usually means that ROLAP handles large data volumes well. Once MOLAP tool has processed the data, it is generally considered to have better performance.

Please sign your posts in the discussion. In my experience, MOLAP tools do not have good query performance when dimensions have millions of members. They have even worse query performance if you try and use more than 10 dimensions. Lastly, MOLAP tools have difficulties with sparse dimensions where the MDBMS spreads the data across too many data cells. Arcann 14:49, 31 December 2005 (UTC)

How can I sign posts ? Perhaps we can arrange a chat to clear out the issues and work the differences ? I certainly would like to avoid frustration from your latest comment. I agree that MOLAP tools are not as good as ROLSAP with million member dimensions - this is exactly the comment I put in the first place ! However, I disagree about number of dimensions - I built myself model with almost 300 dimensions in MOLAP, and it worked great. The term sparse dimensions is specific to Essbase, more precisely to older Essbase BSO. In Essbase 7 ASO, as well as other MOLAP products, such as MS AS - there is no such term or limitation.

To: 71.112.84.115

Four ~'s will automatically become your signature. This may not work well without an account.

From the other edits you have made, I'm assuming your experience is mostly with MS AS. Mine is mostly with Essbase. My company also has very complex dimensional models, and large amounts of data. We have found that even with ASO we need to limit cubes to 10 dimensions or the cubes take hours to load and query performance is miserable.

The term sparse dimensions may have been coined by Essbase, but the concept applies to many MOLAP storage models -- again perhaps not MS AS.

Re performance: The studies I have seen have shown indicate that ROLAP can perform as well as MOLAP for any dimensional model, if the correct aggregates are built (within milliseconds of being as fast; network overhead on the database calls slows it down by a few milliseconds). The new SQL enhancements are also mitigating the need to create as many aggregates to get the same benefit. Finally, there are many companies out there with large volumes of complex data. ROLAP will most likely give them better performance, so it cannot be said that MOLAP always give better performance. It is better to explain the situation, and why there are performance differences. I have tried to do that to some extent but you have been deleting my edits.

Oh, and please... the correct grammar is "worse than" not "worse then". Not that I think the statement should be included at all.

Arcann 04:22, 2 January 2006 (UTC)

To: Arcann

Thanks, I now created account, so user previously known as 71.112.84.115 is now known as wikiolap

My point exactly - since the products are so different, it is hard to generalize about MOLAP products and ROLAP products. It could be that two MOLAP products are more different then one MOLAP and one ROLAP product. I agree that it is possible to tune ROLAP in specific models to perform as fast as MOLAP. I also agree about SQL OLAP extensions - in fact, I myself added this point ! However the general experience is usually not like that. I base my claims on results from OLAP Survey - from 1 to 5. Consistently, the best performer is TM1, which is classic MOLAP, and the worst is Microstrategy, which is classic ROLAP. Same surveys also reveal that ROLAP products handle much large data volumes - hence my comments that ROLAP scales better. So please, help us to reword this statement better, I am not hanging on particular wording, as long as the idea is there.

I did not mean to blantly delete your edits. But to me complex models mean models with a lot of calculations, financial, budgeting, planning etc models. I would argue that MOLAP performs better on these models to the dergee that ROLAP is probably is not even practial there. If you meant models with more complex relational schema, then I agree with you - ROLAP is more suitable then MOLAP. I appologize for previously deleting your edits without explanation. Wikiolap 20:57, 2 January 2006 (UTC)

To Arcann: And please - Don't_bite_the_newbies - I will learn from my mistakes :)

This conversation is getting too deeply indented. I am starting some new threads below. Arcann 19:01, 3 January 2006 (UTC)

Question for Wikiolap re different granularities

Can you explain what you mean by this statement: "ability to derive results from queries to different granularities" ? Arcann 14:49, 31 December 2005 (UTC)

I think you are right - some ROLAP products can do it too.

Performance Surveys

Link to OLAP survey seems to be broken. --125.238.20.170 (talk) 00:05, 25 July 2009 (UTC)-

I am working on a change to the ROLAP document to address the performance discussion. Hopefully you will find it conveys your thoughts as well as mine. Arcann 19:01, 3 January 2006 (UTC)

I still feel that your edit is somewhat biased. For example, OLAP Survey (not that this is different from OLAP Report, even though done by the same person) found that ROLAP solutions scale to larger data volumes - and it is OK to list as advantage. However, the biggest disadvantage of ROLAP, found both by OLAP Survey and experienced by many practicioners somehow did not end up in the Disadvantages section. Your edit also almost discredits OLAP Survey findings, even though it did took into account complexity of models. I think it is fair to add that many practicioners/experts agree that ROLAP tends to be slower, although it is also possible to optimize it, especially with the most advanced tools. Wikiolap 07:43, 4 January 2006 (UTC)

And here is what the OLAP authority, Nigel Pendse, says about ROLAP in OLAP Report: [1] - "Products in this sector draw all their data and metadata in a standard RDBMS, with none being stored in any external files. They are capable of dealing with very large data volumes, but are complex and expensive to implement, have a slow query performance and are incapable of performing complex financial calculations." Wikiolap 07:00, 5 January 2006 (UTC)

I tried to list some of the reasons that ROLAP can suffer from poor performance. I think that is more useful than a blanket statement that ROLAP has slower performance. You have previously said that ROLAP be optimized to have the same performance as MOLAP. So the blanket statment is not true. The same page you linked to also says "not one ROLAP vendor has made a profit to date." A quick look at MicroStrategy's financials show that to be false. The OLAP Report Market share analysis shows MicroStrategy as profitable, and "likely to be found in the largest organizations" (versus "Microsoft Analysis Services is typically chosen by smaller organizations"). Nigel Pendse offers some great information, but is not perfect and is not completely without bias himself. Arcann 18:31, 5 January 2006 (UTC)

Of course nobody is without bias - neither Nigel, nor me, nor you. However NPOV suggests that if significant number of people and/or prominent people (like Nigel) have certain POV - it has to be presented. I agree that blanket statement is not good. I will try to come up with better edit. I am probably going to use an example the following edit from you: "ROLAP tools are better at handling non-aggregatable facts or (e.g. textual descriptions). MOLAP tools tend to suffer from slow performance when quering these elements". Wikiolap 19:49, 5 January 2006 (UTC)

I look forward to seeing your edit. I did try to present what Nigel says about performance. It's not strictly speaking in the disadvantages section, but it is right after it. I originally had it in disadvantages but I wanted more room to explain. Ultimately I feel that even Nigel is being inconsistent in saying that ROLAP is more scalable and yet has slower performance. By definition more scalalble means that at large scales ROLAP performs better than MOLAP. Many companies choose ROLAP for it's better scalability -- So even though they might not invest enough money in database hardware to get < 10 second response time from the tool, it is better than what they would have gotten from a MOLAP solution. Arcann 01:41, 6 January 2006 (UTC)

Complex Models

Yes, by complex models I meant complex relational schemas, which in turn create complex dimensional models. I'm using complex as a catch all phrase to include high dimensional cardinality, number of hierarchies, depth of hierarchies, inter-relationships between hierarchies, slowly changing dimensions, and a variety of relational models that can't be perfectly modeled into a MDBMS. It has been my experience that MicroStrategy (ROLAP) handles these things better than Essbase/Hyperion (MOLAP). Arcann 19:01, 3 January 2006 (UTC)

I have to disagree again that MOLAP is worse in handling many dimensions, many attributes, deep hierarchies, SCD etc. However, I do agree that with MOLAP it is usually harder, and sometimes not possible to handle complex relational schemas which are very far from star or snowflake, and ROLAP can take those. Degenerate dimensions are also probably best handled by ROLAP. (BTW, things like many-to-many dimension relationships used to be ROLAP domain only, but MS AS 2005 can handle these now). But for the balance, we should compensate it with saying that other type of complex models - calculation rich models (even based on simple star schemas) used for budgeting, planning, statuary reporting or complex analytics rules are better handled by MOLAP. I think you would agree that this is domain of such traditional MOLAP tools as TM1 and Essbase. Wikiolap 07:43, 4 January 2006 (UTC)

Currently many MOLAP tools offer better built in financial calculations than ROLAP tools. Originally, the ROLAP tools depended entirely on the database to perform all calculations. That limited the set of operators and functions that were available. However, at least MicroStrategy, now supports post processing of the database results. They now have the foundation on which to build the missing financial functions. So this is less of a ROLAP vs MOLAP difference and more of a difference in development priorities given the different industries that each has tended to serve (retail v.s. financial). Arcann 18:51, 5 January 2006 (UTC)

I think you yourself edited MSTR as an example of HOLAP tool :) - which I think is true especially after introduction of "Intelligent Cubes" technology.

Yes, I did add that, and it is true that MSTR introduced the post processing capability at the same time as the ability to store the results in a MOLAP cube. However, I don't feel that the post processing itself qualified as HOLAP. In other words a hypothetical ROLAP tool that just did the post processing could implement all the financial functions without using MOLAP. Arcann 21:15, 5 January 2006 (UTC)

Microsoft Analysis Services

MS AS 2005 is a great MOLAP engine, but it lacks a solid user interface for analysis / reporting -- which is a critial part of a OLAP solution. I have a hard time comparing it to tools like the Hyperion or MicroStrategy suites. Arcann 18:51, 5 January 2006 (UTC)

This is kind of unrelated to the main discussion, but MS AS has literaly hundrends of 3rd party UIs for both analysis and reporting. There is a list (partial) at http://www.mosha.com/msolap/util.htm. Wikiolap 19:49, 5 January 2006 (UTC)

Sorry it was very off topic. I was just curious to see what you thought. Hopefully someday soon I will get a chance to play with some of the UIs using MS AS. I do think MicroSoft would be in a better position for comparison to the other tools if they had a strong UI of thier own. Right now it's a lot like trying to do a test drive comparison between two complete cars, and an engine that fits in many frames. Which frame do you pick to put it in?

If you will have a chance to see OLAP support in Excel 12 (it's in Beta now) - you will see that Microsoft is getting there. Excel 12 has pretty competitive OLAP UI support. Wikiolap 22:11, 5 January 2006 (UTC)

 How can Analysis Services be considered a ROLAP tool? Doesn't it uses cubes? From what I understand cubes are MOLAP not ROLAP.

Cubes can have either MOLAP or ROLAP storage modes (and also HOLAP), and Analysis Services supports both (all three). Wikiolap (talk) 03:34, 19 February 2008 (UTC)

Perhaps I'm missing something. From what I understand MOLAP = cubes, while ROLAP = relational tables (i.e Star/Snowflake schema's). My basic understanding is that Analysis Services is a tool that generates cubes, not relational tables. Are these assumptions not correct?

Yes, your assumptions are not correct. OLAP means working with cubes. MOLAP and ROLAP refer to storage modes of the cube. Wikiolap (talk) 19:44, 19 February 2008 (UTC)

Thanks for the clarification. So what does someone call a denormalized database, built for reporting purposes, that doesn't contain cubes? Is that simply just a data warehouse? If it's not OLAP, then is it still considered OLTP? The reason that I ask people tend to use the terms data warehouse and OLAP interchangeably.

I do agree that MS do not have ROLAP capacity. MS tools work only on MOLAP. OLAP can be express by some objets like cube it is MOLAP or by relational star schema it is ROLAP.

Tableau sofware

Tableau sofware is not ROLAP, this tool build it propritary cube and never go back to the database to query the ROLAP

Merge discussion

Propose merging MOLAP with ROLAP.

• They basically, if not completely, have the same text. Namely: a comparision between the two.
• Target page is ROLAP, arbitrarily.

-DePiep (talk) 12:25, 22 September 2010 (UTC).

Propose putting both under OLAP. 20:12, 8 October 2010 Althotos (signing addded, -DePiep (talk) 18:24, 5 January 2011 (UTC))

No! They are fundamentally different. Both use a hypercube and star schema, but one needs an engine that supports this, the other is hosted on a COTS RDBMS platform. Merging would destroy this contrast. The fact that WP's current article might not make this absolutely clear is to be remedied, not seen as supporting evidence for a merge. Andy Dingley (talk) 20:41, 8 October 2010 (UTC)

Merging does not imply that they are the same. After merging, the specific descriptions are still needed (as they are needed now). And both DWs can very well be implemented on a RDBMS machine. For the Multidimensional you also can use a cube-oriented application (BI-side), but both are functioning well with RDBMS-SQL. The fundamental difference you point at, is the presence of 3NF and up in the database-design. This is a difference that can well be covered in the text. After merging, the comparision (which is now about 85% of the text, in both articles, and mostly the same text), that comparision can and will stay. -DePiep (talk) 18:37, 5 January 2011 (UTC)

Of course, the new title should not stay "ROLAP"... -DePiep (talk) 18:41, 5 January 2011 (UTC)

How about creating a single page called MOLAP vs. ROLAP, moving the two separate subsections there, and keeping the MOLAP and ROLAP entries for their specific descriptions? Certainly it seems redundant to have two separate entries for M vs. R and R vs. M. It's the same points that are made with the language simply being inverted.

Tlaresch (talk) 17:06, 7 April 2011 (UTC)

Support merging into OLAP.--Dbrukman (talk) 18:21, 2 October 2012 (UTC)