In order to fully understand OLTP and OLAP, it's necessary to provide a bit of context. In the early days of software existence, data was typically stored in a single file. However, as it began to address bigger problems, relational database management systems (DBMS) took the market by storm. Throughout the following decades, those were everyone's solution's for data storage.

With the advent of the web, everything changed massively. Search engines and social networks are now modelling data in domains in which relations between data are not easily identifiable or, sometimes, not even needed (e.g. search engines indexing documents).

Nevertheless, a few terms of the old world are still used today, and it's important to look at them with a modern approach. Two of them are, precisely, OLTP and OLAP. But for an overall context, let's take a look at the following image that shows the relation between OLTP and OLAP.

Table of contents

    ➤  The relationship between OLTP and OLAP
    ➤  What is OLTP?
    ➤  What is OLAP?
    ➤  OLTP vs OLAP: key differences
    ➤  OLTP vs OLAP: putting it all together
    ➤  Conclusion

The relationship between OLTP and OLAP

Before we identify the key differences between OLTP vs OLAP, let's first provide an overall context regarding their relation. Take a look at the following image that shows the relation between OLTP and OLAP.

The image above underlines is that OLTP and OLAP are not competitors approaches to the same issue, but rather processes that complement each other. In general, OLTP systems provide the source data to the data warehouses, while OLAP systems help to analyze the data.

Next, you'll find a more in-depth explanation of each of those terms, followed by a description of how OLTP and OLAP complement each other.

What is OLTP?

The term OLTP refers to Online Transaction Processing. It's often used to mention databases that store and manage relevant data to the day-to-day operations of a system or company. In the past, this term was usually linked to relational databases in operation, where the main focus was to gather data from what was happening in a given context.

In short: OLTP is used to store and manage data for day-to-day operations.

As the information is stored on an OLTP data store was often critical to the business, a huge effort was put to ensure the Atomicity, Consistency, Isolation and Durability (ACID) of the data. Data stored according to these four principles are marked as ACID compliant, and this is where relational database management systems excel.

But having an ACID compliant Datastore does not mean that we don't have to make any additional effort to ensure our data is compliant with those principles: the way we process the data matters. For instance, how can we guarantee that data is consistent if we allow redundancy in our data store?

If we are storing clients' addresses, it's important to ensure that when the client moves to another place, the address is updated everywhere. But storing addresses in several places makes it hard to keep the data in a consistent state. This is why relational databases are often designed to match the 5th normal form - a way of designing relational data that avoids redundancy.

As said previously, the world has changed since the OLTP term was defined, and nowadays, it is easy to store data on non-relational databases. Most of those data stores comply only with some of the four principles of ACID. Depending on the use case, it's OK to relax on one or more of these principles in exchange for other benefits (speed, scalability, etc.).

OLTP Example

For example, if we are storing "likes" in a post on a social network, is it really important to ensure that the number of likes is 100% accurate? Or is it OK to display 995 likes instead of 998 in exchange for a faster response to millions of users?

As OLTP refers to Online Transaction Processing, we see that the term is not bounded to relational databases or even fully compliant ACID databases. It simply refers to the way these data stores are used. If, for instance, we're using a document data storage (e.g. MongoDB) to store and process data from the day-to-day operations of a social app (e.g. to register users, store likes, etc.), we are OK to say that it's OLTP.

What is OLAP?

The term OLAP refers to Online Analytical Processing, and is often used to mention databases that store and manage relevant data for analysis and decision making.

OLAP is strongly connected to Business Intelligence (BI), a specialization of software development targeted at delivering applications for business analysis. In other words, the objective of BI is to allow top-level executives to query and explore data without the help of involving IT staff.

In short: OLAP is used to analyze data and make decisions.

The biggest advance that this area has brought was the capacity to generate reports on the fly. It ended the need to call the IT department to ask for a custom report, or to automate the generation of specific reports. A BI system can now answer questions that the developers did not have the need to know in advance that the question was going to be asked.

BI systems are made possible by organizing the data in a form called Hypercube. This form explores the many dimensions of the data, and allows users to aggregate or drill down data by navigating the dimensions of the cube.

The fun part is that, with the right interface, top-level management can generate reports on the fly, without the help of IT.

OLAP systems can be implemented using relational databases (e.g. MySQL), and this technique is often named ROLAP (Relational OLAP). But, for that, we need to design the database not in the 5th normal form but in the 3rd normal form.

We can live with redundant data when analyzing data. What really matters is the capacity to navigate through the dimensions of the data. This is where ROLAP shines, as a database schema in the 3rd normal form is suited for aggregations and drill downs.

OLTP vs OLAP: key differences

As said before, while OLTP provides an instant report of business activity, OLAP, on the other hand, focuses on generating data analysis and insights from the compiled data. OLTP and OLAP complement each other because OLAP's insights are only as good as the data pipeline that results from the OLTP.

Find out the key differences between OLTP and OLAP in the following table:

OLTP vs OLAP: putting it all together

When encountering the terms OLTP and OLAP for the first time, it's easy to question which one is better, when in fact, one should be asking: how does one complement the other?

We now know that:

• OLTP is used to store and manage data for day-to-day operations;
• OLAP is used to analyze that data.

This is exactly how they are used in an existing business.

The data from the upper part of the above example (HR Database, CRM, Billing System) is usually processed in batch - often overnight - via a process called Extract, Transform and Load (ETL).

What is ETL and how does it join OLTP and OLAP?

ETL is the name given to the operation that collects data from several OLTP sources and puts it in an OLAP data warehouse, allowing cross-system analysis. In the lower part of the above figure, you can see that the data was properly stored and organized in the OLAP cube.

That way, the people performing the analysis can work with up to date information and make timely decisions without disrupting operations.

Conclusion

This article explained the main differences between OLAP vs OLTP and how they complement each other.

Every day new data is acquired; however, we need to organize and analyze that same data to make informed decisions and retrieve valuable insights. Therefore, an organization usually has two types of data processing abilities: OLTP and OLAP.

As we have described throughout the article, both OLTP and OLAP play a crucial role regarding data, despite their different approaches towards it. We hope you have found this post useful!

Found this article useful? You might like these ones too!

• The broken window to the developer’s soul
• Learn how to go from capybara-webkit to Headless Chrome and ChromeDriver
• All you need to know about digital products’ maintenance