Data Warehouse Tutorials Overview
Welcome to Data Warehouse Tutorials. The objective of these tutorial is to gain understanding of Data Warehouse concepts. In these tutorials we will cover basic concepts of Data Warehouse with examples.
In addition to Data Warehouse Tutorials, we will cover common interview questions, issues and how to's of Data Warehouse.
Index
- Business Intelligence Tools in Data Warehouse
- Dashboards in Data Warehouse
- OLAP in Data Warehouse
- Data Mining in Data Warehouse
- Informatica in Data Warehouse
- ETL Concepts in Data Warehouse
- Types of Schema’s in Data Warehouse
- Types of Data Warehouses
- Data Warehouse Architecture
- Warehouse Management In Sap MM
What is a Data Warehouse?
According to Inmon, famous author for several data warehouse books, "A data warehouse is a subject oriented, integrated, time variant, non volatile collection of data in support of management's decision making process".
Example
In order to store data, over the years, many application designers in each branch have made their individual decisions as to how an application and database should be built. So source systems will be different in naming conventions, variable measurements, encoding structures, and physical attributes of data. Consider a bank that has got several branches in several countries, has millions of customers and the lines of business of the enterprise are savings, and loans. The following example explains how the data is integrated from source systems to target systems.
Example of Source Data
| System Name | Attribute Name | Column Name | Datatype | Values | |
| Source System | Customer Application | CUSTOMER_APPLICATION_DATE NUMERIC(8,0) | 11012005 | ||
| 1 | Date | ||||
| Source System | Customer Application | CUST_APPLICATION_DATE | DATE | 11012005 | |
| 2 | Date | ||||
| Source System | Application Date | APPLICATION_DATE | DATE | 01NOV2005 | |
| 3 | |||||
In the aforementioned example, attribute name, column name, datatype and values are entirely different from one source system to another. This inconsistency in data can be avoided by integrating the data into a data warehouse with good standards.
Example of Target Data(Data Warehouse)
| Target System | Attribute Name | Column Name | Datatype | Values |
| Record #1 | Customer Application Date | CUSTOMER_APPLICATION_DATE DATE | 01112005 | |
| Record #2 | Customer Application Date | CUSTOMER_APPLICATION_DATE DATE | 01112005 | |
| Record #3 | Customer Application Date | CUSTOMER_APPLICATION_DATE | DATE | 01112005 |
In the above example of target data, attribute names, column names, and datatypes are consistent throughout the target system. This is how data from various source systems is integrated and accurately stored into the data warehouse.
Data Warehouse Architecture Diagram
Data Warehouse & Data Mart
A data warehouse is a relational/multidimensional database that is designed for query and analysis rather than transaction processing. A data warehouse usually contains historical data that is derived from transaction data. It separates analysis workload from transaction workload and enables a business to consolidate data from several sources.
In addition to a relational/multidimensional database, a data warehouse environment often consists of an ETL solution, an OLAP engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.