ROLAP dead but not forgotten

With a catchy name like Relational On Line Analytical Processing, is it any wonder that this technology hasn’t really entered mainstream use. Firstly what is it?

OLAP (On Line Analytical Processing) is  a data store organised for ad hoc query and analysis, a good way to think about this is as a very big Excel PivotTable which can be shared by many users.  The Relational refers to the fact that the data resides in a conventional relational database as opposed to the other kind – MOLAP (Multi-Dimensional) OLAP. In MOLAP the data store is structured differently and as the name suggest based on the dimensions the  data relates to using a coordinate system.  There is also HOLAP (Hybrid) which mixes the first two. 

So why have two ways of doing this?  Traditionally ROLAP has been able to handle much larger volumes of data while MOLAP is faster because MOLAP makes extensive use of RAM to store the dimensions and aggregations, as will as sophisticated indexes. In the case of SQL Server Analysis Services there is also some very clever compression which uses a bit more cpu usage but less slow moving IO. 

HOLAP tries to provide the best of both worlds by storing the most frequently referenced data in MOLAP and the rest in ROLAP. For example you might have your current year’s sales in MOLAP and prior years in ROLAP.

So in very large BI projects ROLAP has it’s place and some vendors only use this technology, while Microsoft offers all three flavours of OLAP in Analysis Services, with a common interface to use whichever one you need.  

When it comes to database and BI scale, Microsoft also has the SQL Customer Advisory Team (SQLCAT) and they have just published a paper on ROLAP best practice .  This whitepaper is pretty deep, but with a bit of work the SQLCAT team managed to get ROLAP to out perform MOLAP on the same data using the same hardware in certain cases.  However the work involved was considerable so I am not suggesting for a minute that you should stop using MOLAP as I find MOLAP solutions far easier to build and maintain. 

If you just want to know what to do for your ROLAP solution, rather then the details of the why, then the recommendations are summarised at the end of the paper.

Technorati Tags: sql server,rolap,molap,holap,BI,business intelligence