Data Warehousing & Data Mining: Architecture, Techniques & Business Impact

TAKEAWAYS:

• Data warehouses aggregate structured data from multiple sources for analytical reporting, while databases focus on transactional efficiency.
• Data mining uncovers hidden patterns in large datasets using algorithms like classification, clustering, and association rules.
• Data marts are specialized subsets of data warehouses tailored for specific business units (e.g., sales, marketing).
• OLAP (Online Analytical Processing) enables multidimensional data analysis for trend forecasting and strategic planning.
• Data mining in retail (e.g., Daraz) predicts customer behavior, while banks use it for fraud detection.
• Real-world applications include NEPSE’s stock trend analysis and Pathao’s ride-demand forecasting.

1. Introduction to Data Warehousing

Data warehouses are centralized repositories designed to store integrated, historical, and non-volatile data from operational systems (e.g., ERP, CRM) for analytical purposes. Unlike databases, which optimize for transactional speed (OLTP), data warehouses optimize for query performance and reporting (OLAP).

Key Characteristics of Data Warehouses

mindmap
  root((Data Warehouse Characteristics))
    Integrated["Data from multiple sources (ERP, CRM, TPS) unified under a single schema"]
    Non-Volatile["Data is historical and immutable (no updates/deletions after loading)"]
    Time-Variant["Supports trend analysis over time (e.g., monthly sales growth)"]
    Subject-Oriented["Organized by business topics (e.g., customers, products, sales)"]
    OLAP-Optimized["Designed for complex queries and aggregations (e.g., 'Top 10 products by region')"]

Data Warehouse vs. Database

Database	Data Warehouse

Worked Example: NEPSE’s Stock Trend Analysis NEPSE (Nepal Stock Exchange) uses a data warehouse to store daily stock prices, trading volumes, and market indices. Analysts query this warehouse to:

1. Identify trends (e.g., "Which sector grew 20% in Q1 2024?").
2. Detect anomalies (e.g., sudden spikes in a low-liquidity stock).
3. Forecast future movements using time-series analysis.

Data Flow Trace:

Operational Systems (Broker APIs, Trading Terminals)
    ↓ (ETL: Extract-Transform-Load)
Data Warehouse (Star Schema: Fact[Trades] → Dim[Stocks], Dim[Dates])
    ↓ (OLAP Queries)
Dashboards (Power BI/Tableau) → "Top 5 Volatile Stocks"

2. Data Marts: Specialized Subsets

A data mart is a smaller, focused subset of a data warehouse tailored for a specific business function (e.g., marketing, finance). It improves accessibility for end-users.

Types of Data Marts

mindmap
  root((Data Mart Types))
    Dependent["Derived from a central data warehouse (e.g., Sales Data Mart from Corporate DW)"]
    Independent["Built independently (e.g., HR Data Mart from raw HR databases)"]
    Operational["Used for real-time decision-making (e.g., Pathao’s ride-demand forecasting)"]
    Strategic["Supports long-term planning (e.g., Daraz’s inventory optimization)"]

Example: Daraz’s Marketing Data Mart

Daraz uses a dependent data mart to track:

• Customer purchase history (from ERP).
• Marketing campaign performance (from CRM).
• Inventory levels (from supply chain).

Query Example: "Show me the top 3 products purchased by customers who clicked on the 'Summer Sale' banner in Kathmandu."

Fact[Purchases] ---> Dim[Customers] ---> Dim[Campaigns] ---> Dim[Products]

3. Data Mining: Extracting Knowledge from Data

Data mining applies statistical and machine learning techniques to discover patterns, correlations, and trends in large datasets. Key techniques include:

Data Mining Techniques

mindmap
  root((Data Mining Techniques))
    Classification["Predicts categories (e.g., 'Customer churn probability')"]
    Clustering["Groups similar data (e.g., 'Customer segments by RFM analysis')"]
    Association["Finds co-occurring items (e.g., 'Market basket analysis for Daraz')"]
    Regression["Predicts continuous values (e.g., 'Sales forecasting for Q2')"]
    Anomaly_Detection["Identifies outliers (e.g., 'Fraud detection in eSewa transactions')"]
    Sequence_Patterning["Discover temporal patterns (e.g., 'Customer purchase cycles')"]

Example: eSewa’s Fraud Detection

eSewa uses anomaly detection to flag suspicious transactions:

• Rule: If a user completes 10 transactions in 5 minutes from a new device, flag as potential fraud.
• Algorithm: K-Means clustering identifies unusual spending patterns.

Worked Example:

Transaction Data (Source: eSewa API)
    ↓ (Preprocessing: Clean, normalize)
Data Mining (Anomaly Detection)
    ↓ (Model: Isolation Forest)
Results: 47 transactions flagged in last 24 hours → Manual review by fraud team

[User Transactions] → [Feature Extraction] → [Anomaly Model] → [Alert Dashboard]

4. Online Analytical Processing (OLAP)

OLAP enables multidimensional analysis of data warehouses. It uses cubes (e.g., sales by region, product, time) to answer complex queries efficiently.

OLAP Operations

mindmap
  root((OLAP Operations))
    Roll-Up["Aggregates data (e.g., 'Sum sales by country')"]
    Drill-Down["Breaks down data (e.g., 'Show sales by district in Kathmandu')"]
    Slice-and-Dice["Filters data along multiple dimensions (e.g., 'Sales > $10K in Q1 2024')"]
    Pivot["Rotates data views (e.g., 'Compare sales by product vs. region')"]

