Learning Objectives
By the end of this chapter, you will be able to:
- Define Data Warehousing and its role in business infrastructure.
- Explain the ETL process and differentiate a data warehouse from an operational database.
- Define Data Mining and its purpose in extracting knowledge.
- Understand how the two concepts work together to provide business intelligence.
- Identify business applications, benefits, and challenges of these technologies.
Data Warehousing
A Data Warehouse is a large, centralized repository of data that is collected from multiple different sources within an organization. It is designed specifically for query and analysis rather than for transaction processing.
Key Characteristics
- Subject-Oriented: Data is categorized by subject (e.g., sales, marketing, customers) rather than by application.
- Integrated: Data from different sources (CRM, ERP, flat files) is cleaned and standardized before entering the warehouse.
- Time-Variant: Data is historical. It allows businesses to look at trends over time (e.g., sales data from the past five years).
- Non-Volatile: Once data is entered into the warehouse, it is not changed or updated, ensuring a stable historical record.
The ETL process
Data enters the warehouse through ETL:
- Extract — copy data from source systems such as the POS, ERP, CRM, and spreadsheets.
- Transform — clean and standardize it: remove duplicates, fix formats (e.g., dates written differently in two branches), and convert currencies or codes.
- Load — store the cleaned data in the warehouse, usually on a schedule such as every night.
Operational database vs data warehouse
| Aspect | Operational database (OLTP) | Data warehouse (OLAP) |
|---|---|---|
| Purpose | Run daily transactions | Analysis and decision-making |
| Data | Current, detailed | Historical, summarized, integrated |
| Updates | Constant inserts and updates | Loaded periodically; read-mostly |
| Users | Clerks, cashiers, customers | Managers and analysts |
| Example query | “What is this customer’s balance now?” | “How did deposits change by province over five years?” |
A data mart is a smaller warehouse focused on one department, such as a sales data mart for the marketing team.
Data Mining
Data Mining is the process of analyzing massive volumes of data to discover hidden patterns, correlations, and trends that are not immediately obvious.
It uses advanced mathematical algorithms, artificial intelligence, and machine learning to “mine” the data for knowledge.
Common Data Mining Techniques
- Classification: Categorizing data into predefined classes (e.g., predicting if an email is “Spam” or “Not Spam”).
- Clustering: Grouping similar data points together without predefined labels (e.g., grouping customers with similar purchasing habits for targeted marketing).
- Association: Discovering rules that define how different events are connected (e.g., Market Basket Analysis: “Customers who buy bread also buy butter”).
- Anomaly Detection: Identifying unusual data points that do not fit the normal pattern (e.g., credit card fraud detection).
- Prediction / Regression: Estimating a future numeric value from past data (e.g., forecasting next month’s sales).
Worked example: market basket analysis
A supermarket analyzes 10,000 bills. 2,000 bills contain bread, and 1,200 of those also contain butter.
- Support (bread and butter together) = 1,200 ÷ 10,000 = 12% of all bills.
- Confidence (butter given bread) = 1,200 ÷ 2,000 = 60% of bread buyers also buy butter.
The store places butter near the bread shelf and runs a combined offer, raising average bill value.
How They Work Together
Data Warehousing and Data Mining are two sides of the same coin:
- The Data Warehouse provides the structured, historical, and clean data required for analysis.
- Data Mining provides the tools and algorithms to analyze that warehouse and extract actionable business insights.
Without a robust data warehouse, data mining algorithms would struggle with messy, fragmented data. Without data mining, a data warehouse is just an expensive storage system. Together, they form the backbone of modern Business Intelligence.
flowchart LR
S1[CRM] --> ETL[Extract Transform Load]
S2[ERP] --> ETL
S3[Flat Files] --> ETL
ETL --> DW[(Data Warehouse)]
DW --> DM[Data Mining]
DM --> INS[Business Insights]
Figure 1: ETL Pipeline to Data Warehouse and Mining
Business Applications
| Sector | Application |
|---|---|
| Banking | Fraud detection, credit scoring, identifying customers likely to leave |
| Retail | Market basket analysis, stock planning, loyalty offers |
| Telecom | Predicting customers who may switch operator; designing data packs |
| Insurance | Detecting suspicious claims, pricing policies by risk |
| Healthcare | Finding disease patterns and hospital demand trends |
Benefits and Challenges
| Benefits | Challenges |
|---|---|
| Single, consistent version of business data | High cost of hardware, software, and skilled staff |
| Faster, evidence-based decisions | Poor-quality source data leads to wrong results |
| Reveals hidden patterns and opportunities | Privacy and ethical concerns about customer data |
| Long-term trend analysis | Patterns may be coincidences, not causes |
Nepal Context
Large Nepali banks and telecom companies collect millions of transactions daily. Banks use warehoused data to monitor suspicious transactions for anti–money laundering reporting, and telecom operators analyze usage data to design targeted data and voice packs. Supermarket chains can use POS data to plan stock for festivals like Dashain and Tihar.
Summary
A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile store of historical data, filled through the ETL process and designed for analysis rather than daily transactions. Data mining applies techniques such as classification, clustering, association, anomaly detection, and prediction to that data to discover hidden patterns. Together they power business intelligence in banking, retail, telecom, and other sectors, though they require investment, good data quality, and care for customer privacy.
Key Takeaways
- A data warehouse stores integrated historical data for analysis (OLAP), unlike operational databases (OLTP).
- ETL extracts, cleans, and loads data into the warehouse.
- Data mining discovers hidden patterns using techniques like classification, clustering, and association.
- Support and confidence measure the strength of association rules.
- Warehousing and mining together form the backbone of business intelligence.
Review Questions
- Define a data warehouse. Explain its four characteristics.
- What is ETL? Why is the transform step important?
- Differentiate between an operational database and a data warehouse.
- Explain any four data mining techniques with business examples.
- How can a Nepali bank use data warehousing and data mining? Discuss benefits and challenges.
