The data warehouse systems tools which help users in making data analysis and decision making process easier are OLAP systems. These systems meet the user needs by presenting data in various formats according to the requirement. The OLAP systems provide a multidimensional view of data from data warehouses and data marts. It does not concern where and how the data is stored, it just presents the user with every possible view of data.
It gives us deeper understanding and knowledge about data through faster and consistent access to data. The type of analysis using OLAP tools varies from basic “slicing” and “dicing” methods to advance complex analysis such as time series.
OLAP tools are highly preferred from other query processing tools as OLAP tools can also answer questions about data such as “what if” and “why” , rather than just ”who “ and “what” typically answered by general query tools.
However, there lies a concern in the architecture of OLAP servers. Its implementation must consider the data storage issues in OLAP Servers. The implementation of OLAP servers include: ROLAP, MOLAP and HOLAP. These architectures were defined by Berson and Smith in 1997 and Pends and Greeth in 2001. This categorization is based on architecture and processing of multidimensional data.
Let us understand each of these.
MOLAP (Multidimensional Online Analytical Processing)
In MOLAP the data is organized in multidimensional data cube. The MOLAP tools use advance data structures and multidimensional database management systems (MDDBMS) to organize the data. The data views are multidimensional represented through array-based multidimensional storage engines.
The data is aggregated and summarized and stored according to predicted usage. This helps in enhanced query performance through fast indexing on precomputed data. The storage in MOLAP data cubes may be sparse or dense, so compression techniques need to be applied to handle sparse data. Thus, to optimize storage utilization, two-level storage representation to handle the dense and sparse data. The dense data sets are represented as array in data cubes and sparse datasets use compression technique for efficient storage. MOLAP provides data from Multidimensional database systems.
ROLAP (Relational Online Analytical Processing) servers
The ROLAP servers lie between relational database backend and client front end tools. The data warehouse is managed through relational database management system using metadata layer. ROLAP employs optimization techniques for backend database, it uses aggregation techniques and it is more scalable than MOLAP.
ROLAP servers use complex SQL queries for multi-dimensional analysis. The SQL queries “Where” is equivalent to “Slicing” and “Dicing” operations in ROLAP. ROLAP provides data directly from data warehouse. It can store large volumes of data compared to MOLAP.
HOLAP (Hybrid Online Analytical Processing) servers
Hybrid OLAP servers combine the ROLAP and MOLAP servers to provide analysis capability. It provides benefit of greater scalability of ROLAP and faster computation of MOLAP server. The large volumes of detailed data are stored in relational database and aggregations are kept in separate MOLAP server.
It either uses RDBMS product or use an intermediate MOLAP server for analysis. The data is delivered directly to the system either through DBMS directly or intermediate MOLAP. It is the fastest growing OLAP server.
DOLAP (Desktop Online Analytical Processing) servers
DOLAP servers store the data in client-based files. The multidimensional processing takes place using client multi-dimensional engine. The data volume is comparatively smaller and may be distributed in advance or on demand. The database administration of data cube is done by central server or processing routine.
Issues related to various OLAP architectures
1) MOLAP
Only a limited amount of data can be efficiently stored and analyzed.
Navigation and analysis of data are limited because the data is designed according to previously determined requirements.
MOLAP products require a different set of skills and tools to build and maintain the database.
2) ROLAP
Performance problems associated with the processing of complex queries that require multiple passes through the relational data.
Development of middleware to facilitate the development of multi-dimensional applications.
Development of an option to create persistent multi-dimensional structures, together with facilities to assist in the administration of these structures.
3) HOLAP
The architecture results in significant data redundancy and may cause problems for networks that support many users.
Ability of each user to build a custom data cube may cause a lack of data consistency among users.
Only a limited amount of data can be efficiently maintained.
4) DOLAP
Provision of appropriate security controls to support all parts of the DOLAP environment.
Reduction in the effort involved in deploying and maintaining the DOLAP tools.
Current trends are towards thin client machines.
Difference between MOLAP vs ROLAP vs HOLAP
| MOLAP | ROLAP | HOLAP |
| The aggregated and partitioned data is stored as indexed views in the relational database | The aggregated data and partitioned data is stored as multidimensional structure | The aggregated data of the partition are stored in a multidimensional structure in an SQL Server Analysis Services instance. |
| It has faster query response time due to aggregated and summarized data views | The query response time is generally slower | Query response time will differ accoding to data storage and access. If the data is to retrieved from relational database it will not be as fast as it would be if data is stored in MOLAP. With HOLAP, the query response times will differ. |
| The storage investment is little higher in MOLAP as it stores copy of relational data on OLAP server. | The data size is ROLAP does not have any limitation on it. It can handle huge volume of data, | The data cube partitions are smaller in size than MOLAP cubes and partitions. |
| Using MOLAP the data can be viewed even if the data source is not available as a local copy is stored | The data cannot be viewed if the data source is not available. | It is a hybrid approach to MOLAP and ROLAP, so which technique is used will determine the data access. |
Conclusion
In todays article we learned difference between MOLAP vs ROLAP vs HOLAP. When data in data warehouse is stored in form of relational data storage, it is called relational online analytical processing while multidimensional data storage models are called MOLAP. When data is stored as combination of both approaches, it is called hybrid online analytical processing.
⇓ Subscribe Us ⇓
If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:
Happy Testing!!!
