Compare Data Warehouse database and OLTP database?

The data warehouse and the OLTP data base are both relational databases. However, the objectives of both these databases are different.

The OLTP database records transactions in real time and aims to automate clerical data entry processes of a business entity. Addition, modification and deletion of data in the OLTP database is essential and the semantics of the application used in the front end impact on the organization of the data in the database.

The data warehouse on the other hand does not cater to real time operational requirements of the enterprise. It is more a storehouse of current and historical data and may also contain data extracted from external data sources.

Differences
Data warehouse database	OLTP database
Designed for analysis of business measures by categories and attributes	Designed for real time business operations.
Optimized for bulk loads and large, complex, unpredictable queries that access many rows per table.	Optimized for a common set of transactions, usually adding or retrieving a single row at a time per table.
Loaded with consistent, valid data; requires no real time validation	Optimized for validation of incoming data during transactions; uses validation data tables.
Supports few concurrent users relative to OLTP	Supports thousands of concurrent users.

However, the data warehouse supports OLTP system by providing a place for the latter to offload data as it accumulates and by providing services which would otherwise degrade the performance of the database.

Tutorial 2: Data Warehouse database and OLTP database

Interview Candidate
Aug 4th, 2005
6
30656

OLAP

Answer

Showing Answers 1 - 6 of 6 Answers

sithusithu
Profile Answers by sithusithu

Jan 4th, 2006

OLTP ? This is from operational data source
Data warehouse/data mart ? historical data extracted from different ODS for analytical purpose

Cheers
Sithu

zahoorahmad
Profile Answers by zahoorahmad

Apr 12th, 2006

Well above answer are fine
but there is one convention.
Term "Repository" is used for data warehouse data base. And infact its not data base either.

thumatinagaraju
Profile Answers by thumatinagaraju Questions by thumatinagaraju

Oct 23rd, 2006

data ware house is summarized,subject oriented,non volatile,historic, time invarient (only insertion's no updations) , denormalized. one, which support OLAP process.where as database support's detailed data, non-subject oriented , volatile, time varient, normalized one, which support OLTP . it always hold current data only.

Ritu Khare

Dec 15th, 2006

In simple words,
An OLTP system is used to get data into the system.
A datawarehouse system is used to get data out of the system.
-Ritu