Data Warehouse

1. What is a Data Warehouse?

A data warehouse stores integrated data from different sources in a structured format for reporting and analysis.

• Subject-oriented
• Integrated
• Time-variant
• Non-volatile

2. Characteristics of Data Warehouse

• Subject-Oriented: Organized by business subjects
• Integrated: Data from multiple sources
• Time-Variant: Stores historical data
• Non-Volatile: Read-only, stable data

3. Need of Data Warehouse

• Supports business decision making
• Analyzes large volumes of data
• Improves data consistency
• Separates analytical workload from OLTP

4. Data Warehouse Architecture

Operational Databases
        |
        |  (ETL Process)
        v
+----------------------+
|   Data Warehouse     |
+----------------------+
        |
        v
+----------------------+
| Data Marts / OLAP    |
+----------------------+
        |
        v
 Business Users / Reports

5. ETL Process

• Extract: Collect data from sources
• Transform: Clean and format data
• Load: Store data into warehouse

6. Data Warehouse vs Database

Database (OLTP)              Data Warehouse (OLAP)
--------------------------  -----------------------------
Current data                Historical data
Transaction processing      Analytical processing
Frequent updates            Read-only
Operational use             Decision support

7. Advantages of Data Warehouse

• Better business intelligence
• Improved query performance
• Accurate reporting
• Data consistency

8. Applications of Data Warehouse

• Banking and finance
• Retail sales analysis
• Healthcare analytics
• Telecom and marketing

Practice Questions

1. What is a data warehouse?
2. Explain characteristics of data warehouse.
3. What is ETL process?
4. Differentiate database and data warehouse.
5. List applications of data warehouse.

Practice Task