Articles | May 16, 2022

Everything you need to know about RDBMS vs. OLAP

Raghav Vashisht

This article will explore the two big players in the data processing game- OLAP and RDBMS.

Different people have different opinions, understanding, and biases. Let us make a vis-a-vis comparison of the two technologies, OLAP and RDBMS.

Part 1: Let us start with some definitions (yawn…)

DBMS

Database Management System refers to any sort of database.

RDBMS

Relational DBMS is the most commonly used database-e.g., Oracle, SQL Server and is characterized by the organization of data into tables, with primary keys that allow for unique identification of rows in each table and foreign keys that allow tables to relate to other tables.

The only non-relational databases in common use are NoSQL databases, e.g. MongoDB.

OLTP

Online Transaction Processing is the predominant use case for an RDBMS. RDBMS design often emphasizes data normalization for the sake of efficient transaction processing.

OLAP

Online Analytical Processing is an analytical activity like count, aggregation, mean, sum, etc., generally performed on databases that have been de-normalized to facilitate OLAP.

These de-normalized databases are often referred to as data warehouses. There are different variations of OLAP that exist –MOLAP, ROLAP, or HOLAP.

Data lake, Data warehouse, and Data marts

Before diving deep into the RDBS and OLAP comparison, let us try and have a sense of the bigger picture around data lakes, data warehouses, and data marts.

Data warehouse

Data warehouses will frequently work in conjunction with an operational data store (ODS) to ‘warehouse’ data captured by the various databases used by the business.

The method of extracting data from the database, transforming it in the ODS, and loading it into the data warehouse is an example of the extract-transform-load (ETL) process or the similar ELT process.

Data Mart

A data mart is very similar to a data warehouse. Like a data warehouse, the data mart will maintain and house cleaned data ready for analysis. However, unlike a data warehouse, the scope of visibility is limited.

A data mart supplies subject-oriented data necessary to support a specific business unit.

Data Lake

A data lake stores an organization’s raw and processed (unstructured and structured) data at both large and small scales.

Unlike a data warehouse or database, a data lake captures anything the organization deems valuable for future use. With the advent of machine learning, more and more companies are trying to build data lakes.

A data lake can store literally any type of data – images, videos, PDFs, anything!

Part 2: The interesting part — Going beyond the definitions

In the next couple of sections, we will now try to unravel OLAP and RDBMS -understand how they work, going through the pros and cons of each one.

OLAP

Online analytical processing (OLAP) is a technology that organizes large business databases and supports complex analysis.

It can be used to perform complex analytical queries without negatively affecting transactional systems.

OLAP Cube

An OLAP cube is a data structure that overcomes the limitations of relational databases by providing rapid analysis of data.

OLAP cubes can display and sum large amounts of data while also providing users with searchable access to any data points so that the data can be rolled up, sliced, and diced as needed to handle the widest variety of questions that are relevant to a user’s area of interest.

Cubes can display and sum large amounts of data while also providing users with searchable access to any data points.

OLAP cubes can also be used to handle streaming data. Also, there have been advances around making a hybrid of OLAP and OLTP.

How to Query OLAP Cubes? What is MDX?

Multidimensional Expressions (MDX) is a query language for online analytical processing (OLAP) using a database management system. Much like SQL, it is a query language for OLAP cubes.

MDX provides a specialized syntax for querying and manipulating the multidimensional data stored in OLAP cubes. It is also a calculation language with syntax similar to spreadsheet formulas.

While it is possible to translate some of MDX syntax into traditional SQL, it would frequently require the synthesis of clumsy SQL expressions, even for very simple MDX expressions.

So SQL is not exclusively for RDBM Systems; check out this article to learn how OLAP queries can be implemented in SQL.

For example — The CUBE operator computes a union of GROUP BY’s on every subset of the specified attribute types.

RDBMS

A Relational database is a kind of management system in which tables are used to store data. Once the data is stored inside the table, it can be retrieved anytime.

What are examples of Relational databases?

These tables use primary keys and foreign keys to make them interrelated to each other. Thus, it saves time to a greater extent.

Some examples of relational databases are Oracle, SQL Server, DB2, and Access.

