Introduction..

No Business can run without DATA.  Infact ,the success of any business organization depends upon how effectively it analyzes and acts on data.

But , How Data is important for Business ?

The Success of any business organization depends on how wisely and correctly it can take future decisions. These future decisions are very much dependent upon the study of the past trends.
For E.g. :
In a Retail store , in order to figure out the peak selling days and max sold item, one may require to gather data of the past sales record.

In this post , our main focus will be to understand what is Data Warehouse actually ??

In order to cope up with the increasing competitors and fast changing trends, Business organizations needs to keep a track of all the past data , so that it can take right decisions for the future by studying the past trends.In order to achieve this and handle such vast quantities of data, they basically maintain two types of databases :

  • Operational or OLTP Databases.
  • Data Warehouse  or OLAP Databases.

Operational Database (OLTP) :

Data Warehouse Database (OLAP) :

What is Data warehouse?

Warehouse means a storage area , Thus Data + Warehouse may be defined as storage area for the data.
In very simple words , Data warehouse is a database.

What Type of Data is stored in Data warehouse ??

Data warehouse is basically meant to store ‘Historical Data’. It contains data coming from different multiple sources.

What is the source of the data for Data warehouse ??

Well , the data in data warehouse comes from the operational database. A data warehouse is an organized copy of operational database that is specifically designed for query , analysis and reporting purpose.

Features of Data warehouse:

1. Integrated: Data warehouse integrates data from multiple data sources. Let’s take an example of real time scenario of a shopping mart for better understanding.

Suppose, we have 3 branches of a shopping mart A,B and C respectively. All transactions of each branch are stored in their respective database. At the end of each day, data from all these 3 branches integrated into a common database, called data warehouse.

2. Subject Oriented :

3. Time Variant :

4. Non – Volatile :

Technically ,
A Data Warehouse also called as Online Analytical Processing database(OLAP) is a subject- oriented, time variant, non-volatile database that stores historical data integrated from multiple sources and is used by business intelligence (BI) people for reporting ,analysis and business forecasting.

Need for maintaining Historical Data and Data Warehouse :

Why Can’t Opearational database be used for storing the historical data and generating reports ??

Difference between the Operational Database and Data Warehouse Database :

Data warehouse(OLAP) Operational Database (OLTP)
1. Used for Online Analytical Processing. 1. Used for Online Transaction Processing.
2. It stores Historical Data and is used for Historical Processing of Information. 2. It stores Current Data and is used for Day to Day Processing.
3. It is used to Analyze the business as it stores historical data and generate reports and study trends. 3. It is used to run the business as it deals with day to day transactions.
4. Dimensional Modeling Techniques like Star Schema,Snowflake Schema are used for Data Warehouse Database design. 4. Entity – Relational (ER) Modeling Techniques are used for Database design.
5. It Provides Multi Dimensional view of data and contains Summarized Information. 5. It Provides Flat Dimensional view of data and contains Detailed Information.
6. It is optimized for storing large amount of data and transactional activity. 6. It is optimized for storing small amount of data and reporting activities.
7. Tables and Joins in the database are simple as they are de-normalized in order to reduce response time. 7. Tables and Joins in the database are complex as they are normalized in order to reduce redundancy and increase storage space.
8. It is optimized for read operations and is not frequently updated. 8. It is optimized for write operations and is updated for day to day transactions.
9. It shows high performance for analytical queries. 9. It shows low performance for analytical processing.

Print Friendly, PDF & Email
Surrogate key In Data warehouse.
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *