Data Management & Business Intelligence in e-Business: Systems, Analytics & Decision-Making

Core Concepts: Data Management in e-Business

1. Database Management Systems (DBMS)

A DBMS is software that organizes, stores, and retrieves data efficiently for business operations. In e-business, it ensures:

• Data integrity (no duplicates, accurate records)
• Security (access controls, encryption)
• Scalability (handles millions of transactions, e.g., Daraz’s product catalog).

How DBMS Works in e-Business

flowchart TD
    A["User Request\n(e.g., 'Show my order history')"] --> B["Query\n(SQL: SELECT * FROM orders WHERE user_id = 123)"]
    B --> C["DBMS Engine\n(Optimizes query execution)"]
    C --> D["Database\n(Structured tables: users, orders, payments)"]
    D --> E["Result\n(Ordered list of past purchases)"]
    E --> F["User Interface\n(eSewa dashboard, Daraz app)"]

Example: eSewa’s Database

• Tables:
  • users (user_id, name, email, phone)
  • transactions (txn_id, user_id, amount, timestamp, status)
  • service_providers (provider_id, name, service_type)
• Query: "Show all failed transactions in the last 7 days for user 45678"

  SELECT * FROM transactions
  WHERE user_id = 45678 AND status = 'failed'
  AND timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY);
  

Advantages of DBMS in e-Business:

2. Data Warehousing and ETL

A data warehouse is a centralized repository that stores historical data from multiple sources (e.g., sales, customer interactions) for analytical processing.

Key Components:

mindmap
  root((Data Warehouse))
    Sources
      OLTP Systems (e.g., Daraz’s order database)
      External Data (e.g., weather APIs for logistics)
      Flat Files (e.g., Excel exports from small businesses)
    ETL Process
      Extract: Pull data from sources
      Transform: Clean, standardize (e.g., convert "Rs." to numeric values)
      Load: Store in warehouse (star schema)
    Schema
      Fact Tables (e.g., sales_fact: order_id, product_id, quantity, date)
      Dimension Tables (e.g., product_dim: product_id, name, category)
    Tools
      SQL Server Analysis Services (SSAS)
      Talend (open-source ETL)

Example: Nabil Bank’s Loan Analytics

• Source: Loan application forms, credit scores, bank statements.
• ETL:
  1. Extract: Pull data from 50+ branches.
  2. Transform: Standardize loan amounts (convert Nepali rupees to USD for global comparisons).
  3. Load: Store in a star schema with:
     • Fact Table: loan_applications (loan_id, customer_id, amount, approval_status, date).
     • Dimension Tables: customers, branches, loan_types.
• Query: "What’s the approval rate for home loans in Kathmandu vs. Pokhara?"

  SELECT branch.region, loan_type, COUNT(*) as total_apps,
         SUM(CASE WHEN approval_status = 'Approved' THEN 1 ELSE 0 END) as approved
  FROM loan_applications
  JOIN branches ON loan_applications.branch_id = branches.branch_id
  GROUP BY branch.region, loan_type;
  

Why Data Warehouses?

• Historical Analysis: Track trends (e.g., Daraz’s peak sales days).
• Cross-Dimensional Analysis: Compare regions, products, or time periods.
• Decision Support: Predict demand (e.g., Pathao’s driver allocation).

Business Intelligence (BI) in e-Business

1. BI Tools and Techniques

BI transforms raw data into actionable insights using:

• Descriptive Analytics: "What happened?" (e.g., "Sales dropped 15% in Q2").
• Diagnostic Analytics: "Why did it happen?" (e.g., "Competitor Daraz launched a discount").
• Predictive Analytics: "What will happen?" (e.g., "Demand for umbrellas will rise 30% in June").
• Prescriptive Analytics: "What should we do?" (e.g., "Increase inventory by 20% in Region 3").

Popular BI Tools:

Example: Pathao’s Driver Demand Prediction

• Data Sources:
  • Historical ride requests (time, location, driver response time).
  • Weather data (API from OpenWeatherMap).
• Model:
  1. Train a time-series forecast (ARIMA) to predict peak hours.
  2. Use geospatial clustering (K-means) to identify hotspots.
  3. Prescriptive Action: Deploy 20% more drivers in Lalitpur during 7–9 PM.

2. Data Mining Techniques

Data mining extracts hidden patterns from large datasets. Common techniques in e-business:

Worked Example: eSewa’s Fraud Detection

