Comparison between - ROLAP vs MOLAP

[Demetrius]

hello,

Many of you must be knowing about OnLine Analytical Processing (OLAP). I heard there are two variants of OLAP - which are ROLAP and MOLAP.
I want to know the comparison between the two ?

thank you

[Reegan]

ROLAP means Relational OnLine Analytical Processing

The underlying data in this model is stored in relational databases.
Since the data is stored in relational databases this model gives the appearance of traditional OLAP’s slicing and dicing functionality.


MOLAP means Multidimensional OnLine Analytical Processing

This is the traditional mode in OLAP analysis. In MOLAP data is stored in form of multidimensional cubes and not in relational databases.

[Reegan]

ROLAP

Advatages -
The advantages of this model is it can handle a large amount of data and can leverage all the functionalities of the relational database.

Disadvantages -
The disadvantages are that the performance is slow and each ROLAP report is an SQL query with all the limitations of the genre. It is also limited by SQL functionalities

ROLAP vendors have tried to mitigate this problem by building into the tool out-of-the-box complex functions as well as providing the users with an ability to define their own functions.

MOLAP

Advantages -
The advantages of this mode is that it provides excellent query performance and the cubes are built for fast data retrieval. All calculations are pre-generated when the cube is created and can be easily applied while querying data.

Disadvantages -
The disadvantages of this model are that it can handle only a limited amount of data. Since all calculations have been pre-built when the cube was created, the cube cannot be derived from a large volume of data. This deficiency can be bypassed by including only summary level calculations while constructing the cube. This model also requires huge additional investment as cube technology is proprietary and the knowledge base may not exist in the organization.

[MindSpace]

Statistical Differences

These are the statistical differences between the two methods:

Old Cube Setup

ROLAP
At 50% performance gain, 235 aggregations @ 3,434.6Mb
At 75% performance gain, 351 aggregations @ 16,509.7Mb
At 100% performance gain, 3624 aggregations @ 258,271.7Mb

MOLAP
At 50% performance gain, 235 aggregations @ 1,412.2Mb
At 75% performance gain, 351 aggregations @ 6,562.4Mb
At 100% performance gain, 3624 aggregations @ 99,826.5Mb


New Cube Setup

ROLAP
At 50% performance gain, 120 aggregations @ 1,052.9Mb
At 75% performance gain, 189 aggregations @ 5,700.9Mb
At 100% performance gain, 2079 aggregations @ 91,681.4Mb

MOLAP
At 50% performance gain, 120 aggregations @ 546.6Mb
At 75% performance gain, 189 aggregations @ 2,921.8Mb
At 100% performance gain, 2079 aggregations @ 45,820.4Mb

[Katty]

To the end user, ROLAP and MOLAP are transparent. The front ends for these tools are similar, and the types of decision support activities are roughly the same. There are, however, significant differences between the operational details of ROLAP and MOLAP that are of primary concern to the data warehouse designer. The biggest difference between ROLAP and MOLAP involves the tradeoff between speed and flexibility (see Figure 5.16). MOLAP engines, by virtue of their pre-summarization and loading, have the data ready to display and give the end user incredibly fast response times. ROLAP engines, by virtue of their ad hoc data extraction and summarization, give end users incredible flexibility in their choices of queries. This is the very heart of the difference between ROLAP and MOLAP--speed versus flexibility.