📚 Table of Contents
Cover 1. DW Evolution & Architecture 2. Creating the Data Asset 3. Implementation & Management 4. DW & OLAP 5. Mining Primitives & Concept Description 6. Association Rule Mining 7. Classification & Prediction 8. Cluster Analysis 9. Mining Complex Data Types 📝 Semester Exam PapersData Warehousing and Data Mining
🎯 General Objectives
This course provides a comprehensive overview of data warehousing and data mining techniques. It focuses on the establishment of data warehouses, Online Analytical Processing (OLAP), and the core algorithms and system architectures for data mining.
📌 Specific Objectives
- Understand the principles of data mining and data warehousing
- Grasp the implementation techniques of data mining
- Become familiar with mining primitives, languages, and system architecture
1. Data Warehouse Evolution & Architecture
Hours: 4 Hrs
1.1 Evolution and Brief History
The concept of the Data Warehouse (DW) emerged in the 1980s to address the difficulty of performing enterprise-wide analysis using operational databases. Decision Support Systems (DSS) were limited by the performance and structure of transaction-oriented systems. Data Warehousing introduced a separate, subject-oriented, integrated, non-volatile, time-variant data collection dedicated solely to analysis and decision-making.
Modern data warehouses have evolved into Lakehouse architectures, combining the flexibility of data lakes with the performance of traditional warehouses.
1.2 Data Warehouse Design Principles
Core Design Principles:
- Subject-Oriented: Organized around major subjects (e.g., customer, product, sales) rather than applications.
- Integrated: Data from multiple heterogeneous sources is unified with consistent naming, encoding, and structures.
- Non-Volatile: Data is stable; typically, only append operations are performed, preserving historical records.
- Time-Variant: Explicitly includes a time dimension to support trend analysis and historical comparisons.
Two Main Design Approaches:
- Top-Down (Kimball): Build the enterprise data warehouse first, then create data marts.
- Bottom-Up (Inmon): Build data marts first, then integrate them into an enterprise data warehouse.
1.3 Conceptual and Logical Data Architecture
Three-Tier Architecture:
- Bottom Tier: Data sources (operational DB, flat files) → ETL layer.
- Middle Tier: Data warehouse engine (ODS, data storage, metadata repository).
- Top Tier: Front-end tools (OLAP, reporting, data mining).
Logical Data Models: The Star Schema (fact table + dimension tables) and Snowflake Schema (normalized dimensions) are the most common.
2. Creating the Data Asset
Hours: 5 Hrs
2.1 Business Data Warehouse Design
Designing a data warehouse starts with understanding business requirements, Key Performance Indicators (KPIs), and relevant dimensions. The Dimensional Modeling approach (Kimball’s four-step design process) is widely used:
- Select the business process
- Declare the grain (level of detail)
- Identify the dimensions
- Identify the facts
2.2 Populating the Data Warehouse: ETL Process
ETL (Extract, Transform, Load) is the backbone of data warehouse construction.
- Extract: Reading data from source systems (databases, APIs, flat files).
- Transform: Cleaning, deduplication, standardization, calculating derived fields.
- Load: Writing the processed data into the data warehouse (full / incremental).
2.3 End-User Access and Information Context
Once the data asset is created, end-users access it through various tools: reporting (Crystal Reports, Power BI), OLAP cubes, and ad-hoc SQL queries. Providing context—such as data lineage, business glossaries, and metadata—is critical to ensuring that users interpret the data correctly.
3. Implementing the Warehouse: Project Management & Environment
Hours: 4 Hrs
3.1 Implementation Obstacles & Planning
Common obstacles include data quality issues, unclear business requirements, scope creep, and resistance to change. A phased implementation (iterative development) with clear milestones and stakeholder buy-in is essential.
3.2 Justification & Organizational Implications
Justification is typically based on ROI (improved decision-making, reduced reporting time) and competitive advantage. The data warehouse often shifts organizational culture toward data-driven decision-making, requiring new governance roles (data steward, data owner).
4. Data Warehouses and OLAP
Hours: 6 Hrs
4.1 Multidimensional Data Model & OLAP Operations
The multidimensional model views data as a data cube with dimensions (e.g., time, product, region) and measures (e.g., sales). Core OLAP operations include:
- Roll-up (Drill-up): Aggregating data (e.g., city → country).
- Drill-down: Descending into finer detail (e.g., year → quarter).
- Slice & Dice: Selecting and projecting sub-cubes.
- Pivot (Rotate): Re-orienting the cube view.
4.2 OLAP Server: ROLAP, MOLAP, HOLAP
- ROLAP (Relational OLAP): Uses relational databases; scales well but may be slower for complex aggregations.
- MOLAP (Multidimensional OLAP): Uses proprietary multidimensional arrays; fast query performance but limited scalability.
- HOLAP (Hybrid OLAP): Combines both; stores detailed data in relational and aggregates in multidimensional cubes.
4.3 Data Warehouse Implementation & Query Processing
Efficient implementation involves data cube computation (pre-aggregation) and specialized indexing (Bitmap, Bitmap join). Query processing optimizations include materialized views and partition pruning.
5. Data Mining Primitives, Languages, and Concept Description
Hours: 5 Hrs
5.1 Primitives & System Architectures
Data mining primitives define the task-relevant data, kind of knowledge to be mined, background knowledge, and interestingness measures. Languages like DMQL (Data Mining Query Language) provide a structured way to specify mining tasks.
5.2 Concept Description: Characterization & Comparison
Characterization summarizes the general features of a target class (e.g., "describe the average customer"). Comparison contrasts two or more classes (e.g., "compare customers who churn vs those who stay"). Attribute relevance analysis (using information gain, chi-square) identifies the most influential attributes.
6. Mining Association Rules
Hours: 8 Hrs
6.1 Single-Dimensional & Multi-Level Association Rules
Association rules find interesting relationships (e.g., {Milk} → {Bread}). Apriori is the classic algorithm using support and confidence.
- Single-dimensional: Rules involving a single predicate (e.g., "items purchased").
- Multi-level: Mining at multiple concept levels (e.g., "Fruit" → "Apple").
6.2 Multidimensional & Constraint-Based Mining
Multidimensional association rules involve multiple dimensions (e.g., age, income, product). Constraint-based mining allows users to specify constraints (e.g., "only rules with lift > 1.2") to focus the search.
7. Classification and Prediction
Hours: 4 Hrs
7.1 Decision Tree Induction & Bayesian Classification
Decision Tree Induction (e.g., ID3, C4.5): Builds a tree structure by recursively splitting on attributes based on information gain or Gini index.
Bayesian Classification (Naïve Bayes): Based on Bayes’ theorem; assumes conditional independence of attributes. It is simple, fast, and performs well for many real-world problems.
7.2 Back Propagation & Other Methods
Back Propagation is the standard training algorithm for neural networks (multi-layer perceptrons). Other methods include k-Nearest Neighbors (k-NN), Support Vector Machines (SVM), and rule-based classification.
8. Cluster Analysis
Hours: 4 Hrs
8.1 Types of Data & Clustering Methods
Clustering groups objects so that objects in the same group are similar and objects in different groups are dissimilar.
- Types of data: Interval-scaled, binary, nominal, ordinal, ratio-scaled.
- Major methods: Partitioning (K-means, K-medoids), Density-based (DBSCAN), Grid-based (STING), Model-based (EM).
8.2 Outlier Analysis
Outliers are data points that deviate significantly from other observations. Detecting outliers is crucial for fraud detection, network intrusion, and data cleaning. Methods include statistical (z-score) and distance-based approaches.
9. Mining Complex Types of Data
Hours: 5 Hrs
9.1 Spatial, Multimedia, and Time-Series Data
- Spatial Databases: Mining geographic patterns (e.g., location-based clustering).
- Multimedia Databases: Extracting features from images, audio, and video.
- Time-Series & Sequence Data: Finding patterns like stock market trends, DNA sequences.
9.2 Text Mining & Web Mining
Text Mining: Extracting high-quality information from unstructured text (e.g., sentiment analysis, topic modeling).
Web Mining: Includes Web content mining, Web structure mining, and Web usage mining.
📝 Semester Exam Papers (with Model Answers)
These are past question papers from Purbanchal University. Use them for exam preparation.
PURBANCHAL UNIVERSITY — 2023
MIE212: Data Mining & Warehousing (New Course)
Answer SIX questions. Figures in margin indicate full marks.
💡 Model Answer Outline
Significance: DW supports strategic decisions, provides a single source of truth, and enables historical analysis.
Top-Down (Kimball): Enterprise DW first → Data Marts. Pros: Consistent enterprise view. Cons: Costly, longer time to value.
Bottom-Up (Inmon): Data Marts first → Integrated Enterprise DW. Pros: Faster ROI, flexible. Cons: Integration challenges later.
💡 Model Answer Outline
Schemas: Star Schema, Snowflake Schema, Fact Constellation.
OLAP Operations: Roll-up (aggregate), Drill-down (detail), Slice (filter one dimension), Dice (filter multiple), Pivot (rotate). Example: Sales cube with Time, Product, Region.
💡 Model Answer Outline
ETL Significance: It is the most labor-intensive phase; ensures data quality, consistency, and integration from heterogeneous sources.
Multi-tier Architecture: Bottom (Data Sources), Middle (DW Server/ETL), Top (Front-end Tools).
(b) Apply apriori algorithm on the transaction table given below with support threshold of 50% and confidence threshold of 60%, where 11, 12, 13, 14, and 15 are the items purchased by the customers. Show: (I) All frequent itemsets, (II) All association rules generated, (III) Final association rules sorted by confidence. 7
| Transaction ID | List of Items |
|---|---|
| T1 | 11, 12, 13 |
| T2 | 12, 13, 14 |
| T3 | 14, 15 |
| T4 | 11, 12, 14 |
| T5 | 11, 12, 13, 15 |
| T6 | 11, 12, 13, 14 |
💡 Model Answer (Apriori)
(a) Apriori Steps: 1. Generate frequent itemsets (support ≥ min_sup). 2. Generate association rules (confidence ≥ min_conf).
(b) Frequent Itemsets (min_sup=50% → 3/6): L1: {11:4}, {12:5}, {13:4}, {14:4}. L2: {11,12:4}, {11,13:3}, {11,14:3}, {12,13:4}, {12,14:4}, {13,14:3}. L3: {11,12,13:3}, {11,12,14:3}, {11,13,14:2*}, {12,13,14:3}.
Rules (Conf ≥ 60%): {11}→{12} (4/4=100%), {12}→{11} (4/5=80%), {13}→{12} (4/4=100%), {14}→{12} (4/4=100%), {12,13}→{11} (3/4=75%), etc.
Sorted by Confidence: {11}→{12} (100%), {13}→{12} (100%), {14}→{12} (100%), then 80% rules, etc.
PURBANCHAL UNIVERSITY — 2022
MIE212: Data Mining & Warehousing (New Course)
Answer SIX questions. Figures in margin indicate full marks.
💡 Model Answer Outline
Bayesian Classification (Naïve Bayes): Uses Bayes' theorem with the assumption of conditional independence. Example: Classify a customer as "Buys" or "Does not buy" based on Age and Income. Calculate P(Buys|Age, Income) ∝ P(Age|Buys)P(Income|Buys)P(Buys).
(a) World web mining
(b) Boolean association rules
(c) ETL
(d) Grid-based method
(e) Mining spatial database 4×? (10 marks total)