ROLAP

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

Advantages of ROLAP Cube

• High data efficiency. It offers high data efficiency because query performance and access language are optimized particularly for the multidimensional data analysis.
• Scalability. This type of OLAP system offers scalability for managing large volumes of data, and even when the data is steadily increasing.

Disadvantages of ROLAP Cube

• Demand for higher resources: ROLAP needs high utilization of manpower, software, and hardware resources.
• Aggregately data limitations. ROLAP tools use SQL for all calculation of aggregate data. However, there are no set limits to the for handling computations.
• Slow query performance. Query performance in this model is slow when compared with MOLAP

Hybrid OLAP

HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can “drill through” from the cube into the underlying relational data

1. Aggregated or computed data is stored in a multidimensional OLAP cube
2. Detailed information is stored in a relational database.

MOLAP

MOLAP uses array-based multidimensional storage engines to display multidimensional views of data. Basically, they use an OLAP cube. Data is pre-computed,pre-summarized, and stored in a MOLAP (a major difference from ROLAP).

Using a MOLAP, a user can use multidimensional view data with different facets. Multidimensional data analysis is also possible if a relational database is used. By that would require querying data from multiple tables. On the contrary, MOLAP has all possible combinations of data already stored in a multidimensional array. MOLAP can access this data directly. Hence, MOLAP is faster compared to Relational Online Analytical Processing (ROLAP).

MOLAP Tools

1. Essbase – Tools from Oracle that has a multidimensional database.
2. Express Server – Web-based environment that runs on Oracle database.
3. Yellowfin – Business analytics tools for creating reports and dashboards.
4. Clear Analytics – Clear analytics is an Excel-based business solution.
5. SAP Business Intelligence – Business analytics solutions from SAP

Implementation considerations is MOLAP

• In MOLAP it’s essential to consider both maintenance and storage implications to creating strategy for building cubes.
• Proprietary languages used to query MOLAP. However, it involves extensive click and drag support for example MDX by Microsoft.
• Difficult to scale because the number and size of cubes required when dimensions increase.
• API’s should provide for probing the cubes.
• Data structure to support multiple subject areas of data analyses which data can be navigated and analyzed. When the navigation changes, the data structure needs to be physically reorganized.
• Need different skill set and tools for Database administrator to build, maintain the database.

MOLAP Advantages

• MOLAP can manage, analyze and store considerable amounts of multidimensional data.
• Fast Query Performance due to optimized storage, indexing, and caching.
• Smaller sizes of data as compared to the relational database.
• Automated computation of higher level of aggregates data.
• Help users to analyze larger, less-defined data.
• MOLAP is easier to the user that’s why It is a suitable model for inexperienced users.
• MOLAP cubes are built for fast data retrieval and are optimal for slicing and dicing operations.
• All calculations are pre-generated when the cube is created.

MOLAP Disadvantages

• One major weakness of MOLAP is that it is less scalable than ROLAP as it handles only a limited amount of data.
• The MOLAP also introduces data redundancy as it is resource intensive
• MOLAP Solutions may be lengthy, particularly on large data volumes.
• MOLAP products may face issues while updating and querying models when dimensions are more than ten.
• MOLAP is not capable of containing detailed data.
• The storage utilization can be low if the data set is highly scattered.
• It can handle the only limited amount of data therefore, it’s impossible to include a large amount of data in the cube itself.

Meet Ananth Tirumanur. Hi there 👋

I work on projects in data science, big data, data engineering, data modeling, software engineering, and system design.

• 👨‍💻 All of my projects are available at https://github.com/akrish1982
• 💬 Ask me about Data engineering, SQL, Databases, Data pipelines, Data infrastructure
• 📄 My work history: https://www.linkedin.com/in/ananthtirumanur/
• ⚡ Fun fact: Marathoner & Casual Birding enthusiast

Connect with me:

• Twitter: @akrish82
• LinkedIn: https://linkedin.com/in/ananthtirumanur/

My Resources:

• LinkedIn Newsletter: Data Engineering with AWS
• Udemy Course: AWS Certified Data Engineer Associate Practice Test
• Python Crash Course: Python Crash Course on Gumroad

Languages and Tools:

AWS, Bash, Docker, Elasticsearch, Git, Grafana, Hadoop, Hive, EMR, Glue, Athena, Lambda, Step Functions, Airflow/MWAA, DynamoDB, Kafka, Kubernetes, Linux, MariaDB, MySQL, Pandas, PostgreSQL, Python, Redis, Scala, SQLite