Examples When We Would Use Olap Vs Oltp

OLAP vs. OLTP is an online processing system. Opposed to a batch processing system, where transaction details pile up in a stack of documents that are then registered in batches, an online processing system is one that registers ongoing transactions in real-time.

OLTP is a transaction processing system i.e.; it manages transaction-based applications over the internet. OLTP systems, for instance, are responsible for providing data to data warehouses.

On the other side, OLAP is an analytical processing system. This simply means that it responds to multidimensional analytical queries corresponding to financial reporting, forecasting, etc. The data available to a data warehouse, for example, is analyzed using an OLAP system.

One major difference between OLAP vs. OLTP is that while the former is an online database modifying system, the latter is an online database query answering system. However, the difference between the two online processing systems goes way beyond this distinction.

Before comparing the two types of online processing systems, i.e., OLAP and OLTP, let's get to know them better with detailed definitions and pros and cons of using each of them.

What is OLTP (Online Transaction Processing) System?

OLTP stands for Online Transaction Processing. OLTP systems become the source of data for OLAP systems. The primary purpose of a typical OLTP system is to record current Update, Insertion, and Deletion requests, and modify the extant database accordingly.

OLTP systems support transaction-oriented applications in a 3-tier architecture. They administer the daily transactions of an organization. Data processing is the primary objective of an OLTP system.

Unlike an OLAP database, a typical OLTP database gets updated often. An OLTP transaction might fail before completing, causing serious data integrity issues to the OLTP system. Hence, such an online processing system needs to take special care of data integrity.

OLTP systems maintain data integrity in multi-access environments, and their effectiveness is usually evaluated by the total number of transactions they can carry out in a single second.

A typical OLTP database stores detailed and current data. The Entity Model is the scheme used for storing such transactional databases. Therefore, databases about OLTP systems are normalized. Typically, it is achieved using the 3NF normalization.

Most OLTP queries are simple and short. Hence, they have lesser processing time and storage space requirements. An ATM is the best example of an OLTP system. Other noteworthy examples of OLTP systems include online banking and sending an SMS.

Pros:

Powerful for administering day-to-day transactions
Simplifies individual processes, thus allowing an organization to increase its consumer base

Cons:

Allows multiple users to access and modify the same data, hence causing issues
Online transactions get several affected by hardware failures.

What is OLAP (Online Analytical Processing) System?

OLAP is a contraction for Online Analytical Processing. An OLAP database stores past data that has been inputted using an OLTP system. An OLAP system allows users to view a variety of summaries about stored multidimensional data.

An OLAP system allows extracting information from a bigger database and analyzes the same with the intent of decision-making. To do so, the system provides support for both simple and complex queries.

The effectiveness of an OLAP system is measured through its response time, and its primary objective is data analysis. Data integrity isn't much of a concern for OLTP systems. In case of a transaction failure, the user can simply re-run the transaction and extract the required details.

Compared to an OLTP system, transactions made with an OLAP system are infrequent. However, OLAP transactions are long and, therefore, require more processing time as well as storage space. The tables in an OLAP database might not be normalized.

OLAP databases feature aggregated and historical data that is stored in multidimensional schemas. Typically, this is a star schema.

Examples of OLAP transactions include viewing financial and sales reports, extracting budgeting information, and viewing marketing management details. A personalized Amazon homepage with item recommendations are also instances of an OLAP system at play.

Pros:

Capable to easily impose security restrictions on users to comply with regulations and also to protect sensitive data
Consistency of information as well as calculations
Offers a single platform for almost all type of business analytical needs

Cons:

Dependents heavily on an IT professional or team for implementation and maintenance
Effectiveness depends on the cooperation of interdepartmental personnel.

OLAP vs. OLTP: The Comparison

To simplify the comparison between OLAP and OLTP online processing systems, we have divided the whole section into several categories:

OLTP vs OLAp

1. Backup & Recovery

OLAP systems require backup less frequently. Hence, a strict backup schedule, in this case, is not that much important. Moreover, some environments might consider reloading the related OLTP data as a recovery method instead of having regular backups.

The view on backup and recovery is the polar opposite from that of an OLAP system when witnessed from the perspective of an OLTP system. A complete backup at any moment in time is mandatory, along with some kind of provision for incremental backups.

This is because operational data is critical to run a business, and the loss of such data can cause undesired fiscal and legal issues to the concerned organization.

2. Data

Online transactional data serves as the main source of data for an OLTP system. Several databases about multiple OLTP systems come together to act as the chief source of data for an OLAP system.

Data about OLTP systems are operational data, while data related to OLAP systems is consolidation data. The purpose of data for OLTP systems is to control as well as run fundamental business tasks.

