Home > Data Warehousing > MOLAP, ROLAP and HOLAP

MOLAP, ROLAP and HOLAP


Recently a friend of mine asked me the definitions of and differences between MOLAP, ROLAP and HOLAP. I had never pondered over these questions formally and had assumed that my passive knowledge was the right knowledge. I shared my passive knowledge with my friend with a disclaimer that formal defintions and differences could have more to them.

Fortunately for me, my passive knowledge was not too far from the formal definitions. While, one can google for these terms and can get to the details directly; I will summarize the definitions/differences as they make the most sense to me and the professionals who gel well with my style 🙂

Here it is:

MOLAP (Multidimensional OLAP): The technology of creating reports/dashboards/adhoc-analytics based on multi-dimensional storage. Whatever you ask for (even after group-bys and wheres) has been precalculated and stored in the data source you are accessing. Example: When PerformancePoint is used to create reports of an SSAS cube, you are doing MOLAP.

ROLAP (Relational OLAP): In formal computer science, relations are not relationships, they are synonyms for tables. Also in formal computer science, relationships are just constraints. ROLAP is the technology of creating reports/dashboards/adhoc-analytics based on relational-storage (data stored in traditional database tables). If whatever you ask for, needs to be fetched using a set of SQL queries with wheres, joins, group-bys, unions, havings, etc, you are using a ROLAP solution. Example: When SSRS reports use a dataset that is making a stored procedure call for a DataSet, you are doing ROLAP.

HOLAP (Hybrid OLAP): When your reports/dashboards/adhoc-analytics use both MOLAP and ROLAP technologies, your end-product is using HOLAP. MOLAP is used mainly for aggregated data and the details are usually fetched using ROLAP.  HOLAP is used a lot in reports/dashboards with drill-through/drill-down/sub-reports features. A sharepoint deployment with PPS reports built on SSAS cubes and SSRS reports built using DB-stored-procedure calls, is also a HOLAP solution.

If possible, go MOLAP. If not, do HOLAP. Never settle for just ROLAP unless you are doing trivial things or you have an enviable hardware.

———————————————————————————————————————————

As a side note for more mere mortals of software engineering (those who don’t know much about databases and those who don’t care much about databases), MOLAP vs. ROLAP is analogous to Hash-tables vs. Arrays 🙂

———————————————————————————————————————————

Advertisements
  1. Siva
    May 7, 2012 at 6:05 pm

    Nice explanation. Is PowerPivot MOLAP or HOLAP?

  2. May 7, 2012 at 10:00 pm

    Thanks Siva! MOLAP implementation has many complexities involved; it can be fully disc-based, partially disc-based or fully in-memory solution. The best I can guess (without an extensive look into PowerPivot’s Vertipag Engine’s internals) at this time is that PowerPivot is an in-memory MOLAP solution.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: