What are the differences between OLTP and OLAP Systems

Question: What are the differences between OLTP and OLAP Systems

Answer:

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 Read: SQL Server Performance I/O Characteristics

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

OLAP

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://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.