In a relational database, relationships between data items are expressed using tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence.

How to Query RDBMS?

A database management system (DBMS) that incorporates the relational-data model, normally including a Structured Query Language (SQL) application programming interface.

So in conclusion

An RDBMS would be a spreadsheet and a cube would be a Pivotable. Cube allows you to define dimensions (things you’d need to filter on like Dates,regions), hierarchies (eg months within a year, countries for Continents,…) and measures and rules on how these measures relate with the dimensions.

Part 3: The Juxtaposition

In the last part of this article, we will start with a vis-a-vis comparison of OLAP and RDBMS. Then we will go through the pros and cons of each of them. And finally, we will talk about how to take the OLAP cubes to the next level with Atoti.

OLAP vs. RDBMS: Summary

Pros and Cons of Relational Database

Advantages of Using OLAP

Business Focused: OLAP enables one to organize data in a multidimensional model that makes it easy for business users to understand the data and to use it in a business context, such as a budget.
Pre-Calculated Aggregations: OLAP systems are so swift because they pre-aggregate values that would need to be computed on the fly in a traditional relational database system. In an OLAP system, the analytic capabilities are independent of how the data is presented. The analytic calculations are centrally stored in the metadata for the system, not in each report.
Trustworthy Data and Calculations: Unlike RDBMS,OLAP systems centralize data and calculations, ensuring a single source of data for all end users. OLAP systems ensure end users have access to consistently defined data and calculations to support BI (Single Source of Truth).
Flexible, Self-Service Reporting: The best report designers and builders usually come from within the business community itself because they know what is needed. OLAP systems enable business users to query data and create reports using tools that are natural for them to use.
Speed of thought Analysis/Ad Hoc Analysis: OLAP systems respond much faster to end-user queries than relational databases that do not capitalize on OLAP technology. Quick response times are possible because OLAP systems pre-aggregate data. Pre-aggregation means that there is no need for many time-consuming calculations when an end-user query is processed. In addition, OLAP systems are optimized for business calculations, so calculations take less time to execute.
”What-if” Scenarios: You can analyze not only the actual data but also create different “what-if” scenarios and change the data you work with while also ensuring the actual cube data is not overridden or lost.
Flat Learning Curve: Working with OLAP can be more simplistic because it works on more business hierarchies and levels and hence resonates with the business user.

atoti augments the advantages of OLAP and diminishes its disadvantages.

Disadvantages of using OLAP

Memory considerations: If you run all data warehouse extraction, transformation, and load (ETL) operations and OLAP cube functions on one server, carefully consider the memory needs of the operating system, data warehouse, and SSAS to ensure that the server can handle all the data-intensive operations that can run concurrently. This is especially important because processing OLAP cubes is a memory-intensive operation.
OLAP is relational: The main problem of such a system is that the structure must be defined in advance. It means the number of columns in the table and data types should be precalculated before table creation. For quick results, it can cause some difficulties.
Computation capability: Some systems provide lack of computational power. That greatly reduces the flexibility of the OLAP tool. Analyzers are limited to a narrow and small area, unable to analyze freely, and even have to resort to a third party to perform this kind of calculation. In such business computing, OLAP is often left in an awkward situation.

Part 4: Atoti & OLAP

If you planning to do a more business-focused BI, OLAP could be the preferred tool.

How can you take the OLAP cube to the next level with Atoti? Let us go through the features of Atoti and see how it augments the advantages of OLAP and diminishes its disadvantages.

Lets us see how Atoti augments OLAP

Flat Learning Curve: Atoti is backed up by an intuitive UI, where you can perform most functions by a simple drag and drop.
2. ”What-if” Scenarios: You can create multiple what-if scenarios and compare them side by side.

3. Business Focussed: Atoti is focused on the needs of the business. It gives you the flexibility to play with the data.

4. Security: One of the main concerns around OLAP is security and access control. With Atoti+, you can control which user has control over which type of data.

5. Memory considerations: Atoti can optimize your existing computing capacity to do superfast calculations. Atoti also supports almost all cloud-based computing to perform analytics on Big Data in real-time.