What is the difference between OLAP and OLTP systems

There are two types of database applications in application development that we need to know. This article will tell you the similarities and differences between these two types of systems.

Both OLTP (on-line transactional processing) and OLAP (on-line analytical processing) are used in professional systems, especially in data warehousing and analysis. They make up two different sides: analyzing, storing data and manipulating data.

Penetrating into the database world, it's important that you understand the differences between the types of databases used. No matter which direction you go, the basic understanding of the difference between OLAP and OLTP will help you better understand. In this article, I'll explain what each type of system does and how to use each.

What is OLTP?

OLTP stands for On-line transactional processing which means processing online transactions. OLTP systems are classical systems that process transaction data. They are around us. In banks, ATMs or computer systems used by tellers who store transactions, an OLTP system usually uses a database. If you text someone from a smartphone, you're working on another OLTP system. The cash register at the supermarket near you also runs to another OLTP system.

In short, most business applications are OLTP. It's hard to imagine computer technology without some OLTP systems. Indeed, the entire computer science is developed around the management of orderly transactions.

We consider a very important part in the OLTP term: the T, which stands for transactional. Transactions are important and keeping them organized is important.

What helps transactions be organized? That is, transaction databases must be stable (or continuous, not easily changed), independent, homogeneous and atomic (Atomic, Consistent, Isolated and Durable - ACID).

Simply stated, this type of transaction ensures that activities performed by different users do not affect each other. For example, if the husband and wife each perform a withdrawal from a joint account, the atomic nature of the transaction will ensure that they do not withdraw more than the amount of the account.

An OLTP system needs to make sure all work in order. When you go to the bank you believe that your money will be sent to your account and not be confused with the transaction or other account or lost.

What is OLAP?

OLAP stands for on-line analytical processing: processing online analytics, telling us what OLAP systems do. An OLAP system for efficient data analysis.

Unlike OLTP, OLAP systems work with huge amounts of data. So the priority for accuracy and integrity of transactions is not as high as OLTP. OLAP allows you to find trends and crisis numbers and help us see the big picture. These systems have a smaller group of users than OLTP. For example, you will not interact with your bank's OLAP system because it is not related to your account's transaction logging system.

An OLAP prediction system often uses a large data set, the interaction lasts longer. And there are many situations where we won't know what that interaction is and what it looks like. Some of the queries are small, but most often have large sizes and take a lot of time to execute. In contrast, OLTP often uses common statements like INSERT and DELETE, we have a good idea and the result is a complete and time-consuming statement.

The size of the system also helps distinguish the role between OLTP and OLAP. OLTP must be stable and fast (to serve tasks immediately) while OLAP must be large and powerful enough to analyze all business data.

OLTP and OLAP

Let's analyze the difference between OLAP and OLTP in the actual example:

Example of an OLTP application in the real world

Remember, OLTP queries must be as simple as possible and smaller than OLAP systems. We need to execute OLTP queries quickly and for small amounts of data. For example we look at a financial payment system.

We can see a few basic tables containing customer data, contracts, and successful payments. We also see tables that store contact information and a bridge table called contract_number_type. Other table defines contract_types (eg savings account, check account ...), while the contract_bridge_type contains bridging information)

An OLTP system, data is gathered in small lots. An easy-to-understand example is like a highway with many small cars, sports cars running fast and unrelated.

When adding new data to the OLTP system, we often work with a small set of records and do not affect other records. For example, suppose we enter a customer's payment into the database. First we need to verify customer account information. Then enter the payment amount, date and other information. When designing an OLTP system, we need to think about the processing frequency, small data. And we must be able to support multiple users at the same time.

Reference to our financial system, now need to add a $ 5000 payment to account 1112 for customers with an ID of 11.

Because this transaction is being processed, many other transactions are also being done at the same time. In the design of OLTP systems, this does not matter. No transactions were broken, everything had to run ok and OLTP's "highway" still secured it.

Now let's look at an OLTP query, which will display user-related data at the atomic level. Suppose someone with the customer code "11" wants to see the last 10 transactions:

SELECT pm.*
FROM (SELECT ROW_NUMBER() OVER (ORDER BY payment_date DESC) AS rows, p.*
    FROM payment p
    WHERE customer_id = 11 ) pm
WHERE rows <= 10

Or maybe with that customer but they want to see the contract information in the file:

SELECT contracts.*
FROM contract ct
INNER JOIN customer cs ON ct.customer_id = cs.id
WHERE ct.id = 11

The important thing to remember here is that queries are working with lightweight, fast and atomic models.

Examples of OLAP applications in the real world

This model is a star model. In this model, we can see dim_customer dimentional tables for customers, dim_contract for contracts and dim_date for date data. There is a central table of fact_balance. This table contains balance information for a specific time

Data cannot be entered into OLAP in an OLTP manner. Instead of getting bit by bit information like OLTP, OLAP is transmitting data in large numbers by querying files that scan the entire data source (usually from an OLTP system) and entering OLAP. Certainly, the INSERT statement in OLAP is larger and slower because it consists of many tables called ETL (extract-transform-load) that include 3 steps: extract is extracted data, transform data according to data warehouse format and load the data to the destination.

Create an ETL query to fill one of the OLAP tables, in this case dim_customer.

INSERT INTO
  dim_customer(id, cust_number, name, birth_date, email, main_contact_number)
SELECT
  seq.nextval, cst.number, cst.birth_date, cst.email, ccn.value
FROM customer cst
RIGHT OUTER JOIN customer_contact_number ccn
ON cst.id  = ccn.id_customer
JOIN contact_number_type cnt
ON ccn.customer_type_id = cnt.id
WHERE cnt.main = True;

You see that from the query, we optimize multiple tables to fill in the OLAP table. This query is complex but normal for OLAP.

We can fill dim_contract in the same way. However, we do not fill dim_date as above because it is predefined in the database and the data is not changed. With the fact_balance table, processing data fill is much more complicated, so it is not detailed here. At this point, all we need is to know the fact_balance table that stores the capture of customer data (such as account balance information) at a certain date.

If OLTP runs good at high speed with many similar cars, an OLAP high speed is a chaotic place with big and small cars and different speeds. It has big slow cars that no one has ever seen (special queries), slow-moving big cars that we often see (monthly reports) or other monsters ...

An OLAP example query shows the average balance of all 3 months for all users:

SELECT avg(amount)
FROM fact_balance fb
INNER JOIN dim_date dd
ON fb.balance_date = dd.date
WHERE dd.month = 3;

Other OLAP queries show the total number of customers born after 1990:

SELECT avg(amount)
FROM fact_balance fb
INNER JOIN dim_customer cs
ON fb.customer_id = cs.id
WHERE EXTRACT(month from balance_date ) = 3
AND birth_date >= date('01-01-1990','dd-mm-yyyy');

OLAP queries are usually aggregated and compute all data in a specific subset. We can find trends, averages, aggregates . These queries can be time-consuming, depending on the size of the subsets and not uniform.