Data Warehouse Training — Episode 6 — What is OLTP and OLTP VS OLAP

What is OLTP? Definition, Architecture, Example

What is OLTP?

OLTP is an operational system that supports transaction-oriented applications in a 3-tier architecture. It administers the day to day transaction of an organization. OLTP is basically focused on query processing, maintaining data integrity in multi-access environments as well as effectiveness that is measured by the total number of transactions per second. The full form of OLTP is Online Transaction Processing.

Characteristics of OLTP

Following are important characteristics of OLTP:

• OLTP uses transactions that include small amounts of data.
• Indexed data in the database can be accessed easily.
• OLTP has a large number of users.
• It has fast response times
• Databases are directly accessible to end-users
• OLTP uses a fully normalized schema for database consistency.
• The response time of OLTP system is short.
• It strictly performs only the predefined operations on a small number of records.
• OLTP stores the records of the last few days or a week.
• It supports complex data models and tables.

Type of queries that an OLTP system can Process:

OLTP system is an online database changing system. Therefore, it supports database query such as insert, update, and delete information from the database.

Consider a point of sale system of a supermarket, following are the sample queries that this system can process:

• Retrieving the description of a particular product.
• Filtering all products related to the supplier.
• Searching the record of the customer.
• Listing products having a price less than the expected amount.

Architecture of OLTP

Here is the architecture of OLTP:

1. Business / Enterprise Strategy:Enterprise strategy deals with the issues that affect the organization as a whole. In OLTP, it is typically developed at a high level within the firm, by the board of directors or the top management
2. Business Process:OLTP business process is a set of activities and tasks that, once completed, will accomplish an organizational goal.
3. Customers, Orders, and Products:OLTP database store information about products, orders (transactions), customers (buyers), suppliers (sellers), and employees.
4. ETL Processes:It separates the data from various RDBMS source systems, then transforms the data (like applying concatenations, calculations, etc.) and loads the processed data into the Data Warehouse system.
5. Data Mart and Data warehouse: A data mart is a structure/access pattern specific to data warehouse environments.It is used by OLAP to store processed data.
6. Data Mining, Analytics, and Decision Making: Data stored in the data mart and data warehouse can be used for data mining, analytics, and decision making.

This data helps you to discover data patterns, analyze raw data, and make analytical decisions for your organization’s growth.

Example of OLTP Transaction

An example of the OLTP system is the ATM center. Assume that a couple has a joint account with a bank. One day both simultaneously reach different ATM centers at precisely the same time and want to withdraw the total amount present in their bank account.

OLTOP for ATM image

However, the person that completes the authentication process first will be able to get money. In this case, the OLTP system makes sure that the withdrawn amount will be never more than the amount present in the bank. The key to note here is that OLTP systems are optimized for transactional superiority instead of data analysis.

Other examples of OLTP system are:

• Online banking
• Online airline ticket booking
• Sending a text message
• Order entry
• Add a book to shopping cart

OLTP vs. OLAP

Advantages of OLTP:

Following are the pros/benefits of OLTP system:

• OLTP offers accurate forecast for revenue and expense.
• It provides a solid foundation for a stable business /organization due to timely modification of all transactions.
• OLTP makes transactions much easier on behalf of the customers.
• It broadens the client base for an organization by speeding up and simplifying individual processes.
• OLTP provides support for bigger databases.
• Partition of data for data manipulation is easy.
• We need OLTP to use the tasks which are frequently performed by the system.
• When we need only a small number of records.
• The tasks that include insertion, updation, or deletion of data.
• It is used when you need consistency and concurrency in order to perform tasks that ensure its greater availability.

Disadvantages of OLTP:

Here are cons/drawbacks of OLTP system:

• If the OLTP system faces hardware failures, then online transactions get severely affected.
• OLTP systems allow multiple users to access and change the same data at the same time, which many times created an unprecedented situation.
• If the server hangs for seconds, it can affect to a large number of transactions.
• OLTP required a lot of staff working in groups in order to maintain inventory.
• Online Transaction Processing Systems do not have proper methods of transferring products to buyers by themselves.
• OLTP makes the database much more susceptible to hackers and intruders.
• In B2B transactions, there are chances that both buyers and suppliers miss out efficiency advantages that the system offers.
• Server failure may lead to wiping out large amounts of data from the database.
• You can perform a limited number of queries and updates.

Challenges of an OLTP System

• It allows more than one user to access and change the same data simultaneously. Therefore, it requires concurrency control and recovery technique in order to avoid any unprecedented situations
• OLTP system data are not suitable for decision making. You have to use data of OLAP systems for “what if” analysis or the decision making.

Summary

• OLTP is defined as an operational system that supports transaction-oriented applications in a 3-tier architecture.
• OLTP uses transactions that include small amounts of data.
• OLTP system is an online database changing system.
• The architecture of OLTP contains

1) Business / Enterprise Strategy,

2) Business Process,

3) Customers, Orders, and Products,

4) ETL Processes,

5) Data Mart and Data warehouse, and

6) Data Mining, Analytics, and Decision Making.

• OLTP is an online transactional system, whereas OLAP is an online analysis and data retrieving process.
• OLTP provides a solid foundation for a stable business /organization due to timely modification of all transactions.
• OLTP systems allow multiple users to access and change the same data at the same time, which many times created an unprecedented.

Alperen Kezay