Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
Data Warehouse Design Pattern

OLTP and OLAP

 
Updated on Sep 29, 2020

OLTP stands for On-line Transaction Processing and OLAP stands for On-line Analytical Processing. We may have heard these definition many times. But do we really understand the difference between them. Let's explore further on these two kinds of systems and their characteristic differences

Definition

OLTP

On Line Transaction Processing describes processing of short and simple transaction data at operational sites i.e. day to day operations in the source systems. The Database is designed as application-oriented (E-R based) i.e highly Normalized so as to efficiently support INSERT and UPDATE operations. Data stored in these systems are raw, current (Up-to-date) and isolated data, in a much detailed level in flat relational tables.

OLAP

On Line Analytical Processing describes processing at the centralized, integrated and consistent Data Warehouse. It acts as the Decision Support System for the business end users. The Database is designed as Subject-oriented (Star / Snowflake Schema) i.e. highly Denormalized to efficiently support the <b>SELECT</b> operations. Data in these systems are generally consolidated, summarized and historical Data in nature.

What is a Data Warehouse

A Data Warehouse can be defined as a centralized, consistent data store or Decision Support System (OLAP), for the end business users for analysis, prediction and decision making in their business operations. Data from various enterprise-wide application/transactional source systems (OLTP), are extracted, cleansed, integrated, transformed and loaded in the Data Warehouse.

Properties of a Data Warehouse

Subject-oriented

Data Warehouse is designed based on the major subjects areas of the business, rather than the major application areas of the enterprise. The ultimate necessity is to store decision-support data rather than application-oriented data.

Integrated

The data in Data Warehouse comes from different enterprise-wide application source systems. The source data is often inconsistent in nature. During the Integration process, source data must be made consistent in Data Warehouse so that the data becomes homogeneous and uniform.

Time-variant

The source data in the Data Warehouse is only accurate and valid at some point in time or over some time interval.

Non-volatile

Data in Data Warehouse is not updated or replaced in real time but is refreshed from operational systems on a regular basis. New data is always added incrementally integrating it with the previous data set.

Why we go for Data Warehouse instead of OLTP Reporting and Analysis

  1. OLAP reporting requires historical data whereas the operational databases do not maintain history and updates the transactions.
  2. Data in Data Warehouse is loaded after consolidation, integration, aggregation as well as summarization of data from various heterogeneous OLTP source systems. Hence in that case we will fail to generate OLTP reporting.
  3. Also the data in the different source systems uses inconsistent data representations, codes, and formats which have to be reconciled before loading into Data Warehouse.
  4. Finally, complex OLAP queries will obviously degrade the performance of operational / transactional source systems. The OLTP databases are designed and tuned to support insert and update operations mainly.
PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles