OLAP, ROLAP, MOLAP, HOLAP
What is OLAP?
OLAP (Online Analytical Processing) was introduced into the business intelligence (BI) space over 20 years ago, in a time where computer hardware and software technology weren’t nearly as powerful as they are today. OLAP introduced a groundbreaking way for business users (typically analysts) to easily perform multidimensional analysis of large volumes of business data.
Aggregating, grouping, and joining data are the most difficult types of queries for a relational database to process. The magic behind OLAP derives from its ability to pre-calculate and pre-aggregate data. Otherwise, end users would be spending most of their time waiting for query results to be returned by the database. However, it is also what causes OLAP-based solutions to be extremely rigid and IT-intensive.
Limitations of OLAP cubes
- OLAP requires restructuring of data into a star/snowflake schema
- There is a limited number of dimensions (fields) a single OLAP cube
- It is nearly impossible to access transactional data in the OLAP cube
- Changes to an OLAP cube requires a full update of the cube – a lengthy process
Vendors offer a variety of OLAP products that can be grouped into three categories: multidimensional OLAP (MOLAP), relational OLAP (ROLAP), and hybrid OLAP (HOLAP). Here is a breakdown of the differences between them.
What is ROLAP?
ROLAP stands for Relational Online Analytical Processing. ROLAP stores data in columns and rows (also known as relational tables) and retrieves the information on demand through user submitted queries. A ROLAP database can be accessed through complex SQL queries to calculate information. ROLAP can handle large data volumes, but the larger the data, the slower the processing times.
Because queries are made on-demand, ROLAP does not require the storage and pre-computation of information. However, the disadvantage of ROLAP implementations are the potential performance constraints and scalability limitations that result from large and inefficient join operations between large tables. Examples of popular ROLAP products include Metacube by Stanford Technology Group, Red Brick Warehouse by Red Brick Systems, and AXSYS Suite by Information Advantage.
What is MOLAP?
MOLAP stands for Multidimensional Online Analytical Processing. MOLAP uses a multidimensional cube that accesses stored data through various combinations. Data is pre-computed, pre-summarized, and stored (a difference from ROLAP, where queries are served on-demand).
A multicube approach has proved successful in MOLAP products. In this approach, a series of dense, small, precalculated cubes make up a hypercube. Tools that incorporate MOLAP include Oracle Essbase, IBM Cognos, and Apache Kylin.
Its simple interface makes MOLAP easy to use, even for inexperienced users. Its speedy data retrieval makes it the best for “slicing and dicing” operations. One major disadvantage of MOLAP is that it is less scalable than ROLAP, as it can handle a limited amount of data.
What is HOLAP?
HOLAP stands for Hybrid Online Analytical Processing. As the name suggests, the HOLAP storage mode connects attributes of both MOLAP and ROLAP. Since HOLAP involves storing part of your data in a ROLAP store and another part in a MOLAP store, developers get the benefits of both.
With this use of the two OLAPs, the data is stored in both multidimensional databases and relational databases. The decision to access one of the databases depends on which is most appropriate for the requested processing application or type. This setup allows much more flexibility for handling data. For theoretical processing, the data is stored in a multidimensional database. For heavy processing, the data is stored in a relational database.
Microsoft Analysis Services and SAP AG BI Accelerator are products that run off HOLAP.
A Quick Guide to Database Technologies
Sisense and Elasticubes
Similar to OLAP-based solutions, Sisense is a Business Intelligence software designed to enable solutions where multiple business users perform ad-hoc data analysis on a centralized data repository. On the other hand, Sisense does not achieve this by pre-calculating query results, but rather by utilizing state-of-the-art technology called ElastiCube. It is a sophisticated columnar database, which was specifically designed for Business Intelligence solutions. Its unique storage and memory processing technology radically change the way business intelligence solutions access data.
Powered by ElastiCube, Sisense delivers distinct advantages over OLAP-based solutions:
- Instant query response times, without pre-calculation or pre-aggregation of data
- Creation of complicated star/snow flake schemas is not required
- A data warehouse is not required, but easily supported
- There are no physical limits to the number of dimensions an ElastiCube can hold
- ElastiCube provides access to data in any granularity (not merely to aggregated data)
- Changes to ElastiCubes can be done without re-building the entire data model
- An ElastiCube requires significantly less powerful hardware than a similar OLAP cube
