Data Warehousing Basics: Definitions, Types, and OLTP vs. OLAP

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 in organizations. Unlike operational databases (OLTP), which focus on transaction processing, data warehouses are optimized for analytical processing (OLAP).

Key Characteristics of a Data Warehouse

mindmap
  root((Data Warehouse))
    Subject-Oriented
    Integrated
    Time-Variant
    Non-Volatile
    Summary Data

• Subject-Oriented: Organized around major subjects like customers, products, or sales.
• Integrated: Data from multiple sources is consolidated and cleaned.
• Time-Variant: Stores historical data for trend analysis.
• Non-Volatile: Data is never updated or deleted; only new data is added.
• Summary Data: Aggregated data (e.g., monthly sales) is precomputed for faster queries.

Why Do We Need Data Warehouses?

Operational databases (OLTP) are optimized for fast transaction processing (e.g., bank withdrawals, order placements). However, they struggle with:

• Complex analytical queries (e.g., "Which products sold best in 2023?").
• Historical data analysis (OLTP systems often purge old data).
• Performance under heavy reporting loads.

A data warehouse solves these problems by:

1. Storing historical data (years of records).
2. Pre-aggregating data (summaries for faster queries).
3. Supporting ad-hoc queries (e.g., "Show me sales trends by region").

Data Warehouse vs. Operational Database (OLTP)

Types of Data Warehouses

1. Enterprise Data Warehouse (EDW)

• Stores all organizational data in a single repository.
• Example: A bank’s centralized system for loans, deposits, and customer data.
• Pros: Unified view, comprehensive analytics.
• Cons: High cost, complex to maintain.

2. Data Mart

• A subset of a data warehouse focused on a specific department (e.g., sales, finance).
• Example: A retail data mart for Daraz’s inventory analysis.
• Pros: Faster queries, lower cost.
• Cons: Limited scope, risk of data silos.

3. Operational Data Store (ODS)

• Hybrid system combining OLTP and OLAP for near-real-time analytics.
• Example: Nepal Rastra Bank’s system for tracking daily transactions and fraud detection.
• Pros: Balances speed and analytics.
• Cons: Complex to design.

4. Virtual Data Warehouse

• Uses federated queries to pull data from multiple sources on-demand (no physical storage).
• Example: Google BigQuery (queries data across Google’s ecosystem without loading it).
• Pros: No storage costs, scalable.
• Cons: Slower queries, dependency on source systems.

How Data Warehouses Work: ETL Process

Data warehouses are built using the ETL (Extract, Transform, Load) process:

flowchart LR
    A["Source Systems"] -->|"Extract"| B["Data Extraction"]
    B --> C["Data Transformation"]
    C -->|"Clean, Aggregate, Enrich"| D["Data Loading"]
    D --> E["Data Warehouse"]
    E --> F["OLAP Tools"]
    F --> G["Business Intelligence"]

1. Extract: Pull data from transactional databases, ERP systems, or flat files.
   • Example: Extract Khalti transaction logs for fraud detection.
2. Transform: Clean, standardize, and aggregate data.
   • Example: Convert NTC’s call records into monthly usage reports.
3. Load: Store data in the warehouse (either batch or real-time).

In the Real World

1. eSewa (Nepal)

   • Uses a data warehouse to analyze transaction patterns (e.g., peak payment times, failed transactions).
   • How? Historical data helps predict demand for electricity bill payments during monsoon seasons.

2. Daraz (Alibaba Group)

   • Data mart for inventory management tracks stock levels across Nepal’s warehouses.
   • How? Predicts restocking needs using past sales trends (e.g., increased demand for umbrellas before monsoon).

3. Nepal Rastra Bank (NRB)

   • Operational Data Store (ODS) monitors real-time financial transactions while storing historical data for economic trend analysis.
   • How? Detects unusual patterns (e.g., sudden large withdrawals) for anti-money laundering (AML) checks.

Worked Example: Building a Simple Data Warehouse for a Retail Store