1. Data: 10,000 transactions/day with features:
   • Amount, time, location, device used, user behavior (e.g., "usually pays at 8 PM").
2. Algorithm: Anomaly Detection (Isolation Forest).
3. Result: Flags transactions where:
   • Amount > 5× user’s average.
   • Time = 3 AM (unusual for this user).
   • Location = Kathmandu (but user is in Pokhara).
4. Action: Send OTP for verification.

In the Real World

1. eSewa’s Data-Driven Decisions

   • Idea Used: Predictive Analytics + Clustering
   • How: eSewa analyzes transaction patterns to:
     • Predict peak hours for server load balancing (e.g., "Expect 30% more logins at 12 PM").
     • Cluster users by behavior (e.g., "Group A pays bills weekly; Group B pays monthly").
   • Impact: Reduced system downtime by 40% and personalized reminders increased repeat usage by 15%.

2. Daraz’s Inventory Optimization

   • Idea Used: Association Rules + Demand Forecasting
   • How: Daraz’s BI system:
     • Identifies products frequently bought together (e.g., "phone + case + screen guard").
     • Forecasts demand using machine learning (e.g., "Diwali season = +200% for LED lights").
   • Impact: Reduced overstocking by 25% and increased cross-sell revenue by 12%.

3. Ncell’s Customer Churn Prediction

   • Idea Used: Classification (Logistic Regression)
   • How: Ncell trains a model on:
     • Call duration, data usage, complaint history, payment delays.
   • Output: Predicts which users are likely to switch to NTC (probability score 0–1).
   • Action: Targeted discounts for high-risk users (reduced churn by 18%).

Data Management Challenges and Solutions

1. Challenges in e-Business Data

2. Best Practices for Secure Data Management

flowchart LR
    A["Data Collection"] --> B["Encryption\n(AES-256 for databases)"]
    B --> C["Access Control\n(Role-based: admin vs. user)"]
    C --> D["Audit Logs\n(Track who accessed what)"]
    D --> E["Regular Backups\n(3-2-1 rule: 3 copies, 2 media, 1 offsite)"]
    E --> F["Disaster Recovery Plan\n(How to restore in 1 hour?)"]

Example: Nabil Bank’s Data Security

• Encryption: All customer data encrypted at rest (AES-256) and in transit (TLS 1.3).
• Access Control: Tellers can only view account balances; auditors get full logs.
• Backup: Daily incremental backups + monthly full backups stored in a secure vault.

Exam Tip

How to Score Full Marks

1. Define + Diagram:

   • Always start with a clear definition (e.g., "A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data...").
   • Follow with a Mermaid diagram (e.g., star schema or ETL process). Exam tip: Label every box and arrow!

2. Nepalese Examples:

   • Examiners love real-world ties. For every concept, mention:
     • eSewa/Khalti: Payment analytics, fraud detection.
     • Daraz: Inventory management, recommendation systems.
     • Ncell/NTC: Customer segmentation, churn prediction.
     • NEPSE: Stock trend analysis, risk assessment.

3. SQL Queries:

   • If asked about data retrieval, write a short SQL query (even if not required). Example:

     "To find top-selling products in Daraz’s electronics category:"

     SELECT product_name, SUM(quantity) as total_sold
     FROM orders
     JOIN products ON orders.product_id = products.id
     WHERE category = 'Electronics'
     GROUP BY product_name
     ORDER BY total_sold DESC
     LIMIT 10;
     

4. Advantages/Disadvantages Tables:

   • For tools like data warehouses or BI tools, present a 2-column table with 3–4 points each. Example:

     Data Warehouse Pros	Cons
     Supports complex queries (OLAP)	High initial setup cost
     Historical trend analysis	Requires skilled DBAs
     Integrates multiple data sources	Slow for real-time analytics

5. Case Study Approach:

   • For predictive analytics, structure your answer as:
     1. Problem: "How can Pathao reduce wait times?"
     2. Data Used: "Historical rides, driver locations, weather."
     3. Method: "K-means clustering + time-series forecasting."
     4. Outcome: "Reduced wait times by 22% in Thapathali."

6. Avoid Common Mistakes:

   • ❌ "Data mining is the same as data warehousing." → Fix: Data mining is a technique used after data is stored in a warehouse.
   • ❌ Vague answers like "BI is important." → Fix: "BI helps Daraz increase cross-sell revenue by 12% using association rule mining on purchase history."