SQL Server

What are the differences between OLTP and OLAP Systems

What are the differences between OLTP and OLAP Systems

Question: What are the differences between OLTP and OLAP Systems


In a production environment, OLTP system deals with high number of transactions, i.e. inserts/deletes/updates on an on-going basis and is optimized for such activities.

An OLAP system on the other hand refers to carrying out analysis, on historical data transactions that have occurred over time. It is mainly used for read-only querying, and therefore is optimized to serve that purpose. The system should achieve a response time within seconds for any activity, for example: expanding from a product’s sales information to the products categories sales hierarchical information or comparing numbers with previous year’s sales numbers. OLAP systems are used for their heavy read-access on contiguous blocks of data and you need good in-memory and disk performance. Typically they are ideal for SSD (Solid State Drive) based fast storage for high random and sequential reads.

Also ReadSQL Server Performance I/O Characteristics

What are the differences between OLTP and OLAP Systems

A Data Warehouse contains the OLTP system data in de-normalized form typically in a Star or Snowflake schema structure. It stores a subset of the data in the system that is later going to be analyzed. A Data Mart is sometimes used synonymously with Data-Warehouse. Specifically, when it comes to an Enterprise, a DWH (Data-Warehouse) is the encompassing structure, whereas Data Marts then refer to self-contained parts that sum up to the DWH but can as well be managed and analyzed independently and provide a restricted view on a certain area of the enterprise, for example on a certain business process like Purchase, IT, HR etc.

Decision Support Systems facilitate decision-taking processes and often include data not just from an existing OLAP system but include knowledge from external resources as well.



OLTP stands for Online Transactional Processing System

Mostly Production database

Optimized for (mostly fine-grained) inserts, updates, deletes, selects, creates

Focus on maintaining ACID properties and data constancy.

User concurrency is high.

Storage subsystem needs to be highly responsive to handle random read-writes.

Highly Normalized Table structure

OLAP stands for Online Analytical System

The main purpose is rapid data analyis on (historical) data sources from OLTP systems.

Optimized for querying on large sequential data sets

Operational Data Store

Purpose is to integrate from sources

Data Warehouse with de-normalization.

Enterprise-wide database that holds historical data

Data Mart

Specific to a business area

Decision Support System

Include knowledge from other sources


Hopefully that gives you a good insight of what the difference is between OLTP and OLAP systems.

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.

What's your reaction?

In Love
Not Sure

You may also like

Comments are closed.

More in:SQL Server