Back to articles list
Confused by online transaction processing (OLTP) and online analytical processing (OLAP) in the world of databases? Fear not. We have a simple explanation right here.
To understand the differences between OLTP and OLAP, we first need to understand where they fit into the world of data and databases. And the answer is “data warehousing”.
A data warehouse is a system used for reporting and data analysis. They are central repositories of data from one or more disparate sources, including relational databases.
In online transaction processing (OLTP), tables store individual details like orders, customer information, shipping address, etc. When you access the data, typically you are filtering out the vast majority of records to focus only on those records that concern you.
In online analytical processing (OLAP), you are typically creating a report across various tables from numerous sources. OLAP may scan many tables and aggregate data across a huge number of records. With OLTP, we expect a response in a fraction of a second; with OLAP, getting the response may require much more time.
What Is OLTP? What Is OLAP?
An OLTP system captures transaction data that it stores in an OLTP database. Each transaction may involve several database records with multiple fields. OLTP focuses on processing quickly and delivering immediate responses. Tables in OLTP databases are updated frequently. If a transaction fails, the system logic that is built into OLTP must ensure data integrity. OLTP data needs to be atomic, consistent, isolated, and durable (ACID). This ensures that each transaction will still be valid if unexpected errors occur. Good OLTP database design keeps redundant and duplicate data to a minimum.
OLAP is designed for quick, efficient reporting and the analysis of large quantities of data. It uses complex queries on very large amounts of historical data. This data has been aggregated from various sources that likely include OLTP databases. OLAP is used for analytics, reporting, data mining, and Business Intelligence. OLAP queries should have low response times. Query failure in OLAP will not affect transaction processing, but it may delay or reduce the accuracy of the information extracted from the OLAP database.
Extract-Transform-Load (ETL) is often used to extract data from OLTP sources, transform it, and load it into an OLAP system.
If you'd like more details about data warehouse terminology and design, see our articles on OLAP for OLTP Practitioners and What You Need to Know About Data Warehouses.
OLTP and OLAP Database Models
A simple example of a normalized transactional database model is the link between an order and the products included in that order. One table stores all product data, one table stores order data, and a third table links each order to the items (products) that it contains.
In such a model, there is no duplication of data from the Product table into the Order table. We can create several products in the product table, then create orders that contain different combinations of products. The tables required for these transactions would look something like this:
In contrast, data warehouses generally have a star schema design that is based on fact and dimension tables; the star schema is the simplest model used in OLAP.
Fact tables contain reporting data that has been aggregated from related dimension tables. Its columns store values and reference dimension tables via foreign keys. When designing a fact table, you should keep in mind the concept of sparsity.
Dimension tables describe the data that is stored. Each dimension table has a category like orders, employees, and stores. Data warehouses use denormalized data; in other words, they have redundant data for the sake of performance.
Here’s an example of a star schema with fact and dimension tables:
The good news is that Vertabelo can help you create a database model for both OLTP and OLAP. As you see above, with Vertabelo, you can create models from simple to complex for both transaction processing and analytics.
OLTP vs. OLAP: Which Is Best for You?
The answer to that question will depend on the usage that will be made of the data. What are you going to do with the information stored in the database? What’s its business purpose?
To put it in technical terms, is the application oriented to online transaction processing (OLTP) or online analytical processing (OLAP)? Your data model should be normalized for OLTP and denormalized for OLAP. That being said, you should only denormalize when you have a reason to do so. Denormalization leads to redundant data that will be difficult to maintain.
Online transactional processing (OLTP) is used for CRUD (Create, Read, Update, Delete) applications, while online analytical processing (OLAP) is used for reporting and Business Intelligence. The main difference between the underlying transactional and analytical databases is the nature of the application using the data.
A transactional database is designed for an application in which the user is more interested in CRUD, i.e. creating, reading, updating, and deleting records. An analytical database is designed for an application in which the user is more interested in analysis, reporting, and forecasting. In OLAP, the focus is to store data for analysis with no user maintenance of data. Thus, analytical databases rarely have inserts and updates. The goal of an analytical database is to analyze data quickly. When designing a data model, consider the usage of the data.
Vertabelo won't help you decide which type of database model you need, but once you have figured that out, the database modeler can help you create both OLTP and OLAP models. If you are not sure how, check out other articles related to OLTP (there are tons of articles on this, but you might start with OLAP for OLTP practitioners) and OLAP: fact tables, dimension tables, star schemas, snowflake schemas, and data warehousing, among other things.
Side-by-Side Comparison of OLTP vs. OLAP
First off, there is not a single type of database model that you should always use. As mentioned above, one main distinction is OLTP vs. OLAP; the one you choose will depend on how your application uses the data in your database.
|
Criteria |
OLTP |
OLAP |
|
Purpose |
Process transactions quickly. |
Business intelligence or reporting. |
|
Characteristic |
Handles a large number of small transactions. |
Handles large volumes of data. |
|
Query type |
Optimized for all kinds of simple standard queries, typically filtering for a few records. |
Optimized for complex queries that aggregate multiple fact and dimension tables. |
|
SQL Operations |
INSERT, UPDATE, DELETE statements to update data and SELECT to read it. |
SELECT statements to aggregate data. |
|
Update |
Short, fast updates are initiated by the user. Processes all kinds of frequently-occurring updates (insert, update, and delete). |
Data is periodically refreshed via scheduled, long-running batch jobs. Optimized for reading data, typically run on a less frequent basis than OLTP. |
|
Performance |
Queries should execute for immediate response to requests, i.e. in milliseconds. |
Queries execute in seconds, minutes, or hours, depending on the amount of data to process. |
|
Data model type |
Normalized models, like BCNF, with many tables. |
Denormalized model, usually with fewer tables and often based on a star scheme, snowflake scheme, or similar. |
|
Design |
Industry-specific, such as retail, manufacturing, or banking. |
Subject-specific, such as sales, inventory, or marketing. |
|
Data quality |
Efforts to ensure ACID compliance. |
Data may not be organized: what matters is the ability to manage the dimensions of the data. |
|
Space requirements |
Space will depend on the number of transactions to be processed and the length of online storage. Generally smaller than OLAP if historical data is archived. |
Generally large, due to the aggregation of large datasets. |
|
Availability |
Generally, 24x7x365 is essential when transactions are performed every second of every day. |
Interactions are less frequent; the absence of an OLAP system should not impact operations. |
|
Use Case Examples |
Operational: Applications used concurrently by many users, such as order entry, financial transactions, customer relationship management, and retail sales. Examples are online ticket bookings, banking, e-commerce websites, fintech, and other businesses where there are thousands or millions of transactions per day. |
Informational: Trend analysis and data patterns, predicting risks and outcomes, generating reports, and tracking customer behavior and buying patterns. Examples include creating sales and marketing reports, preparing forecasts, and business process management. |
In short, OLTP provides a record of transactional activity; OLAP provides insights from that data over time.
Combining OLTP and OLAP Systems
Both online transaction and analytic processing are essential parts of business data management. OLTP and OLAP are complementary to each other, as analytics can only be carried out based on effective OLTP systems. Based on the analytics requirements, an OLTP data model may need to be designed differently or re-designed to support changes in trends.
