Part 02: Multidimensional data structures for OLAP

The article about traditional multidimensional databases is a summary of different blogs and explanation of myself.

Definions

MOLAP is a “multidimensional online analytical processing”. ‘MOLAP’ is the ‘classic’ form of OLAP and is sometimes referred to as just OLAP. But it is wrong to call it OLAP. MOLAP stores data in an optimized multi-dimensional array storage, rather than in a relational database. Therefore it requires the pre-computation and storage of information in the cube – the operation known as processing. MOLAP tools generally utilize a pre-calculated data set referred to as a data cube. The data cube contains all the possible answers to a given range of questions. MOLAP tools have a very fast response time and the ability to quickly write back data into the data set. They access the data not via SQL but via MDX. The problem is that every multidimensional database has its own MDX-“standard”.

ROLAP bases on relational databases. The base data and the dimension tables are stored as relational tables. It depends on a specialized schema design. This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP’s slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a “WHERE” clause in the SQL statement. ROLAP tools do not use pre-calculated data cubes but instead pose the query to the standard relational database and its tables in order to bring back the data required to answer the question. ROLAP tools feature the ability to ask any question because the methodology does not limit to the contents of a cube. ROLAP also has the ability to drill down to the lowest level of detail in the database.

There is no clear agreement across the industry as to what constitutes HOLAP (“Hybrid OLAP”), except that a database will divide data between relational and specialized storage. For example, for some vendors, a HOLAP database will use relational tables to hold the larger quantities of detailed data, and use specialized storage for at least some aspects of the smaller quantities of more-aggregate or less-detailed data. HOLAP addresses the shortcomings of MOLAP and ROLAP by combining the capabilities of both approaches. HOLAP tools can utilize both pre-calculated cubes and relational data sources.

Advantages of MOLAP
– Fast query performance due to optimized storage, multidimensional indexing and caching.
– Smaller on-disk size of data compared to data stored in relational database due to compression techniques.
– Automated computation of higher level aggregates of the data.

Advantages of ROLAP
– Precalculation is not necessary and does not need extra time
– Scaling of data volumes is better
– Drill Down to detailed data is possible

Comparison
Each type has certain benefits, although there is disagreement about the specifics of the benefits between providers.

Some MOLAP implementations are prone to database explosion, a phenomenon causing vast amounts of storage space to be used by MOLAP databases when certain common conditions are met: high number of dimensions, pre-calculated results and sparse multidimensional data.

MOLAP generally delivers better performance due to specialized indexing and storage optimizations. MOLAP also needs less storage space compared to ROLAP because the specialized storage typically includes compression techniques.[16]

ROLAP is generally more scalable.[16] However, large volume pre-processing is difficult to implement efficiently so it is frequently skipped. ROLAP query performance can therefore suffer tremendously. Since ROLAP relies more on the database to perform calculations, it has more limitations in the specialized functions it can use.

HOLAP encompasses a range of solutions that attempt to mix the best of ROLAP and MOLAP. It can generally pre-process swiftly, scale well, and offer good function Support.

 

About Tobias Riedner
Tobias Riedner foundet WYCDWD.com in 2015. He works and worked as innovator, consultant, analyst and educator in the fields of business intelligence and data warehousing. He learned a lot from the best consultants, managers und educators in the past and shares his knowledge worldwide. He works for a steady growing traditional company which is a leader in industry 4.0.