Data Warehousing: Concepts, Architecture & OLAP

TAKEAWAYS:

• Data warehousing is a subject-oriented, integrated, time-variant, and non-volatile repository designed for analytical processing, not transactional operations.
• The three-tier architecture (data sources → ETL → data warehouse → front-end tools) ensures scalability and performance for complex queries.
• OLAP enables multidimensional analysis (drill-down, roll-up, slicing, and dicing) to support strategic decision-making.
• Unlike OLTP, data warehouses prioritize read-heavy operations with optimized storage (star/snowflake schemas) and indexing.
• Real-world use: E-commerce platforms (Daraz) use data warehouses to analyze customer behavior, while banks (NMB) leverage OLAP for fraud detection.
• Exam focus: Define key terms, distinguish OLTP vs. OLAP, and explain ETL processes with examples.

1. What is a Data Warehouse?

A data warehouse (DW) is a centralized, subject-oriented repository that stores integrated, historical, and non-volatile data from multiple operational sources. It supports analytical processing (e.g., reporting, trend analysis) rather than transactional processing (OLTP).

Key Characteristics

Compare these with Operational Databases (OLTP) in the table below:

Why is it "non-volatile"? Data in a DW is never modified or deleted—only new data is added (append-only). This ensures consistency for historical analysis.

2. Architecture of a Data Warehouse

A typical DW follows a three-tier architecture:

flowchart LR
    A["Operational Sources\n(ERP, CRM, POS)"] -->|"ETL Process"| B["Data Warehouse\n(Fact Tables + Dimensions)"]
    B --> C["Front-End Tools\n(OLAP, Dashboards, BI Tools)"]

Components Explained

1. Data Sources (Bottom Tier)

   • Operational databases (e.g., eSewa’s transaction logs, NTC’s billing systems).
   • External data (e.g., weather APIs for logistics, NEPSE stock data).

2. ETL Process (Middle Tier)

   • Extract: Pull data from sources (e.g., Khalti’s payment records).
   • Transform: Clean, aggregate, and standardize (e.g., convert currencies for Daraz’s global sales).
   • Load: Store in the DW (e.g., Pathao’s ride analytics).

   Worked Example: Cleaning NEPSE Stock Data

   • Dirty Data: Missing values in "Dividend Yield" column.
   • Transformation:
     • Replace NULL with 0 (assuming no dividend).
     • Convert text "NPR" to numeric values.
   • Output: A clean fact table for trend analysis.

3. Data Warehouse (Middle Tier)

   • Fact Tables: Store quantitative data (e.g., sales amount, customer count).
   • Dimension Tables: Store descriptive attributes (e.g., product name, date, region).
   • Schema Types:
     • Star Schema: Simple, fact table connected directly to dimensions (used by Google Analytics).
     • Snowflake Schema: Normalized dimensions (used by enterprise DWs like SAP BW).

   graph TD
     A["Fact: Sales"] --> B["Dimension: Product"]
     A --> C["Dimension: Date"]
     A --> D["Dimension: Customer"]

4. Front-End Tools (Top Tier)

   • OLAP Servers (e.g., Microsoft Analysis Services).
   • BI Tools (e.g., Power BI, Tableau for Ncell’s customer segmentation).
   • Data Mining Tools (e.g., Python’s Pandas for Khalti’s fraud detection).

3. OLAP vs. OLTP: A Deep Dive

Real-World Analogy: Kathmandu Traffic vs. Google Maps

• OLTP: Like a traffic police officer managing real-time traffic (live updates, instant actions).
• OLAP: Like Google Maps’ "Traffic Jam Prediction"—analyzing historical data to predict congestion patterns.

4. ETL Process: Step-by-Step

ETL (Extract, Transform, Load) is the backbone of a DW. Let’s trace how NTC’s billing data becomes a DW-ready dataset.

Step 1: Extract

• Source: NTC’s billing system database (PostgreSQL).
• Extracted Data:

  SELECT customer_id, usage_date, minutes_used, tariff_plan
  FROM billing_records
  WHERE usage_date BETWEEN '2023-01-01' AND '2023-12-31';
  

Step 2: Transform

• Cleaning:
  • Remove duplicate entries (e.g., same customer_id with minutes_used=0).
  • Handle missing values (e.g., tariff_plan=NULL → default to "Standard").
• Aggregation:
  • Calculate monthly usage per customer:

    SELECT customer_id, DATE_TRUNC('month', usage_date) AS month,
           SUM(minutes_used) AS total_minutes
    FROM cleaned_data
    GROUP BY customer_id, month;
    

• Standardization:
  • Convert usage_date to a consistent format (e.g., YYYY-MM-DD).

Step 3: Load

