Data Warehousing & Mining: Definitions, Systems, and Applications

1. Core Definitions: What Are Data Warehousing and Data Mining?

Data Warehousing

A data warehouse (DW) is a subject-oriented, integrated, time-variant, and non-volatile collection of data designed to support decision-making in organizations. It consolidates data from multiple operational databases (OLTP systems) into a unified repository for analytical processing (OLAP).

Data Mining

Data mining is the process of discovering hidden patterns, correlations, and trends in large datasets using statistical, machine learning, and database techniques. It answers questions like:

• "Which products are frequently bought together?" (Association rules)
• "Can we predict customer churn?" (Classification)
• "How can we group similar customers?" (Clustering)

2. Key Components of a Data Warehouse

A typical DW system consists of:

3. OLTP vs. OLAP: The Core Difference

flowchart LR
    A["OLTP (Operational Systems)"] -->|"Focus"| B["Transactions\n(CRUD operations)"]
    A -->|"Users"| C["End-users\n(Clerks, Customers)"]
    A -->|"Data"| D["Normalized\n(3NF, BCNF)"]
    A -->|"Response Time"| E["Fast\n(<2 sec)"]
    A -->|"Example"| F["Bank ATM\n(Khalti transactions)"]

    G["OLAP (Analytical Systems)"] -->|"Focus"| H["Analytics\n(Aggregations, Trends)"]
    G -->|"Users"| I["Analysts\n(Managers, Data Scientists)"]
    G -->|"Data"| J["Denormalized\n(Data Warehouse)"]
    G -->|"Response Time"| K["Slow\n(Minutes to hours)"]
    G -->|"Example"| L["NTC Traffic Analysis\n(eSewa service trends)"]

Why OLAP Needs a Data Warehouse?

• OLTP systems (e.g., bank databases) are optimized for transactions, not analysis.
• OLAP requires:
  • Aggregated data (pre-computed summaries).
  • Historical data (time-series trends).
  • Multidimensional views (e.g., "Sales by region, product, and quarter").

Example:

• OLTP: A customer buys a phone on Daraz (single transaction).
• OLAP: Analyzing "Which regions buy iPhones vs. Xiaomi in Q3 2023?"

4. Data Warehouse Schemas: Star vs. Snowflake

Star Schema

• Simplest design: 1 fact table (numeric measures) + dimension tables (descriptive attributes).
• Pros: Fast queries, easy to understand.
• Cons: Redundancy in dimension tables.

Snowflake Schema

• Normalized dimensions: Dimension tables are further broken down (e.g., Date splits into Year, Month, Day).
• Pros: Reduces redundancy.
• Cons: Slower queries due to joins.

Real-World Example:

• Star Schema: eSewa’s dashboard showing "Total transactions by district, month, and service type."
• Snowflake Schema: NTC’s DW breaking down Date into Year, Quarter, Weekday for granular traffic analysis.

5. ETL Process: How Raw Data Becomes Usable

ETL stands for Extract, Transform, Load—the pipeline that moves data into a DW.

flowchart LR
    A["Extract"] -->|"From"| B["OLTP DBs\nAPIs\nFlat Files"]
    B --> C["Transform"]
    C -->|"Steps"| D["Cleaning\nNormalization\nAggregation\nDiscretization"]
    C --> E["Load"]
    E --> F["Data Warehouse\n(Star/Snowflake)"]
    F --> G["OLAP Engine\n(BI Tools)"]

Key ETL Tasks

6. OLAP Operations: Slicing, Dicing, Drilling

OLAP enables multidimensional analysis using:

7. Data Mining Tasks: What Can We Discover?

Data mining tasks are categorized into descriptive (summarizing data) and predictive (making predictions).

Descriptive Tasks

Predictive Tasks

8. Data Mining Process: From Data to Insights

flowchart TD
    A["Business Understanding"] --> B["Data Understanding"]
    B --> C["Data Preparation"]
    C --> D["Modeling"]
    D --> E["Evaluation"]
    E --> F["Deployment"]
    F --> G["Feedback Loop"]

Step-by-Step Workflow

1. Business Understanding
   • Define the goal (e.g., "Reduce customer churn for Ncell").
2. Data Understanding
   • Explore data sources (e.g., Ncell’s call logs, billing data).
3. Data Preparation (ETL)
   • Clean, transform, and load data into a DW.
4. Modeling
   • Apply algorithms (e.g., decision trees for classification).
5. Evaluation
   • Test model accuracy (e.g., 85% precision in churn prediction).
6. Deployment
   • Integrate into business processes (e.g., Ncell’s CRM system).
7. Feedback Loop
   • Monitor and retrain the model (e.g., quarterly updates).

