Difference between OLAP and OLTP

Key difference: The Online Analytical Processing is designed to answer multi-dimensional queries, whereas the Online Transaction Processing is designed to facilitate and manage the usual business applications. While OLAP is customer-oriented, OLTP is market oriented.

Both OLTP and OLAP are two of the common systems for the management of data. The OLTP is a category of systems that manages transaction processing. OLAP is a compilation of ways to query multi-dimensional databases. This article helps to differentiate between the two data systems.

The OLAP stands for ‘Online Analytical Processing’. It is a class of systems which provides answers to multi-dimensional queries. It manages historical data and stores only the relevant data. It is mainly characterized by low volume of transactions or data. It is consolidation data and the typical source of data for an OLAP database is the OLTP databases or the data warehouse.

The OLAP databases are highly de-normalized, which makes the files redundant and helps to improve analytic performance. The processing speed of the system is very slow and can take up to many hours depending on the data involved.

The OLAP system organizes data in a multidimensional model that is suitable for decision support and data analysis. The main purpose of OLAP is to help with decision support, planning, and problem solving. It reveals the multi-dimensional view of all types of business activities. 

The term OLTP stands for ‘Online Transaction Processing’. It is a class of systems that facilitate and manage transaction-oriented applications, typically for data entry and retrieval transaction processing. It manages current data and stores all of the given data. It is characterized by a large number of short online transactions and their quick real time response to the users.  

OLTP is operational data. It is considered as the original source of the data. The data in the OLTP database is structured, detailed and organized, so it favors speed, performance, reliability, data integrity, and security. The databases are highly normalized, which means there is very little or no data redundancy. 

The main purpose of the OLTP system is to control or run the fundamental business tasks. The main emphasis of the systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by the number of transactions per second. However, the OLTP database's performance is very poor when dealing with real-time data analysis.

Comparison between OLTP and OLAP:

 

OLAP

OLTP

Abbreviation

It stands for ‘Online Analytical Processing’.

It stands for ‘Online Transaction Processing’.

Use

It is used for Query Processing.

It is used for Transaction Processing.

Data

  • It holds historical data.
  • It stores only relevant data.
  • It is de-normalized in the analytical process.
  • It has a large database.
  • It contains non-volatile data.
  • It holds current data.
  • It stores all data.
  • It is normalized for efficient transaction process. 
  • It has a small database.
  • It contains volatile data.

Type

It is analysis driven.

It is application driven.

Source

The data comes from various OLTP sources.

It is the original source of data.

Purpose

To help with planning, problem solving, and decision support.

To control and run fundamental business tasks.

Business

It reveals the multi-dimensional view of all types of business activities.

It reveals the ongoing business process.

Updates

There are periodic long-running batch jobs which refresh the data.

Short and fast inserts and updates initiated by end users.

Queries

They are often complex queries involving aggregations.

They are standardized and simple queries.

Speed

It is slow depending on the data.

It is very fast.

Market

It is customer orientated.

It is market orientated.

Database design

It is de-normalized with fewer tables and makes use of star or snowflake schemas.

It is highly normalized with many tables.

View

It represents managerial view.

It represents clerical or operator view.

Users

It has few concurrent users.

It has many concurrent users.

Image Courtesy: oracle.com, soberit.hut.fi

Most Searched Non-Alcoholic Drinks Most Searched in Computers and Internets
Most Searched in Food and Drink Most Searched in Sports
Effected vs Affected
Quotes vs Quotations vs Sayings
Canola Oil vs Corn Oil
Butcher Paper vs Freezer Paper

Comments

Hi,

Arctcle is Very clearly explained..

But while explaining OLTP you said 'OLAP database is structured'. Is this correct...? (in second para of OLTP).

OLAP has any kind of data like structured,semi-structured

Thnk u, for your review! 

Very nicely Explained...
(y)

Add new comment

Plain text

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.