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.
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
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
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.
Comments are closed.