9. Real-World Applications in Nepal

Example 1: Khalti’s Fraud Detection (Association Rules + Clustering)

• Problem: Khalti needs to detect unusual transaction patterns (e.g., a merchant suddenly processing 10x more transactions).
• Solution:
  • Association Rules: Mine rules like {Merchant_X, High_Amount} → {Fraud}.
  • Clustering: Group merchants by transaction behavior; flag outliers.
• Outcome: Reduced fraudulent transactions by 30%.

Example 2: Daraz’s Recommendation Engine (Collaborative Filtering)

• Problem: Daraz wants to recommend products to users.
• Solution:
  • Clustering: Group users by purchase history (e.g., "Tech Enthusiasts," "Fashion Lovers").
  • Association Rules: Find frequent itemsets (e.g., {Laptop} → {Mouse}).
• Outcome: 20% increase in cross-selling revenue.

Example 3: NTC’s Traffic Prediction (Time-Series + Classification)

• Problem: NTC wants to predict traffic congestion to optimize routes.
• Solution:
  • Time-Series Analysis: Forecast traffic volume using historical data.
  • Classification: Predict "High," "Medium," or "Low" congestion zones.
• Outcome: Reduced travel time by 15% during peak hours.

10. Worked Example: K-Means Clustering (From Past Exam)

Problem: Apply K(=2)-Means algorithm to the dataset: (185, 72), (170, 56), (168, 60), (179, 68), (182, 72), (188, 77) Initial Centroids: First two points (185, 72) and (170, 56).

Iteration 1

1. Assign Points to Nearest Centroid:

   • Distance formula: $d=\sqrt{(x_2-x_1{)}^2+(y_2-y_1{)}^2}$.
   • Calculate distances for each point to both centroids.

   Point	Distance to (185,72)	Distance to (170,56)	Cluster
   (185,72)	0	18.4	1
   (170,56)	18.4	0	2
   (168,60)	18.0	5.4	2
   (179,68)	11.4	13.4	1
   (182,72)	7.1	15.5	1
   (188,77)	6.4	20.6	1

2. Update Centroids:

   • Cluster 1: Mean of (185,72), (179,68), (182,72), (188,77). ${\text{New Centroid}}_1=\left(\frac{185+179+182+188}{4},\frac{72+68+72+77}{4}\right)=(183.5,71.75)$
   • Cluster 2: Mean of (170,56), (168,60). ${\text{New Centroid}}_2=\left(\frac{170+168}{2},\frac{56+60}{2}\right)=(169,58)$

Iteration 2

1. Reassign Points:

   • Recalculate distances to new centroids (183.5, 71.75) and (169, 58).

   Point	Distance to (183.5,71.75)	Distance to (169,58)	Cluster
   (185,72)	1.5	18.0	1
   (170,56)	15.5	2.0	2
   (168,60)	16.0	2.2	2
   (179,68)	2.8	13.0	1
   (182,72)	1.5	15.5	1
   (188,77)	4.5	20.6	1

2. Final Clusters:

   • Cluster 1: (185,72), (179,68), (182,72), (188,77)
   • Cluster 2: (170,56), (168,60)

Visualization:

graph TD
    A["Centroid 1\n(183.5, 71.75)"] --> B["(185,72)"]
    A --> C["(179,68)"]
    A --> D["(182,72)"]
    A --> E["(188,77)"]
    F["Centroid 2\n(169, 58)"] --> G["(170,56)"]
    F --> H["(168,60)"]

11. Exam Tip: How to Score Full Marks

1. Definitions:

   • Always define terms precisely with examples.
   • Example: "A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile repository..."

2. Diagrams:

   • Draw star/snowflake schemas for DW questions.
   • Use distance formulas for clustering (K-Means) and show iterations.

3. OLAP vs. OLTP:

   • Compare purpose, users, data structure, and response time in a table.

4. ETL Steps:

   • List Extract, Transform, Load with real-world examples (e.g., Khalti’s ETL).

5. Data Mining Tasks:

   • Classify tasks into descriptive (summarization, association) and predictive (classification, regression).

6. Worked Examples:

   • For K-Means, show:
     • Distance calculations.
     • Centroid updates.
     • Final clusters.
   • For association rules, use the support-confidence-lift framework.

7. Common Pitfalls:

   • Avoid: Vague answers like "OLAP is for analysis." Instead, say "OLAP enables multidimensional analysis via slicing, dicing, and drilling on aggregated data stored in a star/snowflake schema."
   • Do: Use Nepali examples (e.g., Ncell, Daraz, Khalti) to illustrate concepts.

12. Quick Revision Table