What is OLTP? Definition, Architecture, Example

In this OLTP tutorial, you will learn:

• What is OLTP?
• Characteristics of OLTP
• Type of queries that an OLTP system can Process:
• Architecture of OLTP
• Example of OLTP Transaction
• OLTP vs. OLAP
• Advantages of OLTP:
• Disadvantages of OLTP
• Challenges of an OLTP System

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.

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

Here is the important difference between OLTP and OLAP:

OLTP	OLAP
OLTP is an online transactional system.	OLAP is an online analysis and data retrieving process.
It is characterized by large numbers of short online transactions.	It is characterized by a large volume of data.
OLTP is an online database modifying system.	OLAP is an online database query management system.
OLTP uses traditional DBMS.	OLAP uses the data warehouse.
Insert, Update, and Delete information from the database.	Mostly select operations
OLTP and its transactions are the sources of data.	Different OLTP databases become the source of data for OLAP.
OLTP database must maintain data integrity constraints.	OLAP database does not get frequently modified. Hence, data integrity is not an issue.
It’s response time is in a millisecond.	Response time in seconds to minutes.
The data in the OLTP database is always detailed and organized.	The data in the OLAP process might not be organized.
Allow read/write operations.	Only read and rarely write.
It is a market-orientated process.	It is a customer orientated process.
Queries in this process are standardized and simple.	Complex queries involving aggregations.
Complete backup of the data combined with incremental backups.	OLAP only need a backup from time to time. Backup is not important compared to OLTP
DB design is an application-oriented example: Database design changes with the industry like retail, airline, banking, etc.	DB design is subject-oriented. Example: Database design changes with subjects like sales, marketing, purchasing, etc.
It is used by Data critical users like clerk, DBA & Data Base professionals.	It is used by Data knowledge users like workers, managers, and CEO.
It is designed for real time business operations.	It is designed for analysis of business measures by category and attributes.
Transaction throughput is the performance metric	Query throughput is the performance metric.
This kind of Database user allows thousands of users.	This kind of Database allows only hundreds of users.
It helps to Increase user’s self-service and productivity	Help to Increase the productivity of business analysts.
Data Warehouses historically have been a development project which may prove costly to build.	An OLAP cube is not an open SQL server data warehouse. Therefore, technical knowledge and experience are essential to managing the OLAP server.
It provides a fast result for daily used data.	It ensures that response to the query is quicker consistently.
It is easy to create and maintain.	It lets the user create a view with the help of a spreadsheet.
OLTP is designed to have fast response time, low data redundancy, and is normalized.	A data warehouse is created uniquely so that it can integrate different data sources for building a consolidated database

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 situation.