The term “Data Warehouse” was first coined by Bill Inmon in 1990. He said that Datwarehouse is subject Oriented, Integrated, Time-Variant and nonvolatile collection of data.This data helps in supporting decision making process by analyst in an organization.
Data Warehouse Features:
The key features of Data Warehouse such as Subject Oriented, Integrated, Nonvolatile andTime-Variant are discussed below:
Subject Oriented–The Data Warehouse is Subject Oriented because it provide us the information around a subject rather the organization’s ongoing operations. These subjectscan be product, customers, suppliers, sales, revenue etc.
Integrated– Data Warehouse is constructed by integration of data from heterogeneous sources such as relational databases, flat files etc. This integration enhances the effectiveanalysis of data.
Time-Variant–The Data in Data Warehouse is identified with a particular time period.The data in data warehouse provide information from historical point of view.
Non Volatile–Non volatile means that the previous data is not removed when new data is added to it. The data warehouse is kept separate from the operational database thereforefrequent changes in operational database are not reflected in data warehouse.Data Warehouse helps the business executives in organize, analyze and use their data fordecision making.
Data Warehouse is widely used in the following fields:
Online Transaction Processing System (OLTP)– An OLTP system is an application that modifies data(INSERT, UPDATE, DELETE) and has a large number of concurrent users.Highly normalized with many tables (3 NF)These systems aretypically used for order-entry purposes, such as for retail sales, creditcardvalidation, ATM transactions, and so on
Online Analytical Processing System (OLAP)–OLAP database is aggregated, historical data,stored in multi-dimensional schemas. Typically de-normalized with fewer tables.
OLAP Vs OLTP:-
|Data Warehouse (OLAP)||Operational Database(OLTP)|
|This involves historical processingof information||This involves day to day processing.|
|OLAP systems are used by knowledge workers such asexecutive, manager and analyst.||OLTP system are used by clerk, DBA, ordatabase professionals|
|This is used to analysis the business.||This is used to run the business.|
|This is based on Star Schema,Snowflake Schema and Fact Constellation Schema.||This is based on Entity Relationship Model.|
|It focuses on Information out.||This is application oriented.|
|This contains historical data.||This contains current data.|
|This provides summarized and consolidated data.||This provide primitive and highly detailed data.|
|This provide summarized and multidimensional view of data.||This provides detailed and flat relational view of data.|
|The number or users are in Hundreds.||The numbers of users are in thousands.|
|The numbers of records accessed are in millions||The numbers of records accessed are in tens.|
|The database size is from 100GB to TB||The database size is from 100MB to GB|
|This is highly flexible.||This provides high performance.|