What is Data Warehouse

What is Data Warehouse

Data Warehouse is a relational database which is mainly used for analytical and decision making purpose. Good analysis and decision making can happen when there are different sets of data available. When I say different sets, I mean historical data to compare with. Mostly all Data Warehouses holds historical information in them. This historical information gets stored into these data warehouses over the period of time from different sources. These sources can be the operational databases (OLTP DBs), third party interfaces or even various types of files.





Benefits of Data Warehouse

Main benefit of having a Data Warehouse in the organization is to provide power to business or to user to analyze data and give them a facility to make better decisions in the benefit of the organization. Data Warehouse keeps load away from OLTP databases in the organization and gives better performance and results of the queries one business can have.

Data Warehouse provides the closely bounded information or integrated information with respects to a subject. This is known as a subject area.

Data Warehouse gives us a facility to go back in time and see data as it was in that time. Once data entered into data warehouse then it is not meant to change. We can understand it in this way that DW gives us a snapshot of a time.

The best feature of DW is that the user can slice and dice data as required. Slicing and dicing of data means, user can go from top aggregation level to the granular level of data as per their choice.

Difference between OLTP and Data Warehouse

1. Data Warehouses usually are denormalized and do not follow the 3rd or higher normal forms which are common in OLTP databases.
2. OLTP systems have predefined queries to perform certain tasks ie monthly reports, creation of certain transactions. On the other hand Data Warehouse is to address ad-hoc requests.
3. OLTP systems get frequent DMLs performed on them. However Data Warehouse gets loaded periodically. This loading of the Data Warehouse is known as ETL. (Click here to see more about ETL)
4. Data Warehouse are useful for DSS whereas OLTP systems are useful for day to day business and support.
5. Queries in the Data Warehouse may have less joins but many indexes whereas OLTP system may have more joins as well as more indexes to perform the tasks quickly.

Looking forward to write about different designs of the Data Warehouses and architecture in my next post.

Till then happy working...