• Target: DW’s fact table (fact_billing).
• Schema:

  graph LR
    A["fact_billing"] --> B["dim_customer"]
    A --> C["dim_date"]
    A --> D["dim_tariff"]

Output: A fact table with pre-aggregated metrics for NTC’s analysts to:

• Identify high-usage customers (for upselling).
• Detect fraudulent patterns (e.g., sudden spikes in minutes).

5. OLAP Operations: How to "Slice and Dice" Data

OLAP enables multidimensional analysis using these operations:

Worked Example: Analyzing Khalti Transactions

• Question: Which payment method (Khalti Pay, Bank Transfer, Mobile) drives the most weekly sales in Lalitpur?
• OLAP Steps:
  1. Slice: Filter by region = "Lalitpur".
  2. Dice: Group by payment_method and week.
  3. Pivot: Compare methods side-by-side.

-- MDX Query (simplified)
SELECT [payment_method].MEMBERS ON COLUMNS,
       [week].MEMBERS ON ROWS
FROM [Khalti_Sales]
WHERE [region] = "Lalitpur"

Visual Output:

graph TD
  A["Total Sales"] --> B["Khalti Pay\nNPR 500,000"]
  A --> C["Bank Transfer\nNPR 300,000"]
  A --> D["Mobile\nNPR 200,000"]

6. Data Warehouse vs. Data Lake vs. Data Mart

When to Use Which?

• DW: Structured, business-critical data (e.g., NEPSE’s financial reports).
• Data Lake: Raw data (e.g., WhatsApp’s message logs for sentiment analysis).
• Data Mart: Departmental needs (e.g., NTC’s customer service team).

7. Real-World Applications in Nepal

Example 1: Daraz’s Inventory Optimization

• Problem: Daraz needs to predict demand spikes (e.g., Dashain sales).
• Solution:
  • Data Warehouse: Stores historical sales data, supplier lead times, and promotional data.
  • OLAP: Analysts drill down to see which products sell best in Lalitpur vs. Pokhara.
  • Outcome: Reduces stockouts by 30% and overstocking by 20%.

Example 2: NMB Bank’s Loan Default Prediction

• Problem: Identify customers likely to default on loans.
• Solution:
  • ETL: Extracts transaction history, credit scores, and demographics from operational systems.
  • Data Warehouse: Stores aggregated risk factors (e.g., "customers with >3 late payments").
  • OLAP: Bank officers slice data by loan amount and region to flag high-risk applicants.
  • Outcome: Reduces bad loans by 15%.

Example 3: NTC’s Network Performance Analysis

• Problem: Why do internet speeds drop in certain areas?
• Solution:
  • Data Lake: Stores raw network logs (unstructured).
  • Data Warehouse: Aggregates hourly speed tests, device types, and location data.
  • OLAP: Engineers dice data by time of day and district to find patterns.
  • Outcome: Identifies congestion hotspots in Thapathali, leading to infrastructure upgrades.

8. Challenges in Data Warehousing

Exam Tip

How to Score Full Marks in TU/PU Exams for Unit 1:

1. Definitions:

   • Memorize key terms with examples:
     • "A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile* collection of data..."*
   • OLAP vs. OLTP: Always compare purpose, schema, and query types.

2. Diagrams:

   • Draw the three-tier architecture and star/snowflake schemas.
   • Label ETL steps with a real example (e.g., NTC billing).

3. Worked Examples:

   • ETL Trace: Show one transformation (e.g., handling NULL values in Khalti transactions).
   • OLAP Operations: Apply drill-down/roll-up to a Daraz sales scenario.

4. Applications:

   • Link concepts to Nepali companies:
     • Daraz: Data warehousing for inventory management.
     • NMB: OLAP for fraud detection.
     • NTC: DW for network optimization.

5. Common Pitfalls:

   • ❌ Saying a DW is for real-time transactions (that’s OLTP!).
   • ❌ Forgetting "non-volatile" in the definition.
   • ❌ Confusing data lake (raw) with data warehouse (structured).

Sample Exam Question & Answer: Q: "Explain the ETL process with an example from a Nepali company. How does OLAP help in decision-making?" A:

1. ETL Example (NTC):
   • Extract: Pull billing records from PostgreSQL.
   • Transform: Clean NULL values, aggregate by month.
   • Load: Store in a fact table with dimensions (customer, date, tariff).
2. OLAP Role:
   • NTC analysts use drill-down to find why internet speeds drop in Bhaktapur.
   • Dice data by time of day and district to pinpoint peak congestion hours.
   • Result: Targeted infrastructure upgrades in high-traffic areas.

Final Checklist Before Exam:

• Can you draw the three-tier DW architecture?
• Can you list 4 OLAP operations with a Daraz example?
• Can you explain ETL using NMB’s loan data?
• Do you know 2 Nepali companies using DW/OLAP?