For OLAP systems, data intend to assist with planning, problem-solving, and decision making. Typically, data exposed to OLTP requests are in the form of a screen or screenshot. On the contrary, data displayed by OLAP systems are in the form of multidimensional views.

3. Database

An OLTP system is an online database modifying system that relies on traditional database management systems. An OLAP system makes use of the data warehouse concept and is an online database query management system.

While an OLTP database is composed of several tables, a typical OLAP database has fewer tables. It's optional for tables in an OLAP database, which typically uses star and/or snowflake schema, to be normalized.

However, this is not the same with tables belonging to an OLTP database. In this case, the tables must be normalized, typically, in 3NF.

4. Data Integrity

An OLTP system frequently executes transactions that modify the database. In the case that any transaction fails, it can lead to some serious data integrity consequences. Therefore, data integrity can't be ignored when OLTP systems are concerned.

Unlike OLTP systems, OLAP systems go through infrequent transactions. Therefore, the chance of failing in some transactions is very low. Hence, OLAP systems don't necessarily require for data integrity.

5. Primary Intent

An OLTP system focuses on inserting, updating, and sometimes deleting information from the database. On the contrary, the primary focus of a typical OLAP system is to allow the user to retrieve/extract multidimensional data that can be analyzed and used for decision making.

While the users make short and quick inserts and updates to an OLTP system, OLAP systems are characterized by periodic long-running batch jobs and select operations that refresh the data.

6. Storage Space Requirements

The requirement for storage space is small for OLTP systems and even smaller for those systems with archived historical data.

On the contrary, OLAP systems ask for significant storage space. This is due to the existence of aggregation structures and historical data, which calls for much more indexes than a typical OLTP system.

7. System Type

While OLAP is an online data retrieval and analysis system, OLTP is an online transaction system. Unlike the OLAP system, the OLTP system is responsible for managing database modification.

8. Transactions

An average transaction for an OLTP system is short, and therefore, fast. However, this is not the case for an OLAP system, where a typical transaction lasts much longer. However, the frequency of transactions is less for OLAP systems and more for OLTP systems.

The total processing time required by an OLTP transaction is small. Contrarily, an OLAP transaction requires much time for carrying out a single transaction.

This is evident from the fact that response time for OLTP and OLAP systems are measured in milliseconds and seconds or minutes, respectively.

9. Query Complexity

Both OLAP and OLTP systems are query-based. However, queries concerning the OLTP systems are simple as compared to OLAP systems that provide support for complex queries.

A typical OLTP query is relatively standardized and returns a few records. An OLAP query, on the other hand, involves aggregations and returns a heavy load of records or information.

OLAP vs OLTP: Head to Head Comparison

Parameters	OLAP	OLTP
Backup and Recovery	OLAP systems require backup less frequently.	Backup and recovery are the polar opposite when witnessed from the perspective of an OLTP system. A complete backup at any moment in time is mandatory along with some kind of provision for incremental backups.
Data	It makes use of the data warehouse concept and is an online database query management system.	Relies on traditional database management systems.
Data Integrity	OLAP systems go through infrequent transactions. Hence, OLAP systems don't necessarily require for data integrity.	Data integrity can't be ignored when OLTP systems are concerned as the OLTP system frequently executes transactions that modify the database.
Primary Intent	OLAP system allows the user to retrieve/extract multidimensional data that can be analyzed and used for decision making.	OLTP system focuses on inserting, updating, and sometimes deleting information from the database
Storage Space Requirements	OLAP systems ask for significant storage space due to the existence of aggregation structures and historical data.	It requires smaller storage space and even smaller space for those systems with archived historical data.
System Type	OLAP is an online data retrieval and analysis system.	OLTP is an online transaction system.
Transactions	Transactions much longer but the frequency is more.	Short and fast, but the frequency of transactions is less.
Query Complexity	Complex queries	Simple queries
Audience	Customer-oriented	Market-oriented
Table	Tables are not normalized	Tables are normalized
Response time	In seconds to minutes	In milliseconds

Summary

As both the OLAP and OLTP online processing systems fulfill different purposes, choosing between them depends entirely on the requirements.

This is the complete difference between OLTP vs. OLAP. While OLTP systems are apt for administering daily tasks, OLAP systems are great at storing historical data that can be churned later for decision making and problem-solving.

People are also reading:

What is MongoDB?
Difference between MongoDB vs. MySQL
Best DBMS Interview Questions and Answers
What is Stored Procedures?
MySQL Create Database
Top PL/SQL Interview Questions & Answers
Best SQL Interview Questions & Answer
Top SQL Commands
Difference between SQL vs. NoSQL
Best DBMS Interview Questions

Examples When We Would Use Olap Vs Oltp

Source: https://hackr.io/blog/olap-vs-oltp