Example: NTC’s Network Traffic Analysis

NTC uses OLAP to analyze:

• Dimension 1: Time (hourly/daily).
• Dimension 2: Location (Kathmandu vs. Pokhara).
• Dimension 3: Service (4G vs. 5G).

Query: "What is the average 4G download speed in Lalitpur during peak hours (6–9 PM)?"

[Measures: Data Volume] × [Dimensions: Time × Location × Service]

5. Data Warehousing Architecture

A typical data warehouse follows a 3-tier architecture:

flowchart TD
    A["Operational Systems\n(ERP, CRM, TPS)"] -->|"ETL"| B["Data Warehouse\n(Star/Snowflake Schema)"]
    B --> C["OLAP Server\n(Power BI, Tableau)"]
    C --> D["End Users\n(Executives, Analysts)"]
    E["Data Sources\n(eSewa, Daraz, NEPSE)"] -->|"ETL"| B

ETL (Extract-Transform-Load) Process

1. Extract: Pull data from sources (e.g., Daraz’s sales database).
2. Transform: Clean, standardize, and aggregate (e.g., convert currencies to NPR).
3. Load: Store in the data warehouse (e.g., star schema).

[Source Systems] → [Data Cleansing] → [Aggregation] → [Warehouse]

6. Challenges and Advantages

Advantages of Data Warehousing

• Improved Decision-Making: Historical data enables trend analysis (e.g., NEPSE’s stock trends).
• Enhanced Efficiency: Reduces redundant data storage (unlike siloed databases).
• Scalability: Handles petabytes of data (e.g., Daraz’s customer data).
• Self-Service Analytics: Business users query data without IT support.

Challenges

• High Cost: ETL and infrastructure expenses (e.g., Ncell’s network analytics).
• Data Quality Issues: Garbage in → garbage out (e.g., incorrect inventory data in Daraz).
• Complexity: Requires skilled analysts for OLAP queries.

Comparison Table: Data Warehouse vs. Data Lake

In the Real World

1. Daraz’s Recommendation Engine

   • Idea Used: Association Rule Mining (e.g., "Customers who buy laptops also buy chargers").
   • How: Data mining analyzes purchase history to suggest products, increasing average order value by 15%.
   • Worked Example:

     Support(X→Y) = 0.05 (5% of transactions include both X and Y)
     Confidence(X→Y) = 0.8 (80% of X buyers also buy Y)
     → "Laptop" → "Charger" rule triggered for 20% of laptop buyers.
     

2. Nabil Bank’s Loan Default Prediction

   • Idea Used: Classification (Decision Trees) to predict loan defaults.
   • How: Trained on historical loan data (e.g., credit score, income) to flag high-risk applicants.
   • Impact: Reduced default rates by 12% by denying risky loans pre-approval.

3. Pathao’s Ride-Demand Forecasting

   • Idea Used: Time-Series Analysis (OLAP) to predict peak demand.
   • How: Analyzes past ride data by time/location to optimize driver dispatch.
   • Example Query: "Predict 4G demand in Thapathali at 8 AM next Monday using last 6 months’ data."

Exam Tip

1. Define Key Terms Clearly

   • Always include definitions with examples (e.g., "A data mart is a subset of a data warehouse for marketing; Daraz’s marketing team uses it to track campaign ROI").
   • For data mining, explain the technique + business use case (e.g., "Clustering groups customers by spending habits to tailor promotions").

2. Compare Tables for OLTP vs. OLAP

   • Examiners love Markdown tables comparing databases vs. data warehouses or OLTP vs. OLAP. Use real examples (e.g., NEPSE vs. bank transaction systems).

3. Apply to Real Scenarios

   • For case-based questions (e.g., ABC Retail Ltd.), follow this structure:
     1. Identify the problem (e.g., "Poor production planning").
     2. Propose a solution (e.g., "Implement a data warehouse to analyze demand trends").
     3. Justify with data mining techniques (e.g., "Use regression to forecast raw material needs").

4. Diagrams Are Mandatory

   • Draw star/snowflake schemas for data warehouses.
   • Sketch ETL pipelines with arrows for data flow.
   • Use OLAP cube diagrams to show multidimensional analysis.

5. Link to Nepali Context

   • Mention NEPSE, Daraz, eSewa, or Ncell in every answer. For example:
     • "NEPSE can use data warehouses to analyze stock trends, while eSewa uses data mining to detect fraudulent transactions."

6. Time Management

   • Spend 30% of time defining terms, 40% on examples, and 30% on diagrams.
   • For scenario-based questions, allocate:
     • 2 mins to read → 5 mins to outline → 10 mins to write.

Final Visual: Data Warehouse in a Nepali Business

flowchart TD
    A["Nabil Bank\n(Operational Systems)"] -->|"ETL"| B["Data Warehouse\n(Customers, Loans, Transactions)"]
    B --> C["OLAP Server\n(Power BI: Loan Default Risk Dashboard)"]
    C --> D["Loan Officer\n'Flag high-risk applicants'"]
    E["Data Mining\n(Anomaly Detection: Fraudulent Loans)"] --> C