Part 2: Data Warehousing Concepts - OLTP Vs Data Warehouse

OLTP Vs Data Warehouse

In this series, the previous post was about Part 1: Data Warehousing Concepts. Please go through it if you did not read.

OLTP and Data Warehouse have many differences in the architecture, process and performance. Here are the differences on various aspects.

• Performance
• Data Modification
• Design
• Operation
• Historical Data

Performance

Data Warehouse are developed to accommodate adhoc queries. So data warehouse should be optimized to perform well for any variety of possible query operations. Data Warehouse will perform better on queries. Because of the indexes define on the dimensional tables,  less number of joins and denormalized tables.

OLTP System is developed to support only predefined set of operations. So the database will be tuned only for those operations. For example, the transactions records are inserted, updated or deleted. If the index exists in the database, it will degrade the insert, update and delete operations.

Data Modification

A data warehouse is updated on a regular basis by the ETL process using bulk data load techniques. The end users of a data warehouse do not directly update the data warehouse.

In Data Warehouse, the data is loaded on bulk mode and the end user will not change any data.

Design

Data warehouses often use denormalized or partially denormalized schema (such as a star schema) to optimize query performance.

OLTP systems often use fully normalized schema to optimize update/insert/delete performance, and to guarantee data consistency. In OLTP systems, end users routinely issue individual data manipulation statements to the database. The OLTP database is always up to date, and reflects the current state of each business transaction.

Operation

Data Warehouse fetches millions of records at one shot. Whereas the OLTP fetches only handful of records at once.

Historical Data

Data warehouses usually store many months or years of data. This is to support historical analysis.

OLTP systems usually store data from only a few weeks or months. The OLTP system stores only historical data as needed to successfully meet the requirements of the current transaction.

In my next post, I will be explaining the different Data Warehouse Architectures.

If my post helped you understand the topic, please like and share and also share your comments with me to make the content better. Thanks for visiting my blog.

Part 1: Data Warehousing Concepts