Data Warehousing: Core Concepts, Purpose & Foundations

TAKEAWAYS:

• A data warehouse is a centralized repository for structured, historical data optimized for analytics, not transactions.
• It differs from OLTP systems by subject-oriented, integrated, time-variant, and non-volatile data.
• Business intelligence (BI) and data mining rely on warehouses to uncover patterns (e.g., Daraz’s sales trends).
• ETL (Extract-Transform-Load) pipelines clean and consolidate data from disparate sources (e.g., Ncell’s call records).
• Star and snowflake schemas organize data for efficient querying (used in NEPSE’s market analytics).
• Data marts are specialized subsets of warehouses (e.g., Khalti’s transaction dashboards).

1. What is a Data Warehouse?

A data warehouse (DW) is a subject-oriented, integrated, time-variant, and non-volatile collection of data designed to support decision-making and business intelligence (BI). Unlike operational databases (OLTP), warehouses store historical data for analysis, not real-time transactions.

Key Characteristics

mindmap
  root((Data Warehouse))
    - Subject-Oriented
      - Organized by business themes (e.g., sales, customers)
    - Integrated
      - Data from multiple sources (OLTP, external APIs)
    - Time-Variant
      - Historical data (days, months, years)
    - Non-Volatile
      - Read-only for end users

Why Use a Data Warehouse?

• Unify data from ERP, CRM, and legacy systems (e.g., NTC’s call detail records + customer data).
• Enable complex queries (e.g., "Which Pathao drivers have the highest cancellation rates?").
• Support predictive analytics (e.g., Daraz forecasting demand for monsoon sales).

2. Data Warehouse vs. Operational Database (OLTP)

3. Data Warehouse Architecture

A typical DW consists of:

1. Source Systems (OLTP databases, flat files, APIs).
2. Staging Area (raw data storage before cleaning).
3. Data Warehouse (cleaned, integrated data).
4. Data Marts (subset of DW for specific departments).
5. End-User Tools (BI dashboards, reporting tools).

flowchart LR
    A["Source Systems<br/>(OLTP, APIs)"] --> B["Staging Area<br/>(Raw Data)"]
    B --> C["ETL Process<br/>(Clean, Transform)"]
    C --> D["Data Warehouse<br/>(Star Schema)"]
    D --> E["Data Marts<br/>(Sales, Marketing)"]
    E --> F["BI Tools<br/>(Power BI, Tableau)"]

4. ETL vs. ELT: Data Integration Methods

ETL (Extract-Transform-Load)

• Extract data from sources.
• Transform (clean, aggregate).
• Load into the warehouse.
• Example: Ncell extracts call records, transforms to remove duplicates, loads into a DW for churn analysis.

ELT (Extract-Load-Transform)

• Extract → Load raw data into a cloud DW (e.g., Snowflake).
• Transform using powerful compute (e.g., Spark).
• Used by: Google Analytics (scales for billions of events).

5. Data Warehouse Schemas

Star Schema

• Central fact table linked to dimension tables.
• Example: Sales DW with:
  • Fact: Sales_Transactions (quantity, amount).
  • Dimensions: Customers, Products, Time.

graph TD
    A["Fact: Sales_Transactions"] --> B["Dim: Customers"]
    A --> C["Dim: Products"]
    A --> D["Dim: Time"]

Snowflake Schema

• Normalized dimensions (e.g., Product split into Product_Category and Product_Subcategory).
• Used when: Dimension tables are large (e.g., NEPSE’s stock data).

6. In the Real World

1. Daraz’s Inventory Management

   • Idea: Data warehouses store historical sales data to predict stockouts.
   • How: Uses ETL to pull transaction data → cleans it → loads into a star schema → runs OLAP queries to forecast demand.
   • Worked Example:
     • Data: 2023 sales of "laptops" = 500 units/month (Jan–Jun), 800 units/month (Jul–Dec).
     • Query: "What’s the 90-day moving average for Q1 2024?"
     • Answer: (500+600+700)/3 = 600 units (assuming linear growth).

2. Ncell’s Customer Churn Analysis

   • Idea: Data marts (subset of DW) track call duration, data usage, and complaints.
   • How: OLAP identifies patterns (e.g., "Customers with <100MB data usage churn 3x more").
   • Worked Example:
     • Data:

       UserID	Data_Usage_MB	Churned?
       1001	50	Yes
       1002	200	No

     • OLAP Query: "Average data usage for churned vs. non-churned users."
     • Result:
       • Churned: 75 MB
       • Non-churned: 180 MB
     • Action: Ncell targets users with <150MB usage for retention offers.

3. Khalti’s Transaction Fraud Detection

   • Idea: Data warehouses log all payment attempts (successful/failed) to detect anomalies.
   • How: Association rule mining finds patterns like:
     • "Users who pay at 3 AM are 90% likely to be fraudulent."
   • Worked Example:
     • Data:
       • Rule: {Time: 3:00 AM} → {Fraud: Yes} with support=0.05 (5% of transactions) and confidence=0.9 (90% accuracy).
     • Impact: Khalti flags 3 AM transactions for manual review.

7. Advantages and Disadvantages

8. Exam Tip

• Focus on definitions: Know the 4 Vs (subject-oriented, integrated, time-variant, non-volatile).
• Compare OLTP vs. DW: Use the table above; examiners love this contrast.
• ETL vs. ELT: Explain when to use each (e.g., ETL for small-scale, ELT for cloud).
• Schemas: Draw a star schema for a given business scenario (e.g., "Design a DW for a hospital’s patient records").
• Real-world tie-ins: Relate to Nepalese companies (e.g., "How would a data warehouse help NEPSE track market trends?").
• OLAP vs. OLTP: OLAP = analytical queries; OLTP = transactional.

Worked Example for Exam Practice Question: Design a star schema for a university’s student performance DW. Answer:

1. Fact Table: Student_Performance (fields: StudentID, CourseID, Semester, GPA, Credits).
2. Dimension Tables:
   • Students (StudentID, Name, Department)
   • Courses (CourseID, CourseName, Credits)
   • Semesters (SemesterID, Year, Term)
3. Query Example:

   SELECT s.Department, AVG(f.GPA)
   FROM Student_Performance f
   JOIN Students s ON f.StudentID = s.StudentID
   GROUP BY s.Department;
   

   Output:

   Department	Avg_GPA
   IT	3.2
   Business	3.5

Visual Recap

graph TD
    A["Data Warehouse"] --> B["ETL Pipeline"]
    A --> C["Star Schema"]
    A --> D["OLAP Queries"]
    B --> E["Source Systems"]
    B --> F["Staging Area"]
    C --> G["Fact Tables"]
    C --> H["Dimension Tables"]