In this tutorial we will learn about
differences between Data Warehouse database and OLTP database and
objectives of a Data warehouse and Data flow. The data warehouse and
OLTP data base are both relational databases. However,
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
OLTP database is essential and
semantics of
application used in
front end impact on
organization of
data in
database.
The data warehouse on
other hand does not cater to real time operational requirements of
enterprise. It is more a storehouse of current and historical data and may also contain data extracted from external data sources.
The differences between these two relational databases, is tabulated below for information.
Differences Data warehouse database OLTP database :
Data warehouse database: Designed for analysis of business measures by categories and attributes Optimized for bulk loads and large, complex, unpredictable queries that access many rows per table.
Loaded with consistent, valid data; requires no real time validation
Supports few concurrent users relative to OLTP Supports thousands of concurrent users.
OLTP database : Designed for real time business operations.
Optimized for a common set of transactions, usually adding or retrieving a single row at a time per table.
Optimized for validation of incoming data during transactions; uses validation data tables. Supports thousands of concurrent users.
Objectives of a Data warehouse and Data flow
The primary objective of data warehousing is to provide a consolidated, flexible meaningful data repository to
end user for reporting and analysis. All other objectives of Data warehousing are derived from this primary objective. The data flow in
warehouse also is determined by
objectives of data warehousing.
The data in a data warehouse is extracted from a variety of sources. OLTP databases, historical repositories and external data sources offload their data into
data warehouse. Achieving a constant and efficient connection to
data source is one of
objectives of data warehousing. This process is known as Data Source Interaction.