Data Warehousing & Data Mining: Architecture, ETL, OLAP, and Predictive Analytics

Key Concepts and Definitions

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 an organization. Unlike operational databases (OLTP), it stores historical data optimized for complex queries and analytical processing (OLAP).

Key Characteristics:

How Data Warehouses Work: ETL Process

Data warehouses are built using ETL (Extract, Transform, Load) processes. This involves:

1. Extracting data from source systems (e.g., ERP, CRM, transactional databases).
2. Transforming data (cleaning, aggregating, standardizing formats).
3. Loading data into the data warehouse (often in a star schema or snowflake schema).

flowchart TD
    A["Source Systems<br/>(ERP, CRM, OLTP)"] -->|"Extract"| B["ETL Process"]
    B --> C["Transform<br/>(Clean, Aggregate, Standardize)"]
    C --> D["Load<br/>into Data Warehouse"]
    D --> E["Data Marts<br/>(Sales DW, HR DW)"]
    E --> F["OLAP Tools<br/>(Power BI, Tableau)"]
    F --> G["Business Intelligence<br/>(Dashboards, Reports)"]

Example: eSewa’s Data Warehouse

• Source: Transaction logs (payments, billings), user profiles, merchant data.
• ETL: Aggregates daily transactions into monthly/yearly summaries.
• Output: Dashboards showing peak payment hours, fraud patterns, and customer retention trends.

Data Warehouse Architecture

A typical data warehouse follows a multi-layered architecture:

Key Components:

1. Staging Area: Holds raw data before transformation.
2. Data Warehouse Layer: Stores fact tables (measures) and dimension tables (descriptors).
3. Data Marts: Subsets of the DW for specific departments (e.g., Sales Data Mart, HR Data Mart).
4. OLAP Server: Enables multidimensional analysis (e.g., "Sales by Region, Product, and Quarter").

OLAP vs. OLTP

Example: Ncell’s OLAP System

• OLTP: Processes real-time call logs (who called whom, duration).
• OLAP: Analyzes monthly call patterns to predict peak hours and optimize network load balancing.

Data Mining: Extracting Patterns from Data

Data mining is the process of discovering patterns, correlations, and insights from large datasets using statistical methods, machine learning, and AI.

Key Data Mining Techniques

Worked Example: Customer Segmentation for Daraz

Problem: Daraz wants to personalize recommendations for customers. Data Mining Technique: Clustering (K-Means Algorithm).

Steps:

1. Collect Data: Purchase history, browsing behavior, demographics.
2. Preprocess: Normalize data, handle missing values.
3. Apply K-Means:
   • Group customers into 3 clusters:
     • High Spenders (frequent buyers, high cart value).
     • Bargain Hunters (buy during sales, low average order value).
     • Occasional Buyers (infrequent purchases).
4. Action: Tailor discounts and recommendations per cluster.

Real-World Applications

1. eSewa: Fraud Detection with Data Mining

• Problem: Detect fake transactions or identity theft.
• Technique: Anomaly Detection (Isolation Forest Algorithm).
• How it Works:
  • Trains on normal transaction patterns (time, amount, location).
  • Flags transactions that deviate significantly (e.g., sudden large payment from a new device).
  • Result: Reduces fraud by 30% in high-risk areas.

2. NTC: Predictive Maintenance for Power Grids

• Problem: Avoid power outages due to equipment failure.
• Technique: Time-Series Forecasting (ARIMA Model).
• How it Works:
  • Analyzes historical data on transformer temperatures, voltage fluctuations.
  • Predicts failure risk and schedules maintenance proactively.
  • Result: Reduces downtime by 25%.

3. Pathao: Dynamic Pricing with OLAP

• Problem: Optimize ride prices based on demand.
• Technique: OLAP Cubes (Drill-Down Analysis).
• How it Works:
  • Aggregates ride data by time, location, and driver availability.
  • Adjusts prices in real-time during peak hours (e.g., 20% surge in Thapathali at 8 PM).
  • Result: 15% increase in driver earnings and better customer satisfaction.

Data Warehousing vs. Data Mining: Key Differences

Exam Tip: How to Score Full Marks

1. Define Clearly:

   • Data Warehouse: Always mention subject-oriented, integrated, time-variant, non-volatile.
   • Data Mining: Define as "discovering patterns from large datasets using statistical/AI methods."

2. Compare OLAP vs. OLTP:

   • Use a table (as above) and relate to real examples (e.g., Ncell’s call logs vs. sales trends).

3. ETL Process:

   • Draw a flowchart (as above) and link to a business case (e.g., eSewa’s payment data).

4. Data Mining Techniques:

   • Match techniques to examples:
     • Classification → Spam filter.
     • Clustering → Customer segmentation (Daraz).
     • Association Rules → "Buy X, get Y" (Amazon).

5. Worked Examples:

   • Always tie to Nepalese companies (eSewa, Daraz, NTC) for contextual marks.
   • Show SQL-like pseudocode for OLAP queries (e.g., SELECT Region, SUM(Sales) FROM Sales WHERE Year=2023 GROUP BY Region).

6. Diagrams Are Mandatory:

   • Star Schema for DW structure.
   • OLAP Cube for multidimensional analysis.
   • K-Means Clustering for data mining.