Scenario: A small shop in Kathmandu wants to analyze monthly sales to decide which products to stock more of.

Step 1: Define the Subject Area

• Focus: Sales data (products, quantities, dates, prices).

Step 2: Extract Data from OLTP

Assume the OLTP system has tables:

• Sales (sale_id, product_id, quantity, sale_date, customer_id)
• Products (product_id, product_name, price)

Sample OLTP Data (Last 3 Months):

Step 3: Transform Data

• Aggregate by month and product:

  SELECT product_id, product_name, SUM(quantity) as total_quantity, SUM(quantity * price) as total_sales
  FROM Sales s JOIN Products p ON s.product_id = p.product_id
  GROUP BY product_id, product_name, MONTH(sale_date), YEAR(sale_date)
  

• Result (Data Warehouse Table):

  product_id	product_name	month_year	total_quantity	total_sales
  P001	Umbrella	2023-10	50	75,000
  P002	Shoes	2023-10	30	180,000

Step 4: Load into Data Warehouse

• Store the aggregated data in a star schema (fact table + dimension tables).
• Fact Table: Sales_Fact (month_year, product_id, total_quantity, total_sales)
• Dimension Tables:
  • Products_Dim (product_id, product_name, category)
  • Time_Dim (month_year, month_name, quarter)

erDiagram
    Sales_Fact ||--o{ Products_Dim : contains
    Sales_Fact ||--o{ Time_Dim : tracks
    Products_Dim {
        product_id PK
        product_name
        category
    }
    Time_Dim {
        month_year PK
        month_name
        quarter
    }
    Sales_Fact {
        month_year FK
        product_id FK
        total_quantity
        total_sales
    }

Step 5: Query the Data Warehouse

Question: "Which product had the highest sales in October 2023?"

SELECT p.product_name, sf.total_sales
FROM Sales_Fact sf JOIN Products_Dim p ON sf.product_id = p.product_id
WHERE sf.month_year = '2023-10'
ORDER BY sf.total_sales DESC
LIMIT 1;

Answer: Shoes (₹180,000).

Data Warehousing and Data Mining Connection

Data warehouses feed data mining by providing:

1. Clean, integrated data (no duplicates or inconsistencies).
2. Historical trends (e.g., seasonal sales patterns).
3. Aggregated summaries (faster mining algorithms).

Example Workflow:

1. Data Warehouse stores Nepal’s stock market (NEPSE) data for 10 years.
2. Data Mining applies association rule mining to find:
   • "When the index rises by 2%, gold stocks tend to rise by 1.5%."

Challenges in Data Warehousing

1. Data Quality Issues

   • Dirty data (missing values, duplicates) leads to wrong insights.
   • Solution: Use ETL tools (e.g., Informatica, Talend) for cleaning.

2. High Storage Costs

   • Storing years of data is expensive.
   • Solution: Use columnar storage (e.g., Parquet format) to compress data.

3. Slow Query Performance

   • Complex OLAP queries can be slow.
   • Solution: Materialized views (precomputed results).

4. Security and Privacy

   • Sensitive data (e.g., customer records) must be protected.
   • Solution: Role-based access control (RBAC).

Exam Tip

What to Expect in TU Exams:

1. Definitions: Be ready to explain data warehouse, OLTP, OLAP, ETL, and data mart with examples.
2. Comparisons: Expect OLTP vs. OLAP or EDW vs. Data Mart tables.
3. ETL Process: Draw a flowchart of ETL and explain each step.
4. Real-World Scenarios:
   • "How would you design a data warehouse for a bank?"
   • "Explain how Daraz uses data warehouses for inventory management."
5. Short Questions:
   • "What are the 5 characteristics of a data warehouse?"
   • "Why is a data warehouse non-volatile?"

Key Formula to Remember:

• Data Warehouse Size ≈ (Number of Years × Daily Data Volume × Compression Ratio).
  • Example: If a company generates 10 GB/day and stores 5 years of data with 3:1 compression, the warehouse size ≈ (5 × 10 × 365) / 3 ≈ 6,083 GB.