Data Warehousing: Architecture, ETL, OLAP & Business Use

What is Data Warehousing?

Data warehousing is a centralized repository that stores integrated, subject-oriented, time-variant, and non-volatile data from multiple sources to support business intelligence (BI) and decision-making. Unlike operational databases (OLTP), which focus on transaction processing, data warehouses optimize query performance and analytical reporting.

Key Characteristics of a Data Warehouse

mindmap
  root((Data Warehouse))
    Characteristics
      Subject-Oriented: Organized by business functions (e.g., sales, finance)
      Integrated: Data from heterogeneous sources (ERP, CRM, Excel) is cleaned and standardized
      Time-Variant: Historical data is stored (e.g., sales trends over 5 years)
      Non-Volatile: Data is never updated or deleted; only new data is added
    Purpose
      Decision Support: Enables strategic analytics (e.g., "Which product drives 30% of revenue?")
      Reporting: Generates KPIs, dashboards, and ad-hoc queries
      Data Mining: Supports predictive modeling (e.g., customer churn analysis)

Data Warehouse Architecture

A typical data warehouse follows a multi-layered architecture:

How fact tables (sales) link to dimension tables (product, date, customer) (Image: SqlPac, CC BY-SA 3.0, via Wikimedia Commons)

ETL (Extract, Transform, Load) Process

ETL is the backbone of data warehousing, ensuring data is clean, consistent, and ready for analysis.

flowchart LR
  A["Extract"] --> B["Transform"]
  B --> C["Load"]
  A -->|"From"| D["Source Systems<br/>(ERP, CRM, Excel)"]
  B -->|"Clean, Aggregate,<br/>Standardize"| E["Staging Area"]
  C -->|"Into"| F["Data Warehouse"]
  F -->|"To"| G["OLAP Cubes<br/>Dashboards"]

Worked Example: Nabil Bank’s Loan Default Prediction

1. Extract: Pull loan application data (borrower details, credit score, income) from Nabil Bank’s core system.
2. Transform:
   • Clean missing values (e.g., fill "unknown" income with median income).
   • Aggregate monthly transactions into yearly summaries.
   • Encode categorical data (e.g., "Urban/Rural" → 1/0).
3. Load: Store in a data warehouse with dimensions:
   • Fact Table: Loan_ID, Amount, Default_Status (0/1)
   • Dimensions: Customer, Location, Loan_Type, Time

OLAP (Online Analytical Processing)

OLAP enables multidimensional analysis of data stored in OLAP cubes. Unlike OLTP (transactional), OLAP supports:

• Slice & Dice: View data by different dimensions (e.g., "Sales in Kathmandu for Q1 2023").
• Drill-Down/Up: Move from summary to detail (e.g., "Why did Q1 sales drop? → Check regional performance").
• Pivoting: Rotate axes (e.g., compare "Sales by Product" vs. "Sales by Region").

OLAP vs. OLTP

Data Warehouse vs. Database vs. Data Lake

Real-World Applications in Nepal

1. Nabil Bank’s Customer Segmentation

• Idea Used: Data warehousing + OLAP
• How:
  • ETL: Extracts transaction data from core banking → transforms into customer segments (high-value, churn-risk) → loads into a data warehouse.
  • OLAP: Analysts "slice" data by Customer_Segment, Loan_Type, and Time to identify cross-selling opportunities.
  • Outcome: Targeted marketing (e.g., "Offer credit cards to high-spend customers").

2. Daraz’s Inventory Optimization

• Idea Used: Data mart + predictive analytics
• How:
  • Data Mart: Subset of warehouse data for supply chain team (only Product_ID, Stock_Level, Sales_Velocity).
  • OLAP: "Drill down" to see which products in Pokhara have low stock but high demand.
  • Outcome: Reduces overstocking by 20% using automated reorder alerts.

3. NTC’s Network Performance Monitoring

• Idea Used: Real-time data warehousing
• How:
  • ETL: Pulls call drop data from cell towers → aggregates by Tower_ID, Time, Network_Type.
  • OLAP: Identifies "hotspots" (e.g., "Why is call quality poor in Bhaktapur at 6 PM?").
  • Outcome: Deploys additional towers in high-traffic areas.

Advantages and Disadvantages

Advantages

• Single Source of Truth: Eliminates data silos (e.g., Finance and Marketing using the same customer data).
• Faster Queries: Optimized for analytics (e.g., "Which 5 products contribute to 80% of revenue?").
• Historical Analysis: Tracks trends over years (e.g., "How did COVID-19 impact e-commerce?").
• Decision Support: Enables data-driven strategies (e.g., NEPSE’s algorithmic trading).

Disadvantages

• High Cost: Requires hardware, ETL tools (e.g., Informatica), and skilled personnel.
• Latency: Data is not real-time (batch processing delays).
• Complexity: Designing schemas (star vs. snowflake) and maintaining data quality is challenging.
• Storage Overhead: Historical data consumes significant space.

Case Study: Chaudhary Group’s Supply Chain Analytics

Problem: Chaudhary Group (owners of Himalayan Java, Bhatbhateni) struggled with demand forecasting across 50+ outlets. Solution: Implemented a data warehouse with:

1. ETL Pipeline:
   • Extracted POS data from all outlets.
   • Transformed to standardize units (e.g., "kg" vs. "lbs" for coffee beans).
   • Loaded into a star schema with facts: Sales_Amount, Quantity; dimensions: Product, Store, Date.
2. OLAP Analysis:
   • Used slice-and-dice to find:
     • "Which stores in Kathmandu have 30% higher coffee sales on weekends?"
     • "Which products have declining demand in Pokhara?"
3. Outcome:
   • Reduced overstocking by 15% by adjusting orders based on predictive trends.
   • Increased revenue by 12% through targeted promotions (e.g., "Buy 1 kg, get 200g free").

Exam Tip

1. Define Clearly: Always start with a one-sentence definition of data warehousing (e.g., "A subject-oriented, integrated, time-variant repository...").
2. Draw Diagrams: Expect questions on:
   • Architecture layers (source → staging → warehouse → mart → OLAP).
   • Star vs. snowflake schemas (show how dimensions normalize).
3. ETL Example: Be ready to trace data flow (e.g., "How would you load NEPSE stock data into a warehouse?").
4. OLAP Operations: Practice slice, dice, drill-down on a sample cube (e.g., "Given a sales cube, how would you find Q1 2023 sales in Kathmandu for product X?").
5. Real-World Link: Relate to Nepali companies (e.g., "How would Nabil Bank use a data warehouse for fraud detection?").
6. Advantages/Disadvantages: Compare data warehouse vs. database vs. data lake in a table (as above).
7. Case Study: Know one Nepali example (e.g., NTC, Nabil Bank, Daraz) and explain its ETL + OLAP process.

Key Formula to Remember: For data warehouse size estimation: $$\text{Storage Requirement}=\text{Daily Data Volume}\times \text{Retention Period (days)}\times \text{Compression Factor (0.3–0.5)}$$ Example: If NTC generates 100GB/day and retains 5 years (1825 days) with 40% compression: $$100\text{GB}\times 1825\times 0.4=73,000\text{GB (73TB)}$$