6.3 OLAP, MOLAP, ROLAP, and HOLAP

---

On-line Analytical Processing (OLAP) is technology used to build and maintain data in a multidimensional format, such as a cube. The source data is often stored in an underlying relational database in traditional rows and columns, and the cube is built on top of that.

MOLAP is the name given to conventional cube or multidimensional OLAP structures. This offers fast query performance since the data is prebuilt in the cube format, but often requires mass storage to store the aggregations-'data explosion' as it is called.

ROLAP is seen to be a more scalable solution and enables the data to remain in the SQL Server tables, with a skeletal cube structure is built to house the aggregations.

HOLAP is the use of a hybrid storage structure-that is, data that combines both ROLAP and MOLAP data. A good example of a hybrid solution is the use of regular reports, which analyze geographical data, but occasionally need to drill down into product-detail data. The geographical data is placed into a MOLAP cube and the product data placed into a ROLAP cube.

SLOWLAP is what you get when you do it wrong. OK, sorry about that-but I always have to get that pun in when talking about OLAP.

Table 6.1 lists the advantages and disadvantages of these technologies.

Table 6.1: MOLAP? ROLAP? HOLAP? Which One Do I Use?

	MOLAP	ROLAP	HOLAP
Advantages	Data navigation, slicing, and analysis are quicker since the dimensions have all been precalculated and stored	Data resides in the original data source, allowing changes to be reflected quicker Cubes are quicker to process	Best of both worlds since the structure can be tuned to the business requirements for optimized performance
Disadvantages	Cube may take a while to process initially Cube needs to be managed on a regular basis as new data come into the data warehouse.	Data navigation may be slower	Administration and management may be cumbersome

The cube is the central object in a multidimensional database containing dimensions and measure-dimensions being derived from underlying tables and columns and measures being the quantitative data derived from the columns. Dimensions should be distinct categories added to the cube. Measures are generally time periods or geographically-based metrics, which are often contained in a hierarchical structure-for example, hours roll into days, which roll into weeks.

The cube can hold a number of aggregations that can dramatically improve the efficiency and response time of a query. The scope of the aggregations can be massive, and the designer of a data warehouse needs to offset performance against storage space.

---