ROLAP and MOLAP Definitions 1

[Skittle]

Another fiddly little question.

I have tried to get my head round the meaning of ROLAP and MOLAP variations of OLAP.

I am learning about data from a SQL Server 2008 point of view which may have some bearing on the definitions of ROLAP and MOLAP.

I believe that ROLAP is an OLAP cube that overlays traditional relational data tables as it's source. ROLAP cubes are therefore relatively slow as the cube definition has to reformat the data on the fly to represent an OLAP cube.

I believe MOLAP is an OLAP cube that overlays star schema representation of data where the reorganizing of the data has already been performed as a periodic task so that the OLAP cube has less work to do on the fly when the data interacts with it. MOLAP is therefore relatively fast but needs more disc space because you have to hold the data in your relational tables and your data mart star schemas as a proper data warehouse.

Can anybody offer opinions on whether or not I have got it right?


Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[RiverGuy]

ROLAP is stored in a relational database system. It can be SQL Server or any other relational database. You could create a star schema and put a tool like Business Objects or SSRS on top of it and you'd have a ROLAP data warehouse system.

MOLAP is stored in a multidimensional database system. It can be SSAS or Hyperion or another multidimensional DB.

SSAS provides you with two main components -- the UDM, which is your metadata layer built upon your datawarehouse, and the multidimensional database. The UDM can store data MOLAP, ROLAP or a combination of the two.

MOLAP is generally faster than ROLAP for larger aggregations. It won't be faster than ROLAP every time, and may even be slower in some circumstances.

When SSAS stores something in the MOLAP database, it's compressed. So if you created a proper MOLAP database against a star schema, you're not going to be doubling the size of your star schema. You might add an extra ten percent for example.

 

[Skittle]

Hmmm.
I'me even more confused now which is probably a sign of my January blues.

I thought a ROLAP configuration refered to a set of standard normalised or snowflake database tables with a metadata overlay ( such as the SSAS UDM ) to present the data as OLAP.

I thought MOLAP is a set of star schema database tables with a more efficient metadata overlay ( again such as SSAS UDM ) to present the data as OLAP.


Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[RiverGuy]

Your ROLAP definition is what I said above.

MOLAP does not require a star schema. It's not simply a more efficient metadata overlay -- it's an additional storage and logic engine besides the source data warehouse. It's not a set of summary, aggregated tables -- it's a completely different type of database. It's not a SELECT * FROM SomeTable type of database--it isn't SQL. The concept of rows and columns do not apply. The source is typically a relational star schema.

 

[blom0344]

I would not spend too much time on breaking your head on the (X)OLAP semantics. I believe that other variations do surface regularly. Makes great ammunition for web discussions, but beyond that , has little use..

Ties Blom

 

[RiverGuy]

Well, it's important to understand a technology when making a decision, so I have to disagree blom. In this example, it's important to understand how choosing ROLAP or MOLAP affects things such as returning atomic-level data to the client. It's a lot easier to do in ROLAP than MOLAP. But there is not always a need to drill into atomic data. It's important to understand that MOLAP uses a completely different storage mechanism from ROLAP, that it's not just another set of tables and more server resources may need to be provisioned.

 

[blom0344]

The technology is about understanding relational versus dimensional. The whole (X)OLAP labels that are used tend to cloud discussions more then they clarify things as people tend to have different ideas about what each labels means.

Your own clarifications in this topic are all that matters. Tagging the concepts does not make them more understandable. That was the point I wanted to make ..

Ties Blom

 

[MDXer]

ROLAP MOLAP and HOLAP are simply storage modes for your data. Some products are one or the other where MS SSAS allows you to choose.

Simply put

MOLAP (Multidimension OLAP)
Data is preaggregated and stored in a non RDMS file system. MOLAP storage tends to produce the fastest cubes when queried but also can take longer to process. MOLAP storage is what most people are talking about when they Talk Cubes. Products that use MOLAP are SSAS, Essbase, Applix and Cognos Hyper Cubes.

ROLAP (Relational OLAP)
Data is stored in a RDMS data store so queries are served from Tables and the over head of joins and various other RDMS bottle necks can be present in a Rolap Solution. Rolap Cube solutions can be Faster to process, depending on the technology or product used. Products that use Rolap are SSAS, Cognos, Business Objects, Micro Strategy. Some Rolap products such as BO allow you to simply define your model and will build related tables for various aggregates.

HOLAP (Hybrid OLAP)
Is nothing more than a mixed storage mode. HOLAP can be approached from 2 different ways which may seem opposite but are based on data demand.

First would be on cubes that maintain a defined process schedule such as nightly, weekly or monthly. Where the most recent data is frequently the most relevant data and accessed the most and therefore requires faster query performance. This data you would place in a MOLAP structure and old less needed data you would migrate to ROLAP at some predefined point in time x Days, Weeks, Months or Years. Since historical data is not accessed as often beyond the predefined point the performace requirement may be seen to be not as important.

The second and somewhat opposite approach would be in a more Realtime implementation where you can't afford the overhead of processing the cube structure every x Second, Minutes or Hour. In the scenario you would implemnt the most recent data in a ROLAP structure and during a standard process window would then process the realtime data in to a MOLAP structure.

Don't Let the Multidimension in MOLAP confuse you as most OLAP implementations are based on a Dimensional model. The approact to build an efficient Cube design would be pretty much the same regardless of the storage mode in SSAS.

I do remember from my Cognos days that... Well I prefer MOLAP cubes and SSAS as the platform.

I migrated a product from Cognos (both their MOLAP and ROLAP offerings) to be a pure SSAS solution. In that project we had reports that took so long to run we scheduled them to build in the early morning, I remember one being close to 2 hours and a cube was 17 hours to process). The SSAS solution processed in 20 minutes with no query taking longer than 3 seconds and +90% were sub second response times.

 

[blom0344]

MDX, this is interesting stuff.. I am very much interested in the reason why you had such better performance with SSAS. We are currently switching from traditional Cognos cubes to the new DMR (Dimensionally Modelled Relational) approach. This can be seen as a ROLAP solution. With our applications we can safely work with this due to the small datasets. However, larger sets of data should be handled likewise in the future, so performance could then be an issue.

Ties Blom

 

[MDXer]

I will start a thread later today discussing the differences as I have experienced. I will do my best to get this done. I have been away from here for about 6 months for health related issues but am trying to get back and contribute.