In this article, you’ll learn what the differences are between OLTP and OLAP and how they connect to SAP.
OLTP and OLAP both describe how a database is used.
Get or update your fundamental SAP knowledge here.
So without further ado, let’s do this.
- How to OLAP vs. OLTP and SAP
- What Is a Database?
- What Is a Database Management System?
- What Is a Relational Database Management System?
- What Is a Data Warehouse?
- What Is Online Transaction Processing?
- What Is Online Analytical Processing?
- Differences between OLTP and OLAP
- OLTP, OLAP, and SAP
How to OLAP vs. OLTP and SAP
First, we need to define these terms.
OLTP stands for Online Transaction Processing, and OLAP stands for Online Analytical Processing.
As these names indicate, the difference between them is how they are used.
OLTP and OLAP describe two different types of databases and two different ways of processing data.
Because the data is used differently, it is also structured differently in the database.
OLTP is designed for processing operational requests in a database.
These are the kinds of requests to the database from applications used in standard business operations, such as maintaining sales orders.
OLAP is designed for processing analytical requests in a database.
These are the kinds of requests to the database from applications used for business intelligence and data mining.
A business intelligence application provides information about sales orders, such as the aggregated net value for a specific timeframe.
To understand the differences between OLTP and OLAP and how they function in SAP, you’ll need to begin by understanding these terms:
- DB (Database)
- DBMS (Database Management System)
- RDBMS (Relational Database Management System)
- DW (Data Warehouse)
- OLTP (Online Transaction Processing)
- OLAP (Online Analytical Processing)
What Is a Database?
A database (DB), also called a database system, is a collection of structured data and software to access that data (the DBMS, or database management system).
Typically, the data is stored electronically.
A database system serves users and applications to:
- save high volumes of data in a way that is
- provide data subsets in
- different presentation forms
- needs-based presentation forms
A database system consists of two parts:
- the actual database in a narrow sense, and
- a database management system (DBMS).
The actual database consists of just the data in a database system, which is managed by the DBMS.
Although the entire database system is often simply called a database.
Let’s look at the example of a zoo.
You want to save all the data about the zoo:
- animal names,
- zoo areas,
- zookeeper wages, and
This is the actual data that resides in the database.
To store and retrieve the data, a DBMS is necessary.
What Is a Database Management System?
A DBMS (Database Management System) is the administration software of a database system that manages the database that contains the actual data.
Whenever someone wants to add information to the database or retrieve information from it, they make a request to the DBMS.
- takes the request,
- translates it,
- and executes it, modifying or retrieving data as requested.
The DBMS organizes the structured storage of the data and controls all reading and writing access to the database.
To manage and request data, a database system provides a database language, such as SQL (Structured Query Language).
In order to make a request of the DBMS, they must format their request in the language that the particular DBMS uses.
Here are ten examples of database management systems:
- SAP Sybase ASE
- SAP HANA
- Microsoft SQL Server
- Microsoft Access
- Oracle RDBMS
- IBM DB2
- Amazon RDS
Let’s go back to our zoo example.
You have an application to manage the zoo animals, and you add a new animal in the application:
- The application takes the provided animal data and sends it to the application server.
- The application server translates the request to save the data into the database system into the DBMS language and then sends it to the DBMS.
- Finally, the DBMS puts the animal data into the database.
What Is a Relational Database Management System?
An RDBMS (Relational Database Management System) is a DBMS that stores data in a relational structure.
This means that the data is grouped in logical units in tables, and those units link to each other using keys.
The tables have relations with each other through the keys.
DBMSs are made to retrieve and store data with the presence of redundant data.
RDBMSs are made to retrieve and store data on a data basis without the presence of redundant data.
Let’s say that you would like to save the zoo animals according to their zoo area.
In a so-called flat and non-relational database, you would save all animals and their zoo area together in one table, like this:
|ID||Animal Type||Zoo Area|
But in a relational database, you would save the animals and the zoo areas as logical units in separate tables.
Each entry in the animals table would have a key that points to an entry in the zoo areas table.
The keys relate to each animal entry with an entry in the zoo areas table.
The process of structuring data that belongs logically together in a database into separated tables is called normalization.
Here is the animals table with relations via the zoo area ids to the zoo areas table:
|ID||Zoo Area ID||Animal|
And the zoo areas table:
There are two key advantages of a relational database over a flat database that saves all data in one table:
- reduced data redundancy, and
- improved data integrity.
Let’s say that you would like to add zookeepers to the database, along with the animals and zoo areas.
You want this to include information about who is responsible for which animal and therefore works in which zoo area.
In a flat (not relational) database, you would have to extend your one table with the data about the zookeepers.
The table already holds the data about your animals and zoo areas. The table gets more and more packed, complex, and redundant.
In a relational database, you would just add a new table for the zookeepers, and use a key to relate the entries of the zoo area tables to the zookeeper table.
The key relates each entry in the zoo area table with an entry in the zookeeper table:
|ID||Zoo Area ID||Zoo Keeper|
Now you know which zookeeper is responsible for which zoo area through the relation of the zookeeper table to the zoo area table.
Plus, you know which zookeeper is responsible for which animal through the relation of the zookeeper table through the zoo area table to the animal table.
In comparison to a DBMS and one single table, an RDBMS and its multiple related tables are less packed, less complex, and do not have any redundancies.
Tables in an RDBMS are well structured and make sense for a human through their logical groupings, such as animals, zoo areas, and zookeepers.
Relational database systems are the most common database systems.
What Is a Data Warehouse?
A DW (Data Warehouse) is at its core just another relational database. It is a relational database that is structured to fit the needs of analytical requests.
A data warehouse extracts, transforms, structures, and finally stores data from operational databases and external sources.
A data warehouse is made for analytical applications.
A data warehouse supports analytical applications in
- data consolidation,
- and reporting, including the aggregation of data.
In turn, these analytical applications are made for decision support.
Let’s go back to the zoo.
You are thinking about closing your zoo for a month because you want to go on vacation to Hawaii.
However, you are not sure which month would be the best month to close your zoo in terms of revenue.
You use an analytical application that is connected to a DW to get the net value of all sold zoo tickets for each month over the past two years.
Then you can pick the month with the least net value of sold tickets for your vacation to Hawaii.
What Is Online Transaction Processing?
OLTP (Online Transaction Processing) describes databases that are optimized for operational requests.
These requests are by far the most frequently used in business operations, such as managing sales and other interactions.
Operational requests are not requests from applications that are used for analytics such as decision support.
An OLTP database is not a DW.
An OLTP database is made to
- process high volumes of requests,
- respond without delay to requests,
- process requests as fast as possible, and
- maintain the data integrity despite multiple simultaneous requests.
The effectiveness of an OLTP database is measured in processed transactions per second.
Therefore, efficient operation is the most important feature of an OLTP database.
OLTP systems are designed to serve data to operational applications that access the database by providing fast and consistent processing of the applications’ requests.
As the name says, the requests to an OLTP database are
- transactions, and
- processed online.
The “Transaction” in OLTP
A transaction is a request to a relational database and is a logical unit.
This means that after the request is processed successfully, the request leaves the database consistent.
Consistent, in turn, means that the data is changed only in a way allowed by the database, so that data integrity remains intact.
Data integrity within a relational database is maintained if the
- attributes are within their value ranges,
- primary keys are unique,
- primary keys are not empty,
- foreign keys are either empty or an equivalent primary exists, and
- custom integrity rules are fulfilled.
Either a transaction successfully executes, or it is rolled back until the database has the same status as if the transaction had never been executed.
Therefore, if a transaction is aborted during its processing, then all the transaction changes in the database until its abortion must be undone.
For example, the zoo has vacations for the animals.
At each zoo area’s entry is a note with the vacation times of the zoo area.
Each zoo area and the animals inside of it get four weeks of vacation in a year, during which the area is closed to visitors.
The zoo area is opened again when the vacation ends.
Let’s take some liberties and say that the animals can decide on their own when they want their zoo area closed so they can take their vacations.
The hawk from the desert zoo area decides to take two weeks of vacation from January 01, 2019 to January 14, 2019.
The hawk communicates its vacation to Michael, the zookeeper for the zoo’s mountain area.
Michael then records the vacation in the mountain area’s vacation note.
The transaction could look formal like this:
start of the transaction. read the field remaining vacations for Chad the hawk. write January 01, 2019 into the field vacation from for Chad the hawk. write January 14, 2019 into the field vacation to for Chad the hawk. end of the transaction.
That is a valid transaction that leaves the vacation note in the database consistent.
But imagine that the above transaction gets aborted before Michael has written to the “vacation to” field: the zoo area gets closed but never reopened again.
If this note were left in the database and its instructions followed, this would cause some problems.
The aborted transaction would leave the note inconsistent.
The transaction actually would be no transaction, because of its inconsistent effects on the note.
The invalid transaction would be a mere request to the note.
When a transaction is aborted, all its changes done in the database must be rolled back to retain the consistency of the database.
Let’s look at another scenario: it’s now June, and Chad the hawk took two weeks of vacation in January, and another two in April.
If zookeeper Michael did not read the field “remaining vacation” before entering any vacation requests, Chad the hawk could take as much vacation as he wants, even though he’s already used up his four weeks of vacation per year.
The transaction again would not be a transaction but a mere request, because the request would not be consistent.
The transaction would violate the custom integrity rules of the note, which say that a zoo area has overall only four weeks vacation in a year.
The “Online” in OLTP
The “online” in OLTP means that the transactions in an OLTP system are processed in real-time, without any delay.
The data is processed as soon as the request is made and gives an answer as fast as possible.
An ATM is a good example: as soon as you confirm your PIN code, the ATM checks immediately if the entered PIN code is correct, and then allows you to proceed with your transactions.
The opposite of real-time is batch processing.
In a batch processing system, the requests are processed in bulk at a later time. That means that the answer to the batch processing is provided at a later time as well.
An example of this is a program to optimize images: during the day, new images get uploaded by users to a server, and at night, when the workload of the server is lower, the program optimizes the new images on the server in a batch.
The results are then available the next day when the processing is complete.
Real-time processing requires constant user interaction: the user interacts with an application, the application fires the request up to the database, the database processes the request, and it fires a response back to the application.
Batch processing requires user interaction once, or no user interaction at all.
The user starts the batch process, and the system processes the batch until it is finished.
The other option is that the user schedules a batch request, and the system processes the batch automatically.
The naming “batch processing” comes from the 60s/70s.
In this time, data and programs were stored on punch cards, and those punch cards were read and processed in batches, often at night.
What Is Online Analytical Processing?
OLAP (Online Analytical Processing) describes databases that are optimized for analytical requests.
These are requests from applications that are used for decision support, such as an application to analyze the aggregated net value of sales orders per month or the average empty seats on flights overall.
An OLAP database is not an operative database, but a database structured for analytics that extracts data from operative databases and then organizes that data in a different way.
As the name says, the requests to an OLAP system are
- analytical, and
- processed online.
The “Analytical” in OLAP
An OLAP database is made to analyze data and thus to answer questions about the data.
For example, you, as the zoo owner, would like to know all about your zoo, such as:
- What is the net value of all sold tickets for last year?
- What is the average amount of food that each zoo animal has eaten last month?
- Who is the zookeeper with the most overtime overall?
An OLAP database is a data warehouse.
A DW extracts, transforms, and stores data from operational databases or external sources.
Imagine a DW or OLAP database providing an additional layer on top of the operational database.
An advantage of an OLAP database is that it is separated from the operational databases.
Therefore, it does not use any of the computation power of an operational database to answer analytical requests and thus does not slow down the day-to-day-business applications.
The “Online” in OLAP
The online in OLAP means the same as online in OLTP: the requests to the database are processed in real-time, without any delay.
Data is processed as soon as the request is made, and the results of the requests are made available as quickly as possible.
Differences between OLTP and OLAP
|Describes||Structure of an RDBMS database for operational requests||Structure of an RDBMS database for analytical requests|
|Data Source||Original data source||Extracts data from OLTP databases and external sources|
|Purpose||Day-to-day-business requests to run the fundamental business||Analytical requests for decision support, planning, and problem solving|
|Request Types||Create, read, update, and delete||Read|
|Request Aims||Operational such as update the price of the item with the ID 1||Analytical such as what is the net value of all sold items in 2018|
|Performance Scope||High volume of transactions||High volume of data|
|Data Structure||Normalized data||Denormalized data|
|Data Set Age||6 - 18 months||2 - 7 years|
|Request Complexity||Mostly short and simple requests that are standardized||Mostly long and complex requests that contain aggregations|
OLTP, OLAP, and SAP
OLTP databases are used in SAP ERP systems, such as the R/3 and ECC.
Data from these databases have traditionally been extracted into OLAP databases, used in SAP NetWeaver Business Warehouses.
But the future is bright in the world of SAP: SAP’s own database called HANA combines the functions of OLTP and OLAP systems in a single database.
HANA is an in-memory database.
In-memory means that the data does not reside on a hard drive, to be loaded into the memory only when it is needed for processing or analysis.
Rather, it resides all the time in memory.
Because the data resides permanently in memory, and therefore no hard drive access is necessary, a HANA database is super fast.
Therefore, OLTP and OLAP requests can be processed altogether in a single HANA database.
SAP’s newest generation of ERP and CRM applications use HANA, such as
- SAP S/4HANA (ERP), or
- SAP Sales Cloud (CRM).