Lecture Notes: Overview of Data Mining Technology

1. Introduction to Data Mining

Data Mining is the process of extracting useful, previously unknown, and

potentially valuable patterns, relationships, and knowledge from large
volumes of data.

According to industry reports, data mining is one of the most important

technologies for decision support, business intelligence, scientific analysis,
and predictive analytics.

Definition

Data Mining is the process of discovering meaningful patterns, trends,

correlations, and relationships from large datasets using statistical,
machine learning, and database techniques.

Applications

 Retail market analysis

 Banking and finance

 Healthcare diagnostics

 Fraud detection

 Scientific research

 Cybersecurity

 Customer relationship management (CRM)

2. Data Mining vs Data Warehousing

Data Warehouse

A Data Warehouse is a centralized repository of integrated, historical,

and summarized data used for decision making.

Purpose

 Store large volumes of data

 Support reporting and analysis

 Facilitate strategic decisions

Data Mining

Data mining extracts hidden knowledge and patterns from the data stored
in databases or data warehouses.
Relationship

Data Warehouse Data Mining

Stores data Discovers knowledge

Supports querying and Supports pattern

reporting discovery

Contains historical data Finds hidden trends

Generates actionable
Provides organized data
insights

Key Point

Data mining works more efficiently when data has already been:

 Collected

 Cleaned

 Integrated

 Stored in a data warehouse

Conclusion: Data Warehouse provides the foundation, while Data Mining

extracts knowledge from it.

3. Data Mining as Part of Knowledge Discovery in Databases

(KDD)

Knowledge Discovery in Databases (KDD)

KDD is the complete process of discovering useful knowledge from data.

KDD Process

Raw Data
│
▼
Data Selection
│
▼
Data Cleansing
│
▼
Data Enrichment
│
 ▼
Data Transformation
│
▼
Data Mining
│
▼
Knowledge Presentation

Phase 1: Data Selection

Selecting relevant data from large databases.

Example

A retailer selects:

 Electronics sales records

 Customer purchase history

 Regional sales information

Phase 2: Data Cleansing

Removing inconsistencies and errors.

Activities

 Remove duplicate records

 Correct invalid ZIP codes

 Fix incorrect phone numbers

 Handle missing values

Phase 3: Data Enrichment

Adding additional information from external sources.

Example

Original Customer Data:

 Name

 Phone Number
Enriched Data:

 Age

 Income

 Credit Rating

 Occupation

Phase 4: Data Transformation

Converting data into suitable formats for mining.

Examples

Original Transformed
Data Data

Product
Item Codes
Categories

Geographic
ZIP Codes
Regions

Income
Income Ranges
Values

Phase 5: Data Mining

Applying algorithms to discover patterns and knowledge.

Examples:

 Association Rules

 Classification

 Clustering

 Sequential Patterns

Phase 6: Knowledge Presentation

Displaying discovered information.

Methods

 Reports
  Graphs

 Dashboards

 Tables

 Visualizations

4. Example of KDD in Retail Business

Consider a retail store transaction database containing:

 Customer Name

 ZIP Code

 Phone Number

 Purchase Date

 Product Code

 Price

 Quantity

 Total Amount

After KDD processing, the retailer can discover:

Association Rule

If a customer buys a Video Camera

→ They often buy Memory Cards.

Sequential Pattern

Buy Camera
↓
Buy Accessories
↓
Buy Maintenance Products

Classification

Customers may be classified as:

 Frequent Shoppers

 Discount Seekers

 Premium Buyers

 Seasonal Buyers
5. Goals of Data Mining

Data mining aims to transform data into actionable knowledge.

5.1 Prediction

Predict future events based on historical data.

Examples

 Sales forecasting

 Weather prediction

 Stock market trends

 Earthquake prediction

Retail Example

Predict:

 Future product demand

 Customer purchasing behavior

 Seasonal sales volume

5.2 Identification

Recognizing the existence of an object, event, or activity.

Examples

 Fraud detection

 Intrusion detection

 Face recognition

 Gene identification

Cybersecurity Example

Detect unauthorized users by analyzing:

 Login patterns

 CPU usage

 File access behavior

5.3 Classification

Assigning data into predefined categories.

Example

Supermarket customers can be classified as:

1. Discount Seekers

2. Loyal Customers

3. Brand-Conscious Buyers

4. Occasional Shoppers

5. Impulse Buyers

Benefits

 Targeted marketing

 Customer segmentation

 Risk analysis

5.4 Optimization

Finding the best solution under constraints.

Objectives

 Maximize profit

 Minimize cost

 Optimize inventory

 Improve resource utilization

Examples

 Supply chain optimization

 Product placement optimization

 Workforce scheduling

6. Types of Knowledge Discovered in Data Mining

Data mining discovers Inductive Knowledge, which derives new

patterns from existing data.
Types of Knowledge

1. Association Rules

Discover relationships between items.

Example

Handbag → Shoes

Meaning:
Customers buying handbags often buy shoes.

Applications

 Market Basket Analysis

 Recommendation Systems

 Cross-Selling

2. Classification Hierarchies

Create categories and classes from data.

Example

Credit Risk Classification

Excellent
│
Good
│
Average
│
Poor
│
Very Poor

Applications

 Loan approval

 Medical diagnosis

 Customer segmentation

3. Sequential Patterns

Discover event sequences occurring over time.

Example

Camera Purchase
↓
Photographic Supplies
↓
Accessories

Applications

 Customer behavior analysis

 Disease progression studies

 Web navigation analysis

4. Time-Series Patterns

Analyze trends and repeated patterns over time.

Example

Daily Stock Prices

Day 1 → Day 2 → Day 3 → Day 4

Applications

 Stock market prediction

 Weather forecasting

 Sales trend analysis

 Energy consumption prediction

5. Clustering

Grouping similar objects without predefined categories.

Example

Customer Segments:

Cluster 1 → Premium Buyers

Cluster 2 → Budget Buyers
Cluster 3 → Occasional Buyers
Cluster 4 → Loyal Customers

Applications
  Customer segmentation

 Disease categorization

 Web user profiling

 Image processing

7. Deductive vs Inductive Knowledge

Deductive Knowledge Inductive Knowledge

Learns patterns from

Uses predefined rules
data

Rule-based reasoning Data-driven discovery

Example: Mathematical
Example: Data mining
proofs

Hidden knowledge
Predictable conclusions
discovery

Data Mining Focus

✔ Inductive Knowledge Discovery

8. Knowledge Representation Methods

Discovered knowledge may be represented as:

Rules

IF Bread is purchased
THEN Butter is likely purchased

Decision Trees

Income?
│
├── High → Premium Customer
└── Low → Budget Customer

Neural Networks

Used for:

 Pattern recognition

 Prediction
  Classification

Semantic Networks

Represent relationships among concepts.

Class Hierarchies

Represent structured categories.

9. Summary

Key Concepts

 Data Mining extracts hidden patterns from large datasets.

 Data Warehouses provide organized data for mining.

 KDD consists of:

1. Data Selection

2. Data Cleansing

3. Data Enrichment

4. Data Transformation

5. Data Mining

6. Knowledge Presentation

 Major goals:

o Prediction

o Identification

o Classification

o Optimization

 Knowledge discovered includes:

o Association Rules

o Classification Hierarchies

o Sequential Patterns

o Time-Series Patterns

o Clustering
Lecture Notes: 28.2 Association Rules

28.2 Association Rules

Introduction

Association Rule Mining is one of the most important techniques in Data

Mining. It discovers relationships among items that frequently occur
together in a database.

Definition
An Association Rule identifies patterns showing how the occurrence of
one set of items influences the occurrence of another set of items.

General Form

X→Y

Where:

 X = Antecedent (Left-Hand Side, LHS)

 Y = Consequent (Right-Hand Side, RHS)

Meaning:

If X occurs, then Y is likely to occur.

Example

Milk → Juice

If a customer buys milk, they are likely to buy juice.

28.2.1 Market-Basket Model, Support and Confidence

Market-Basket Analysis

Market-basket analysis studies customer purchasing behavior by

examining items bought together in a transaction.

Sample Transactions

Transaction
Items Purchased
ID

Milk, Bread, Cookies,

101
Juice

792 Milk, Juice

1130 Milk, Eggs

1735 Bread, Cookies, Coffee

Itemset

An Itemset is a collection of one or more items.

Examples
{Milk}
{Milk, Juice}
{Bread, Cookies}

Support

Definition

Support measures how frequently an itemset appears in the database.

Formula
Transactions containing X ∪ Y
Support( X →Y )=
Total Transactions
Example

Itemset:

{Milk, Juice}

Appears in:

 Transaction 101

 Transaction 792

Total transactions = 4
2
Support= =0.5=50 %
4

Interpretation

50% of all transactions contain both Milk and Juice.

Confidence

Definition

Confidence measures the strength of an association rule.

Formula

Support ( X ∪ Y )
Confidence( X → Y )=
Support (X )

Example

Rule:

Milk → Juice
Milk appears in:

 101

 792

 1130

Total = 3 transactions

Milk and Juice together appear in:

 101

 792

Total = 2 transactions
2
Confidence= =66.7 %
3
Interpretation

66.7% of customers buying Milk also buy Juice.

Difference Between Support and Confidence

Support Confidence

Measures frequency Measures strength

Based on all Based on transactions

transactions containing LHS

Indicates popularity Indicates reliability

Frequent (Large) Itemsets

An itemset is called Frequent Itemset if:

Support ≥ Minimum Support Threshold

Example

Minimum Support = 50%

Frequent Itemsets:

{Milk}
{Bread}
{Juice}
{Cookies}
{Milk, Juice}
{Bread, Cookies}

Properties Used in Association Rule Mining

1. Downward Closure Property

Every subset of a frequent itemset must also be frequent.

Example:

If

{Milk, Bread, Juice}

is frequent,

then

{Milk, Bread}
{Milk, Juice}
{Bread, Juice}

must also be frequent.

2. Antimonotonicity Property

Every superset of an infrequent itemset is also infrequent.

Example:

If

{Eggs}

is infrequent,

then

{Milk, Eggs}
{Bread, Eggs}
{Milk, Bread, Eggs}

are also infrequent.

28.2.2 Apriori Algorithm

Purpose
To discover all frequent itemsets efficiently.

Key Idea

Use:

 Downward Closure

 Antimonotonicity

to reduce the search space.

Apriori Steps

Step 1

Generate Candidate 1-itemsets

C1

Example:

{Milk}
{Bread}
{Juice}
{Cookies}
{Eggs}
{Coffee}

Step 2

Compute Supports

Suppo
Item
rt

Milk 0.75

Bread 0.50

Juice 0.50

Cookie
0.50
s

Eggs 0.25

Coffee 0.25
Step 3

Generate Frequent 1-itemsets (L1)

L1 =
{Milk}
{Bread}
{Juice}
{Cookies}

Step 4

Generate Candidate 2-itemsets (C2)

{Milk,Bread}
{Milk,Juice}
{Bread,Juice}
{Milk,Cookies}
{Bread,Cookies}
{Juice,Cookies}

Step 5

Compute Supports

Suppo
Itemset
rt

Milk,Bread 0.25

Milk,Juice 0.50

Bread,Juice 0.25

Milk,Cookies 0.25

Bread,Cooki
0.50
es

Juice,Cookie
0.25
s

Step 6

Generate Frequent 2-itemsets

L2=
{Milk,Juice}
{Bread,Cookies}

Step 7

Generate Candidate 3-itemsets

No candidate satisfies downward closure.

Hence algorithm stops.

Advantages

 Simple

 Easy implementation

 Effective for small datasets

Disadvantages

 Multiple database scans

 Large candidate generation

 High computational cost

28.2.3 Sampling Algorithm

Purpose

Reduce computation by analyzing a sample of the database.

Working

Database
↓
Take Sample
↓
Find Frequent Itemsets
↓
Check Negative Border
↓
Validate in Full Database
Negative Border

The set of minimal itemsets that:

 Are not frequent

 But all their subsets are frequent

Importance

Helps detect missing frequent itemsets.

Example

Items:

{A,B,C,D,E}

Frequent Sets:

{A}
{B}
{C}
{D}
{AB}
{AC}
{BC}
{AD}
{CD}
{ABC}

Negative Border:

{E}
{BD}
{ACD}

Advantages

 Faster

 Less memory requirement

Disadvantages

 May miss some frequent itemsets

 Additional verification required

28.2.4 FP-Tree and FP-Growth Algorithm

Motivation

Apriori generates a huge number of candidate itemsets.

Example

1000 frequent items generate:

(1000
2 )
=499,500

candidate 2-itemsets.

FP-Tree (Frequent Pattern Tree)

A compressed representation of transaction data.

Features

 No candidate generation

 Compact storage

 Faster mining

FP-Tree Construction

Step 1

Scan database

Find frequent 1-itemsets.

Example:

Milk = 3
Bread = 2
Cookies = 2
Juice = 2

Step 2

Sort items by descending support.

Milk > Bread > Cookies > Juice

Step 3
Insert transactions into tree.

Example

Transaction:

Milk Bread Cookies Juice

Stored as:

Root
└─Milk
└─Bread
└─Cookies
└─Juice

FP-Growth Algorithm

Steps

1. Build FP-tree.

2. Create conditional pattern base.

3. Construct conditional FP-tree.

4. Mine recursively.

Output

Frequent Patterns:

{Milk}
{Bread}
{Cookies}
{Juice}
{Milk,Juice}
{Bread,Cookies}

Advantages

 No candidate generation

 Fewer database scans

 High performance

28.2.5 Partition Algorithm

Idea

Divide the database into smaller partitions.

Working

Database
↓
Partition 1
Partition 2
Partition 3
↓
Find Local Frequent Itemsets
↓
Merge Results
↓
Find Global Frequent Itemsets

Two Passes

Pass 1

Find local frequent itemsets.

Pass 2

Verify global support.

Benefits
  Suitable for very large databases

 Supports parallel processing

 Less memory consumption

28.2.6 Other Types of Association Rules

1. Association Rules among Hierarchies

Items may belong to hierarchies.

Example

Beverages
├─ Bottled Water
└─ Juices

Desserts
├─ Ice Cream
└─ Frozen Yogurt

Rule Example

Healthy Frozen Yogurt

→
Bottled Water

These cross-hierarchy associations are often more valuable.

2. Multidimensional Association Rules

Use multiple attributes.

Single-Dimensional Rule

Items_Bought(Milk)
→
Items_Bought(Juice)

Multidimensional Rule

Time(6:30–8:00)
→
Items_Bought(Milk)

Attribute Types

Type Example

Categorica Product
l Type

Quantitati Salary,
ve Time
Quantitative Data Handling

Convert values into ranges.

Example:

Salary Category

0–29999 Low Income

30000– Middle
74999 Income

>75000 High Income

3. Negative Association Rules

Describe items that rarely occur together.

Example

Potato Chips
→
NOT Bottled Water

Challenges

 Huge number of possible negative rules

 Many are meaningless

 Requires domain knowledge

Example

Positive Rule:

Days Chips
→
Topsy Soft Drink

Interesting Negative Rule:

Days Chips
→
NOT Joke Soft Drink
28.2.7 Additional Considerations for Association Rules

Challenges in Real Databases

1. Huge Number of Items

Retail stores may contain:

10,000+ products

2. Massive Transaction Volumes

Millions of transactions generated daily.

3. Geographic and Seasonal Variations

Examples:

 Summer purchases

 Winter purchases

 Regional preferences

4. Multiple Classification Levels

Products belong to:

 Categories

 Subcategories

 Brands

5. Data Quality Issues

Common problems:

 Missing data
  Incorrect entries

 Conflicting information

 Redundant records

Comparison of Association Rule Algorithms

Sampli FP- Partitio

Feature Apriori
ng Growth n

Candidate
Yes Yes No Yes
Generation

Database Scans Many Few Two Two

Moderat
Speed Fast Very Fast Fast
e

Memory Usage High Low Moderate Low

Large Database Excellen

Limited Good Excellent
Support t

Quick Revision Summary

Association Rule: X → Y

Measures:

 Support = Frequency

 Confidence = Strength

Major Algorithms:

 Apriori

 Sampling

 FP-Growth

 Partition

Special Rules:

 Hierarchical Rules

 Multidimensional Rules
  Negative Rules

Key Properties:

 Downward Closure

 Antimonotonicity

Applications:

 Market Basket Analysis

 Recommendation Systems

 Customer Behavior Analysis

 Retail Analytics

 Fraud Detection

 Business Intelligence

Lecture Notes: 28.3 Classification and Decision Tree Induction

28.3 Classification

Introduction

Classification is a fundamental Data Mining technique used to predict the

class label of data objects based on previously classified examples.

Definition

Classification is the process of learning a model that describes different

predefined classes of data and using that model to classify new records.

Since the classes are known in advance, classification is also called

Supervised Learning.
Example

In a banking application, customers applying for a credit card can be

classified as:

 Good Risk

 Fair Risk

 Poor Risk

based on attributes such as:

 Salary

 Marital Status

 Account Balance

 Age

Classification Process

Classification consists of two phases:

Phase 1: Model Construction (Training)

A model is built using a set of pre-classified records called the training

dataset.

Training Data Contains

 Input attributes

 Known class labels

Example:

Custom Salar Ag Loanwort

er y e hy

C1 50K 30 Yes

C2 20K 22 No

Phase 2: Model Usage (Testing)

The generated model is applied to new unseen records to predict their

class.
Training Data
↓
Learning Algorithm
↓
Classification Model
↓
New Data
↓
Predicted Class

Objectives of Classification

A good classification model should have:

1. High Accuracy

Ability to correctly predict class labels.

2. Low Computational Cost

Training and prediction should be efficient.

3. Scalability

Should handle large datasets effectively.

Decision Tree Classification

Definition

A Decision Tree is a tree-structured model that represents classification

rules graphically.

Components

Componen
Description
t

Root Node Starting node

Internal Decision based on

Node attribute

Branch Outcome of decision

Leaf Node Final class label

Example Decision Tree

Credit Card Risk Classification

Married?
/ \
Yes No
| |
Salary Acct_Balance
/ | \ / \
<20K 20-50K ≥50K <5K ≥5K
| | | | |
Poor Fair Good Poor Age
|
<25 ≥25
| |
Fair Good

Example Rule Extraction

Rule 1

IF Married = Yes
AND Salary ≥ 50K
THEN Good Risk

Rule 2

IF Married = No
AND Account Balance < 5K
THEN Poor Risk

Rule 3

IF Married = No
AND Account Balance ≥ 5K
AND Age ≥ 25
THEN Good Risk
Decision Tree Induction Algorithm

Information Gain and Entropy

Why Information Gain?

Decision trees choose the attribute that best separates the data.

This is measured using Information Gain.

Entropy

Definition

Entropy measures the impurity or uncertainty of a dataset.

Formula
n
I (S 1 , S2 , … , S n)=−∑ pi log ⁡2 p i
i=1

Where:

 pi= probability of class i

Interpretation

Entro
Meaning
py

0 Pure dataset

Maximum
1
uncertainty

0–1 Partial impurity

Expected Entropy After Splitting

For an attribute A:
m
∣Sj∣
E( A)=∑ × I (S 1 j , S 2 j ,... , S nj )
j=1 ∣S ∣

Where:

 S j= partition produced by attribute A

Information Gain Formula

Gain( A)=I (S)−E ( A)

Where:
  I (S )= Original Entropy

 E( A) = Expected Entropy after split

Rule

Choose the attribute with the highest gain.

Sample Training Dataset

RI Marrie Acct Ag Loanwort

Salary
D d Balance e hy

≥2
1 No ≥50K <5K Yes
5

≥2
2 Yes ≥50K ≥5K Yes
5

20K– <2
3 Yes <5K No
50K 5

<2
4 No <20K ≥5K No
5

≥2
5 No <20K <5K No
5

20K– ≥2
6 Yes ≥5K Yes
50K 5

Step 1: Calculate Initial Entropy

There are:

 Yes = 3

 No = 3

Thus:

3 3
p(Yes)= =0.5 p(No)= =0.5
6 6

Entropy:

I (3 , 3)=−0.5 log ⁡2 (0.5)−0.5 log ⁡2 (0.5) I (3 , 3)=1

Step 2: Compute Information Gain

Attribute: Married

For Married = Yes:

 Yes = 2

 No = 1

Entropy:

I (2 ,1)=0.92

For Married = No:

 Yes = 1

 No = 2

Entropy:

I (1 ,2)=0.92

Expected Entropy:
3 3
E(Married )= (0.92)+ (0.92) E(Married )=0.92
6 6
Gain:

Gain(Married )=1−0.92=0.08

Attribute: Salary

E(Salary)=0.33Gain(Salary)=1−0.33Gain(Salary)=0.67

Attribute: Account Balance

E( AccountBalance)=0.92Gain( AccountBalance)=0.08

Attribute: Age

E( Age)=0.54Gain( Age)=0.46

Attribute Selection
 Gai
Attribute
n

Married 0.08

Salary 0.67

Account
0.08
Balance

Age 0.46

Highest Gain

Salary = 0.67

Therefore:

Root Node = Salary

Building the Decision Tree

Salary < 20K

Records:

RID 4
RID 5

Both belong to:

Loanworthy = No

Create leaf node:

Class = No

Salary ≥ 50K

Records:

RID 1
RID 2

Both belong to:

Loanworthy = Yes

Create leaf node:

Class = Yes
Salary = 20K–50K

Records:

RID 3
RID 6

Need additional splitting.

Remaining Attributes:

 Married

 Age

 Account Balance

Information Gain for Remaining Records

Gai
Attribute
n

Married 0

Age 1

Account
1
Balance

Both Age and Account Balance provide perfect separation.

Choose:

Age

Final Decision Tree

 Salary
/ | \
<20K 20K-50K ≥50K
| | |
Class=No Age Class=Yes
/ \
<25 ≥25
| |
Class=No Class=Yes

Final Classification Rules

Rule 1

IF Salary < 20K

THEN Loanworthy = No

Rule 2

IF Salary ≥ 50K
THEN Loanworthy = Yes

Rule 3

IF Salary = 20K–50K
AND Age < 25
THEN Loanworthy = No

Rule 4

IF Salary = 20K–50K
AND Age ≥ 25
THEN Loanworthy = Yes

Advantages of Decision Trees

1. Easy to understand and interpret.

2. Generates human-readable rules.

3. Requires little data preparation.

4. Handles both categorical and numerical data.

 5. Fast classification of new records.

Limitations of Decision Trees

1. Can overfit training data.

2. Sensitive to small data changes.

3. Large trees become complex.

4. May produce biased results with unbalanced data.

Applications of Classification

Banking

 Credit card approval

 Loan risk analysis

Healthcare

 Disease diagnosis

 Patient risk prediction

Marketing

 Customer response prediction

 Customer segmentation

Cybersecurity

 Spam filtering

 Intrusion detection

Insurance

 Claim approval

 Risk assessment

Lecture Notes: 28.4 Clustering

28.4 Clustering

Introduction
Clustering is a data mining technique used to group similar data objects
together without prior knowledge of class labels.

Unlike classification, clustering does not require pre-classified training

data.

Therefore, clustering is called:

Unsupervised Learning

because the system learns patterns directly from the data.

Definition of Clustering

Clustering is the process of partitioning a dataset into groups (clusters)

such that:

 Objects within the same cluster are highly similar.

 Objects in different clusters are highly dissimilar.

Goal

Maximize Similarity Within Clusters

Minimize Similarity Between Clusters

Examples of Clustering

Business Applications

 Customer segmentation

 Market analysis

 Product recommendation

Healthcare Applications

 Disease pattern analysis

 Patient grouping based on symptoms

 Drug response analysis

Social Media

 User behavior analysis

 Community detection

Education
  Student performance grouping

Classification vs Clustering

Feature Classification Clustering

Learning Type Supervised Unsupervised

Training Data
Yes No
Required

Class Labels Known Yes No

Predict predefined Discover hidden

Objective
classes groups

Customer
Example Loan approval
segmentation

Similarity Measure in Clustering

The effectiveness of clustering depends on how similarity between records

is measured.

For numerical data, similarity is usually measured using distance

functions.

Euclidean Distance

The most commonly used distance measure is Euclidean Distance.

Formula

For two records:

r j=(r j 1 , r j 2 ,... , r jn )r k =(r k1 , r k 2 ,... , r kn )

Distance:

Distance(r j , r k )=√ ¿ ¿ ¿

Interpretation

Smaller Distance

More Similar
Larger Distance

Less Similar

K-Means Clustering Algorithm

Introduction

The most popular clustering algorithm is K-Means.

Idea

Partition the dataset into K clusters, where each cluster is represented by

its centroid (mean).

K-Means Flowchart

Start
↓
Select K Centroids
↓
Assign Records to Nearest Cluster
↓
Compute New Centroids
↓
Any Changes?
↓
Yes → Repeat
No
↓
Stop
Sample Dataset

Two-Dimensional Records

RI Ag Years of
D e Service

1 30 5

2 52 20

3 51 15

4 5 52

5 10 3

6 52 55

(RID is only an identifier and is not used for clustering.)

Initial Cluster Selection

Assume:

K=2

Initial centroids chosen randomly:

Cluster C1

RID 3

( 51 ,15 )

Cluster C2

RID 6

( 52 ,55 )

First Iteration

Compute distance of every record from both centroids.

Assignment Results
Recor Assigned
d Cluster

RID 1 C1

RID 2 C2

RID 3 C1

RID 4 C1

RID 5 C1

RID 6 C2

Recalculate Centroids

Cluster C1

Records:

RID 1
RID 3
RID 4
RID 5

New Mean:

( 33.75 , 8.75 )

Cluster C2

Records:

RID 2
RID 6

New Mean:

( 52.5 , 25 )

Second Iteration

Reassign records based on updated centroids.

New Cluster Membership

Cluster C1

RID 1
RID 4
RID 5

Cluster C2

RID 2
RID 3
RID 6

Updated Centroids

Cluster C1

( 28.3 , 6.7 )

Cluster C2

( 51.7 , 21.7 )

Third Iteration

All records remain in their current clusters.

No changes occur.

Therefore:

Algorithm Terminates

Final Clusters

Cluster C1
-----------
RID 1
RID 4
RID 5

Cluster C2
-----------
RID 2
RID 3
RID 6

Cluster Mean Formula

For a cluster containing n records:

Mean= ( ∑nr , ∑nr , ... ∑nr )

1 2 m

Where:

 m = number of dimensions

 n = number of records

Squared Error Criterion

K-Means attempts to minimize the total clustering error.

Formula
k
Error=∑ ∑ Distance ¿ ¿ ¿
i=1 r j ∈C i

Where:

 C i= Cluster i

 mi= Centroid of Cluster i

Objective Function

Minimize Total Squared Distance

Between Data Points and Centroids

Advantages of K-Means

1. Simple

Easy to understand and implement.

2. Fast

Efficient for large datasets.

3. Scalable
Works well with large databases.

4. Converges Quickly

Usually requires few iterations.

Limitations of K-Means

1. Need to Specify K

Number of clusters must be known beforehand.

2. Sensitive to Initial Centroids

Different starting points may yield different results.

3. Local Optimum

May not find global optimum.

4. Poor for Non-Spherical Clusters

Works best when clusters are compact and spherical.

BIRCH Clustering Algorithm

Full Form

Balanced Iterative Reducing and Clustering using Hierarchies

Why BIRCH?

Traditional clustering algorithms assume:

Entire Dataset Fits Into Memory

For huge datasets, this is impractical.

BIRCH was designed to handle:

 Very large databases

 Limited memory environments

Main Characteristics of BIRCH

Hybrid Technique

Combines:
 1. Hierarchical Clustering

2. Partition-Based Clustering

Input Parameters

1. Available Memory

Determines how much cluster information can be stored.

2. Radius Threshold

Controls cluster size.

Radius Threshold Effect

Large Radius

Few Clusters
Many Records per Cluster

Small Radius

Many Clusters
Few Records per Cluster

Working of BIRCH

Step 1

Read records sequentially.

Step 2

Insert records into a clustering tree (CF Tree).

Step 3

Assign records to closest leaf cluster.

Step 4

Update:
  Cluster center

 Radius

Step 5

Split nodes if radius exceeds threshold.

Step 6

If memory becomes full:

Increase radius threshold.

Step 7

Merge clusters if necessary.

BIRCH Architecture

Input Records
↓
CF Tree Construction
↓
Leaf Clusters
↓
Cluster Refinement
↓
Final Clusters

Advantages of BIRCH

Memory Efficient

Works with limited RAM.

Incremental

Processes data sequentially.

Scalable

Suitable for very large databases.

Fast
Linear complexity.

Computational Complexity

BIRCH complexity:

O(n)

where
n=Number of Records

This makes it highly efficient for big data applications.

K-Means vs BIRCH

K-
Feature BIRCH
Means

Memory
High Low
Requirement

Moderat Very
Scalability
e High

Very
Dataset Size Medium
Large

Centroid
Structure Used CF Tree
s

Complexity Iterative Linear

Applications of Clustering

Banking

 Customer segmentation

 Fraud detection

Healthcare

 Patient grouping

 Disease classification

Marketing
  Target advertising

 Customer profiling

E-Commerce

 Product recommendation

Social Networks

 Community detection

Telecommunications

 Usage pattern analysis

Quick Revision Summary

Clustering

 Unsupervised learning technique.

 Groups similar records together.

Similarity Measure
Euclidean Distance

K-Means Steps

1. Select K centroids

2. Assign records

3. Recompute centroids

4. Repeat until convergence

Objective
Minimize Squared Error

BIRCH

 Hierarchical clustering method.

 Uses CF Tree.

 Suitable for very large databases.

 Linear computational complexity.

Key Difference
Classification → Known Classes
Clustering → Unknown Classes

Lecture Notes: 28.5 Approaches to Other Data Mining Problems

28.5 Approaches to Other Data Mining Problems

Introduction

Apart from Association Rules, Classification, and Clustering, Data Mining

includes several other techniques for discovering hidden knowledge from
data.

Major approaches include:

1. Discovery of Sequential Patterns

2. Discovery of Patterns in Time Series

3. Regression Analysis

4. Neural Networks

5. Genetic Algorithms

28.5.1 Discovery of Sequential Patterns

Definition

Sequential Pattern Mining identifies frequently occurring sequences of

events or transactions over time.

It discovers relationships where one event is likely to be followed by

another event.

Sequence of Itemsets

A sequence consists of multiple transactions arranged according to time.

Example

Customer purchases:

Visit 1 → {Milk, Bread, Juice}

Visit 2 → {Bread, Eggs}
Visit 3 → {Cookies, Milk, Coffee}

Sequence:

{Milk, Bread, Juice}

↓
{Bread, Eggs}
↓
{Cookies, Milk, Coffee}

Subsequence

A subsequence is a smaller sequence appearing within a larger sequence.

Examples:

{Milk, Bread, Juice} → {Bread, Eggs}

and

{Bread, Eggs} → {Cookies, Milk, Coffee}

Support of a Sequence

Support measures how frequently a sequence appears in the database.

Formula
Number of sequences containing S
Support( S)= ×100
Total number of sequences

Goal of Sequential Pattern Mining

Find all sequences whose support exceeds a minimum support threshold.

Applications

Retail

Customer buying behavior analysis.

Example:

Laptop
↓
Mouse
↓
Printer

Healthcare

Patient treatment sequences.

Web Usage Mining

Analysis of webpage navigation patterns.

Banking

Credit card transaction analysis.

Sequential Pattern Mining Process

Sequence Database
↓
Find Frequent Subsequences
↓
Apply Minimum Support
↓
Generate Sequential Patterns

Advantages

 Predicts future customer behavior.

 Supports recommendation systems.

 Useful in trend analysis.

Limitations

 Computationally expensive.
  Large sequence databases require significant processing.

28.5.2 Discovery of Patterns in Time Series

Definition

A time series is a sequence of observations collected at regular intervals

over time.

Examples

Stock Prices

Monday → 500
Tuesday → 510
Wednesday → 520

Daily Sales

Day 1 → 100 Units

Day 2 → 120 Units
Day 3 → 110 Units

Weather Data

 Temperature

 Rainfall

 Humidity

Objectives of Time Series Mining

Trend Detection

Identify upward or downward movement.

Pattern Discovery

Detect repeated behaviors.

Forecasting

Predict future values.

Similarity Analysis
Compare multiple time series.

Typical Patterns

Increasing Trend

10 → 20 → 30 → 40

Decreasing Trend

40 → 30 → 20 → 10

Seasonal Pattern

High sales every December

Stable Pattern

100 → 101 → 99 → 100

Applications

Stock Market Analysis

Predict stock movement.

Weather Forecasting

Predict climate changes.

Sales Forecasting

Estimate future demand.

Healthcare

Monitor patient health parameters.

Advantages

 Reveals temporal relationships.

 Enables forecasting.
Limitations

 Sensitive to noise.

 Complex analysis for long sequences.

28.5.3 Regression

Definition

Regression is a predictive data mining technique used to estimate

numerical values.

Unlike classification, which predicts categories, regression predicts

continuous values.

Example

Predicting:

 House prices

 Stock prices

 Patient survival probability

 Sales revenue

Regression Rule Example

Consider:

LAB_TESTS
(Patient ID,
Test1,
Test2,
...
Testn)

Target variable:

P = Probability of Survival

Regression rule:

(Test1 Range)
AND
(Test2 Range)
AND ...
(Testn Range)
→P=x

or

x<P≤y

Regression Function

General form:

Y =f ( X 1 , X 2 , X 3 , … , X n )

Where:

 Y = Target variable

 X₁, X₂, …, Xₙ = Predictor variables

Linear Regression

When the relationship is linear:

Y =a+bX

or
Y =a+b 1 X 1 +b 2 X 2+⋯+ bn X n

Working of Regression

Historical Data
↓
Build Regression Function
↓
Estimate Target Variable
↓
Prediction

Applications

Medical Diagnosis

Predict survival probability.

Finance

Predict stock prices.

Marketing

Predict product demand.

Education

Predict student performance.

Advantages

 Easy interpretation.

 Effective prediction tool.

Limitations

 Assumes relationships among variables.

 Sensitive to outliers.

28.5.4 Neural Networks

Definition

A Neural Network is an Artificial Intelligence technique inspired by the

human brain.

It learns patterns from data and develops predictive models.

Basic Idea

Neural networks use:

Input Data
↓
Learning Process
↓
Hidden Representation
↓
Output Prediction
Characteristics

Learning Capability

Learns from examples.

Self-Adaptive

Improves performance automatically.

Generalization

Can predict unseen data.

Types of Neural Networks

1. Supervised Neural Networks

Learning occurs using known outputs.

Example:

Input → Desired Output

2. Unsupervised Neural Networks

Learning occurs without predefined outputs.

Example:

Input → Discover Hidden Patterns

Neural Network Structure

Input Layer
↓
Hidden Layer(s)
↓
Output Layer

Applications

Image Recognition

Face detection.

Speech Recognition
Voice assistants.

Medical Diagnosis

Disease prediction.

Fraud Detection

Banking applications.

Data Mining

Classification and prediction.

Advantages

 Learns complex relationships.

 Handles noisy data.

 High predictive accuracy.

Limitations

Black Box Nature

Difficult to interpret results.

Non-Unique Internal Representation

Different models may learn differently.

High Computation Cost

Requires significant processing.

Poor Time-Series Handling

May struggle with temporal dependencies.

28.5.5 Genetic Algorithms (GAs)

Definition

Genetic Algorithms are optimization and search techniques inspired by

biological evolution.

Developed by:

John Holland
Basic Concept

Genetic Algorithms mimic:

 Natural Selection

 Mutation

 Crossover

 Survival of the Fittest

Biological Inspiration

DNA consists of:

A
C
T
G

Similarly, candidate solutions are encoded as strings.

Components of Genetic Algorithms

1. Population

Set of candidate solutions.

2. Chromosome

Representation of a solution.

3. Fitness Function

Measures solution quality.

4. Selection

Choose best individuals.

5. Crossover
Combine parent solutions.

6. Mutation

Random modification.

Working of Genetic Algorithm

Initial Population
↓
Evaluate Fitness
↓
Selection
↓
Crossover
↓
Mutation
↓
New Generation
↓
Repeat Until Optimum Found

Characteristics of Genetic Algorithms

Population-Based Search

Works with multiple solutions simultaneously.

Parallel Search

Explores many solution paths.

Randomized Search

Uses probabilistic operations.

Adaptive Learning

Improves solutions generation by generation.

Applications

Data Mining

Pattern discovery.
Clustering

Optimal cluster formation.

Scheduling

Job scheduling problems.

Engineering Design

Optimization tasks.

Image Analysis

Pattern recognition.

Advantages

Global Search Capability

Avoids many local optima.

Flexible

Applicable to many problems.

Parallel Processing

Multiple solutions explored simultaneously.

Limitations

Computationally Expensive

Requires large processing power.

Random Nature

Results may vary.

Large Number of Solutions

Produces many candidate solutions.

Comparison of Approaches

Technique Purpose Output

Sequential Pattern Discover event

Frequent sequences
Mining sequences
Technique Purpose Output

Time Series Mining Analyze temporal data Trends & forecasts

Regression Predict numeric values Continuous outputs

Learn complex
Neural Networks Predictive model
patterns

Near-optimal
Genetic Algorithms Optimization & search
solutions

Applications of Advanced Data Mining Techniques

Business

 Customer behavior analysis

 Market forecasting

Healthcare

 Disease prediction

 Survival analysis

Finance

 Stock prediction

 Fraud detection

Telecommunications

 Usage analysis

Manufacturing

 Process optimization

Quick Revision Summary

Sequential Pattern Mining

 Finds frequently occurring sequences.

 Uses support measure.

Time Series Mining

 Analyzes data collected over time.

  Supports forecasting and trend analysis.

Regression

Y =f ( X 1 , X 2 , … , X n )

 Predicts continuous values.

Neural Networks

 AI-based learning technique.

 Excellent for classification and prediction.

Genetic Algorithms

 Inspired by biological evolution.

 Uses selection, crossover, and mutation.

 Effective for optimization and clustering problems.

Key Learning

Sequential Patterns → Event Sequences

Time Series → Temporal Trends
Regression → Numerical Prediction
Neural Networks → Intelligent Learning
Genetic Algorithms → Optimization Search

Lecture Notes: 28.6 Applications of Data Mining

1. Introduction to Applications of Data Mining

Definition
Data Mining is the process of extracting useful patterns, trends,
correlations, and knowledge from large volumes of data to support
decision-making and strategic planning.

Importance

Organizations generate massive amounts of data daily. Data mining helps

transform this raw data into actionable information for:

 Business growth

 Improved decision-making

 Risk management

 Resource optimization

 Customer satisfaction

2. Major Application Areas of Data Mining

A. Marketing Applications

Objective

To understand customer behavior and improve marketing effectiveness.

Key Applications

1. Consumer Behavior Analysis

 Studies customer purchasing habits.

 Identifies products frequently purchased together.

 Helps understand customer preferences.

Example:
Customers buying smartphones often purchase:

 Earphones

 Phone cases

 Screen protectors

Benefits

 Improved product placement

 Better inventory management

 Increased sales
2. Targeted Marketing and Advertising

Data mining helps:

 Identify potential customers

 Design personalized advertisements

 Send targeted promotional offers

Example:
An online retailer recommends products based on previous purchases.

Benefits

 Higher response rates

 Reduced advertising costs

 Increased customer engagement

3. Customer Segmentation

Customers are grouped based on:

 Age

 Income

 Purchase behavior

 Location

 Interests

Types of Customer Segments

Segment Characteristics

Premium Customers High spending

Regular Customers Frequent buyers

Occasional Seasonal
Customers purchases

New Customers Recently joined

Benefits

 Customized services
  Personalized marketing campaigns

 Better customer retention

4. Store Layout and Catalog Design

Data mining identifies:

 Frequently purchased item combinations

 Effective shelf arrangements

 Product placement strategies

Example

Placing:

 Bread near butter

 Chips near soft drinks

Benefits

 Increased impulse buying

 Improved shopping experience

B. Finance Applications

Objective

To reduce risk, improve profitability, and detect fraudulent activities.

1. Credit Risk Analysis

Financial institutions analyze customer information such as:

 Income

 Employment history

 Credit history

 Loan repayment records

Classification Example
Customer
Risk Level
Type

Good Risk Loan Approved

Additional
Fair Risk
Verification

Poor Risk Loan Rejected

Benefits

 Reduced loan defaults

 Improved credit decisions

2. Analysis of Accounts Receivable

Organizations analyze:

 Outstanding payments

 Payment behavior patterns

Benefits

 Better cash flow management

 Faster debt recovery

3. Investment Performance Analysis

Data mining helps analyze:

 Stocks

 Bonds

 Mutual funds

 Investment portfolios

Applications

 Trend analysis

 Risk prediction

 Portfolio optimization

Benefits
  Better investment decisions

 Increased returns

4. Fraud Detection

Types of Financial Fraud

Banking Fraud

 Fake loans

 Identity theft

Credit Card Fraud

 Unauthorized transactions

Insurance Fraud

 False claims

Data Mining Techniques Used

 Classification

 Clustering

 Anomaly Detection

Benefits

 Reduced financial losses

 Improved security

C. Manufacturing Applications

Objective

To improve efficiency and optimize resources.

1. Resource Optimization

Resources include:

 Machines

 Workers

 Raw materials
  Energy

Applications

 Production planning

 Workforce allocation

 Inventory control

Benefits

 Lower operating costs

 Higher productivity

2. Manufacturing Process Optimization

Data mining identifies:

 Process bottlenecks

 Production inefficiencies

 Causes of product defects

Benefits

 Improved product quality

 Reduced waste

 Faster production

3. Shop-Floor Layout Design

Analyzes:

 Workflow patterns

 Material movement

Benefits

 Reduced transportation cost

 Improved operational efficiency

4. Product Design

Customer requirements are analyzed to improve products.

Example: Automobile Industry

Data collected from:

 Customer feedback

 Sales records

 Service reports

Used to improve:

 Safety

 Fuel efficiency

 Comfort

 Features

Benefits

 Increased customer satisfaction

 Better product acceptance

D. Healthcare Applications

Objective

To improve patient care, medical research, and hospital management.

1. Medical Image Analysis

Data mining helps analyze:

 X-rays

 CT scans

 MRI images

 Ultrasound images

Applications

 Tumor detection

 Disease diagnosis

 Pattern recognition

Benefits
  Early disease detection

 Improved diagnostic accuracy

2. Gene and Microarray Data Analysis

Microarray (Gene-Chip) Technology

Measures expression levels of thousands of genes simultaneously.

Data Mining Applications

 Gene clustering

 Disease prediction

 Drug development

Benefits

 Personalized medicine

 Better understanding of genetic diseases

3. Drug Effectiveness and Side-Effect Analysis

Analyzes:

 Patient records

 Treatment outcomes

 Medication responses

Benefits

 Improved treatment plans

 Reduced adverse effects

 Safer medications

4. Hospital Process Optimization

Applications include:

 Patient scheduling

 Bed allocation

 Resource utilization
  Emergency response management

Benefits

 Reduced waiting times

 Improved service quality

 Better resource management

5. Patient Wellness Analysis

Studies relationships among:

 Patient health indicators

 Treatment quality

 Doctor qualifications

Applications

 Predicting patient recovery

 Evaluating healthcare quality

Benefits

 Enhanced patient care

 Evidence-based medical decisions

Summary Table: Applications of Data Mining

Domain Major Applications

Customer segmentation, targeted advertising, consumer

Marketing
behavior analysis, store layout design

Credit scoring, investment analysis, fraud detection, risk

Finance
management

Manufacturi Resource optimization, process improvement, product

ng design, quality control

Medical diagnosis, gene analysis, drug effectiveness,

Healthcare
hospital management

Advantages of Data Mining Applications

 1. Improved decision-making.

2. Discovery of hidden patterns.

3. Increased operational efficiency.

4. Better customer relationship management.

5. Fraud and risk reduction.

6. Cost savings.

7. Enhanced productivity.

8. Competitive advantage.

Lecture Notes: 28.7 Commercial Data Mining Tools

Learning Objectives

After studying this topic, students should be able to:

 Understand commercial data mining tools and their features.

 Identify common techniques used in commercial data mining

software.

 Explain the role of ODBC in data mining.

 Describe user interfaces and APIs used by data mining tools.

 Discuss popular commercial data mining products.

 Understand future trends in data mining technologies.

28.7 Commercial Data Mining Tools

Introduction

Commercial data mining tools are software applications designed to

extract useful patterns, knowledge, and insights from large databases.

These tools combine various techniques from:

 Artificial Intelligence (AI)

 Machine Learning

 Statistics

 Optimization

 Database Technology

The primary objective is to assist organizations in making better business

decisions.

1. Techniques Used in Commercial Data Mining Tools

Most commercial tools use one or more of the following techniques:

1. Association Rule Mining

Discovers relationships among items in large databases.

Example

Bread ⇒ Butter

Applications:

 Market basket analysis

 Product recommendation systems

2. Clustering

Groups similar records together without predefined classes.

Example

Grouping customers based on:

 Purchasing behavior

 Income level

 Geographic location

Applications:
  Customer segmentation

 Medical diagnosis

3. Neural Networks

AI-based learning systems inspired by the human brain.

Applications:

 Pattern recognition

 Fraud detection

 Stock prediction

 Medical diagnosis

4. Sequential Pattern Mining

Discovers patterns occurring over time.

Example

Customer purchases:

Laptop → Mouse → Printer

Applications:

 Customer behavior prediction

 Web clickstream analysis

5. Statistical Analysis

Uses statistical methods such as:

 Correlation analysis

 Regression analysis

 Hypothesis testing

Applications:

 Business forecasting

 Risk analysis
6. Decision Trees

Graphical models used for classification and prediction.

Applications:

 Credit approval

 Disease diagnosis

 Customer classification

7. Advanced Techniques

Many modern tools additionally support:

Genetic Algorithms

 Optimization problems

 Feature selection

Case-Based Reasoning

 Solves new problems using past experiences

Bayesian Networks

 Probabilistic reasoning

 Risk prediction

Nonlinear Regression

 Complex trend analysis

Pattern Matching

 Image recognition

 Text mining

Fuzzy Logic

 Handles uncertain or imprecise data

2. ODBC (Open Database Connectivity)

Definition

ODBC is an industry-standard interface that allows data mining tools to

access data from different database systems.
Supported Databases

 Microsoft Access

 Oracle

 SQL Server

 Informix

 dBASE

Advantages of ODBC

 Database independence

 Easy connectivity

 Standardized access method

 Improved interoperability

Architecture

Data Mining Tool

|
ODBC
|
----------------------
| Databases |
----------------------
| Oracle |
| SQL Server |
| Access |
| Informix |
----------------------

3. Operating Environment

Most commercial data mining tools operate in:

Windows Environment

 Most popular platform

 User-friendly GUI support

UNIX Environment
  Used in enterprise-level applications

 Better scalability

Client-Server Architecture

Many tools follow:

Client
|
Server Database
|
Data Mining Engine

Advantages:

 Centralized processing

 Better performance

 Scalability

28.7.1 User Interface

Graphical User Interface (GUI)

Most commercial tools provide GUI-based environments.

Features

 Drag-and-drop operations

 Visualization tools

 Interactive analysis

 Graphical reports

Visualization Support

Visualization helps users understand:

 Trends

 Clusters

 Rules

 Patterns

Examples
  Charts

 Graphs

 Heat maps

 Decision trees

Benefits

 Easy interpretation

 Improved decision-making

 User-friendly operation

Text-Based Interfaces

Less common today.

Mostly found in UNIX-based systems.

Example

IBM Intelligent Miner

Advantages:

 Low resource usage

 Script automation

Disadvantages:

 Difficult for beginners

28.7.2 Application Programming Interface (API)

Definition

API allows developers to integrate data mining functionality into custom

applications.

Common API Types

C Libraries

Provide reusable mining functions.

Dynamic Link Libraries (DLLs)

Allow software applications to access data mining services.

Proprietary Languages

Some tools provide specialized commands for mining operations.

Benefits

 Software customization

 Application integration

 Automation of mining tasks

4. Representative Commercial Data Mining Tools

Company Product Technique Used

Decision Trees, Case-Based

AcknoSoft Kate
Reasoning

Angoss Knowledge SEEKER Decision Trees, Statistics

Neural Networks, Machine

Business Objects Business Miner
Learning

Statistical Analysis,
CrossZ Data Distilleries
Optimization

DBMiner
DBMiner Comprehensive Data Mining
Technology

Classification, Association
IBM Intelligent Miner
Rules

Megaputer Symbolic Knowledge

PolyAnalyst
Intelligence Acquisition

Management Discovery
NCR Association Rules
Tool (MDT)

Decision Trees, Association

Purple Insight MineSet
Rules

Neural Networks, Regression,

SAS Enterprise Miner
Clustering
Comparison of Popular Tools

Tool Major Strength

IBM Intelligent
Enterprise analytics
Miner

SAS Enterprise Advanced predictive

Miner modeling

MineSet Visualization

PolyAnalyst Knowledge discovery

Comprehensive mining
DBMiner
features

28.7.3 Future Directions in Data Mining

Continuous Evolution

Data mining tools continuously improve through advances in:

 Artificial Intelligence

 Machine Learning

 Statistics

 Optimization Techniques

Integration with Modern Database Systems

Future tools will support:

 Distributed databases

 Parallel databases

 Data warehouses

 Cloud platforms

Internet-Based Data Mining

Emerging Trends
  Web mining

 Social media analytics

 Real-time data analysis

Applications:

 Online recommendation systems

 E-commerce analytics

 User behavior analysis

Hybrid Approaches

Future systems combine multiple techniques:

Data Mining
|
---------------------------
| | | |
AI ML Statistics OLAP
---------------------------

Benefits:

 Improved accuracy

 Better scalability

 Faster processing

Parallel and Distributed Computing

Future mining systems utilize:

Parallel Computing

Multiple processors execute tasks simultaneously.

Distributed Computing

Data processing distributed across multiple systems.

Advantages:

 Faster computation

 Handling large datasets

 Improved scalability
Big Data and Data Mining

Big Data Characteristics

Volume

Terabytes and petabytes of data.

Velocity

Continuous generation of data.

Variety

Structured and unstructured data.

Hadoop-Based Mining

Hadoop

Open-source framework for big data processing.

Mahout

Machine learning library running on Hadoop.

Applications:

 Recommendation systems

 Classification

 Clustering

Cloud-Based Data Mining

Future systems increasingly use cloud environments.

Benefits

 Scalability

 Reduced infrastructure costs

 On-demand resources

Cloud Mining Architecture

Data Sources
|
Cloud Storage
|
Data Warehouse
|
OLAP Server
|
Data Mining Engine
|
Knowledge Discovery

Multimedia Data Mining

Future mining systems must handle:

Images

 Medical imaging

 Satellite imagery

Videos

 Surveillance systems

 Video analytics

Audio

 Speech recognition

 Voice analytics

Text Documents

 Text mining

 Sentiment analysis

Challenges for Future Data Mining Tools

1. Handling petabyte-scale data.

2. Processing multimedia data.

3. Real-time analytics.

4. Data privacy and security.

 5. Integration across heterogeneous databases.

6. Faster image and video retrieval.

7. Cloud-based scalability.

Advantages of Commercial Data Mining Tools

 Automated knowledge discovery.

 Support for large databases.

 User-friendly interfaces.

 Integration with enterprise databases.

 Advanced visualization.

 Predictive analytics capabilities.

 Better business decision-making.

Summary

Commercial data mining tools combine techniques such as association

rules, clustering, neural networks, decision trees, regression, and
statistical analysis to discover useful knowledge from large databases.
Modern tools use ODBC for database connectivity, provide GUI-based
interfaces, support APIs for customization, and increasingly leverage big
data technologies, cloud computing, distributed processing, and AI-driven
analytics. Future data mining systems will focus on processing massive
datasets, multimedia information, and real-time cloud-based analytics
efficiently.
Lecture Notes: Data Warehousing – Introduction, Definitions, and
Terminology

Module: Data Warehousing Fundamentals

1. Introduction to Data Warehousing

A database is a collection of related data, and a database system

consists of the database along with the software used to manage it.

A Data Warehouse (DW) is also a collection of data, but it differs

significantly from traditional databases in terms of:

 Purpose

 Structure

 Functioning

 Performance

 Data organization

The primary objective of a data warehouse is to support decision-

making, business analysis, and strategic planning rather than day-
to-day transaction processing.

2. Traditional Databases vs. Data Warehouses

Traditional Database Data Warehouse

Feature
(OLTP) (OLAP)

Decision Support &

Purpose Transaction Processing
Analysis
 Traditional Database Data Warehouse
Feature
(OLTP) (OLAP)

Operations Insert, Update, Delete Query and Analysis

Historical and Integrated

Data Type Current Operational Data
Data

Users Clerks, Operators Managers, Analysts

Optimizatio Fast Transaction

Fast Query Processing
n Processing

Data
Single System Multiple Systems
Sources

Data
Moderate Very Large
Volume

Example

Bank Transaction System (OLTP)

 Depositing money

 Withdrawing cash

 Updating account balances

Bank Data Warehouse (OLAP)

 Analyzing customer spending habits

 Predicting loan defaults

 Identifying profitable customer segments

3. Definition of Data Warehouse

According to W. H. Inmon:

"A Data Warehouse is a subject-oriented, integrated, nonvolatile, and

time-variant collection of data in support of management's decision-
making process."

This is the most widely accepted definition of a data warehouse.

4. Characteristics of a Data Warehouse

A. Subject-Oriented
Data is organized around major business subjects rather than applications.

Examples:

 Customer

 Sales

 Product

 Supplier

 Finance

Instead of storing data according to operational functions, the warehouse

organizes data according to business subjects.

B. Integrated

Data is collected from multiple heterogeneous sources and integrated into

a consistent format.

Sources may include:

 Relational databases

 Legacy systems

 Flat files

 ERP systems

 Web applications

Example

Customer data from:

 CRM System

 Sales Database

 Online Store

is integrated into a single warehouse format.

C. Nonvolatile

Once data enters the warehouse, it is generally not modified or deleted.

Operations performed are mainly:

 Data Loading
  Data Retrieval

 Data Analysis

Unlike OLTP systems, updates occur infrequently.

Benefits

 Consistency

 Historical preservation

 Reliable analysis

D. Time-Variant

Historical data is maintained over long periods.

Example

Sales data may be stored for:

 5 years

 10 years

 20 years

This allows trend analysis and forecasting.

Example query:

Compare annual sales from 2020–2025.

5. Purpose of Data Warehousing

A data warehouse supports:

Decision Making

Managers can:

 Analyze business performance

 Forecast trends

 Develop strategies

Business Intelligence

Organizations gain insights from large volumes of historical data.

Knowledge Discovery
Hidden patterns and relationships can be discovered.

6. Types of Queries Supported

Ad Hoc Queries

User-defined queries created when needed.

Example

Find the top-selling products in Karnataka during the last quarter.

Canned Queries

Predefined queries frequently executed with changing parameters.

Example

Monthly sales report for a selected region.

Advantages:

 Faster execution

 Standardized reporting

 Easy for management use

7. Applications Supported by Data Warehouses

7.1 OLAP (Online Analytical Processing)

OLAP refers to analytical processing of large volumes of warehouse data.

It enables users to:

 Analyze trends

 Compare performance

 Generate multidimensional reports

Features

 Slice and Dice

 Drill Down

 Roll Up

 Pivoting
Example

Analyzing:

Sales → Region → State → City

at different levels of detail.

7.2 DSS (Decision Support Systems)

Decision Support Systems assist managers and executives in making

strategic decisions.

Also known as:

 Executive Information Systems (EIS)

 Management Information Systems (MIS)

Functions

 Trend analysis

 Performance monitoring

 Strategic planning

 Forecasting

Example

A retail company deciding:

 Where to open a new store

 Which products should be promoted

7.3 Data Mining

Data Mining is the process of discovering hidden knowledge from large

datasets.

Objectives

 Pattern discovery

 Trend analysis

 Prediction

 Classification

 Clustering
Example

A supermarket discovers:

Customers who buy bread often purchase milk and butter.

This information helps in:

 Product placement

 Marketing campaigns

 Sales promotions

8. OLTP vs OLAP

OLTP (Online Transaction Processing)

Supports daily operational activities.

Characteristics

 Frequent updates

 Small transactions

 High concurrency

 Real-time processing

Examples

 ATM transactions

 Railway reservations

 E-commerce purchases

OLAP (Online Analytical Processing)

Supports business intelligence and analysis.

Characteristics

 Complex queries

 Large data volumes

 Historical information

 Read-intensive operations

Examples
  Sales forecasting

 Customer behavior analysis

 Market trend analysis

9. Why Traditional Databases Are Not Suitable for OLAP

Traditional databases are optimized for:

 Small transactions

 Fast updates

 Limited record retrieval

However, analytical applications require:

 Massive data scans

 Complex joins

 Historical analysis

 Multidimensional reporting

These requirements reduce OLTP performance.

Therefore, separate data warehouses are developed.

10. Sources of Data Warehouse Data

A data warehouse gathers data from multiple sources:

Internal Sources

 ERP Systems

 CRM Systems

 Operational Databases

 Accounting Systems

External Sources

 Market Research Data

 Government Data

 Partner Data

 Web Data
File-Based Sources

 CSV files

 Excel spreadsheets

 Text files

11. Data Warehouse Architecture (Basic View)

Operational Databases
│
▼
Data Extraction
│
▼
Data Transformation
│
▼
Data Cleaning
│
▼
Data Warehouse
│
┌──────┼──────┐
▼ ▼ ▼
OLAP DSS Data Mining

Key Terms

Term Meaning

Data Repository of integrated

Warehouse historical data

OLTP Online Transaction Processing

OLAP Online Analytical Processing

DSS Decision Support System

EIS Executive Information System

MIS Management Information System

Data Mining Knowledge discovery from data

Term Meaning

Ad Hoc Query User-defined query

Canned Query Predefined frequently used query

Subject- Organized around business

Oriented subjects

Integrated Combined from multiple sources

Data is stable and rarely

Nonvolatile
modified

Time-Variant Stores historical data

Summary

A Data Warehouse is a centralized repository designed for analytical

processing and decision support. Unlike traditional databases that focus
on transaction processing (OLTP), data warehouses support OLAP, DSS,
and Data Mining applications. According to W. H. Inmon, a data
warehouse is subject-oriented, integrated, nonvolatile, and time-
variant, enabling organizations to perform complex analysis, discover
knowledge, and make strategic business decisions using historical and
integrated data.
Lecture Notes: 29.2 Characteristics of Data Warehouses

Module: Data Warehousing and OLAP

1. Introduction

A Data Warehouse (DW) is a centralized repository of integrated,

historical, and subject-oriented data designed to support decision-making,
business intelligence, and analytical processing.

Unlike transactional databases, which focus on daily operations, data

warehouses are optimized for:

 Strategic decision-making

 Trend analysis

 Forecasting

 Data mining

 Business intelligence

To effectively support these activities, data warehouses employ the

Multidimensional Data Model.

2. Data Warehouse vs. Transactional Database

Transactional Database Data Warehouse

Feature
(OLTP) (OLAP)

Purpose Operational Processing Analytical Processing

Data Current Data Historical Data

Updates Frequent Periodic

 Transactional Database Data Warehouse
Feature
(OLTP) (OLAP)

Queries Simple Complex

Users Clerks, Operators Managers, Analysts

Data
Relational Multidimensional
Model

Response Fast Transactions Fast Analysis

3. Multidimensional Data Model

A data warehouse typically stores data using a Multidimensional Data

Model.

Characteristics

 Represents data as dimensions and measures.

 Supports fast analytical queries.

 Enables slicing, dicing, drill-down, and roll-up operations.

Example

A sales warehouse may contain:

Fact:

 Sales Amount

Dimensions:

 Time

 Product

 Location

 Customer

Time
│
│
Product ───── Sales ───── Location
│
│
Customer
This multidimensional structure is ideal for OLAP operations.

4. Integrated Data Repository

Unlike multidatabases that provide access to independent databases, a

data warehouse stores:

 Integrated data

 Consistent data

 Cleansed data

 Consolidated data

Data Sources

 Relational Databases

 Legacy Systems

 ERP Systems

 CRM Systems

 Flat Files

 Web Data

Benefits

✔ Single version of truth

✔ Improved consistency

✔ Better decision-making

5. Historical Data Support

Data warehouses store historical information for long periods.

Supports

 Trend Analysis

 Time Series Analysis

 Predictive Analysis

 Forecasting

Example
An organization may analyze:

Yea
Sales
r

202 ₹50
2 Crore

202 ₹62
3 Crore

202 ₹75
4 Crore

202 ₹89
5 Crore

This enables management to identify growth patterns and future

opportunities.

6. Nonvolatile Nature of Data Warehouses

One of the most important characteristics of a data warehouse is that it is

nonvolatile.

Meaning

Data once loaded is rarely changed.

Operations mainly include:

 Read

 Append

 Purge

Not Commonly Allowed

 Frequent Updates

 Frequent Deletions

 Transaction Processing

Read / Append / Purge Model

Data Warehouse

Read Data
 ↓
Append New Data
↓
Purge Old Data (Optional)

Advantages

 Stable environment

 Consistent reporting

 Accurate historical analysis

7. Periodic Refreshing of Data

Unlike OLTP databases that update continuously, data warehouses are

refreshed periodically.

Common Refresh Policies

Full Refresh

Entire warehouse is reloaded.

Incremental Refresh

Only newly changed records are added.

Benefits of Incremental Refresh

 Faster

 Less storage overhead

 Reduced processing time

8. ETL Process in Data Warehousing

Warehouse data is loaded through the ETL Process.

ETL stands for:

E – Extract

Collect data from multiple sources.

T – Transform

Convert data into a consistent format.

L – Load
Store transformed data into the warehouse.

ETL Architecture

Source Systems
(DBs, Files, ERP, CRM)
│
▼
Extract
│
▼
Transform
(Clean, Filter, Integrate)
│
▼
Load
│
▼
Data Warehouse

9. Decision-Support Technologies

A data warehouse supports several decision-support technologies.

A. OLAP

Online Analytical Processing

Functions:

 Slice

 Dice
  Drill Down

 Roll Up

 Pivot

Example:

Analyze sales by:

 Year

 State

 Product

B. DSS

Decision Support Systems

Used by:

 Executives

 Managers

 Analysts

Applications:

 Strategic planning

 Resource allocation

 Forecasting

C. Data Mining

Knowledge discovery from data.

Applications:

 Classification

 Clustering

 Association Rules

 Prediction

Example:

Customers purchasing laptops often buy printers.

10. Feedback Loop in Data Warehousing

Data mining and DSS systems may generate:

 Rules

 Patterns

 Metadata

 Predictions

These outputs can be stored back into the warehouse.

Data Warehouse
│
▼
OLAP / DSS / Data Mining
│
▼
Knowledge / Rules
│
▼
Stored Back into Warehouse

11. Important Characteristics of Data Warehouses and OLAP

Systems

1. Multidimensional Conceptual View

Data represented through dimensions and facts.

Example:

Sales by Product, Region, and Time.

2. Unlimited Dimensions and Aggregation Levels

Supports many dimensions simultaneously.

Example:

Country → State → City → Store

3. Unrestricted Cross-Dimensional Operations

Compare data across multiple dimensions.

Example:

Sales of Product A in Karnataka during 2025.

4. Dynamic Sparse Matrix Handling

Efficiently handles empty data combinations.

Example:

Some products may not be sold in certain regions.

5. Client-Server Architecture

Supports distributed access.

Client Applications
│
▼
OLAP Server
│
▼
Data Warehouse

6. Multiuser Support

Multiple analysts can access the warehouse simultaneously.

Benefits:

 Collaboration

 Concurrent analysis

7. Accessibility

Easy access to enterprise-wide data.

Benefits:

 Improved reporting

 Better visibility
8. Transparency

Users need not know:

 Data source locations

 Storage details

 Data integration methods

9. Intuitive Data Manipulation

Simple analytical operations:

 Slice

 Dice

 Drill-down

 Roll-up

10. Inductive and Deductive Analysis

Inductive Analysis

Discovers unknown patterns.

Example:

Finding customer buying behavior.

Deductive Analysis

Tests known hypotheses.

Example:

Verifying whether discounts increase sales.

11. Flexible Distributed Reporting

Supports report generation across multiple locations.

Example:

Head office generating reports from regional warehouses.

12. Types of Data Warehouses

Because warehouses often contain terabytes or petabytes of data, several
architectures exist.

A. Enterprise Data Warehouse (EDW)

Organization-wide warehouse.

Characteristics

 Centralized

 Large scale

 High investment

Advantages

 Enterprise-wide view

 Better consistency

B. Virtual Data Warehouse

Provides materialized views of operational databases.

Benefits

 Reduced storage

 Faster implementation

C. Logical Data Warehouse

Uses:

 Data Federation

 Virtualization

 Distributed Access

Benefits

 No physical consolidation required

 Flexible access

D. Data Mart
Small departmental warehouse.

Examples

 Sales Data Mart

 Finance Data Mart

 HR Data Mart

Benefits

 Lower cost

 Faster deployment

Data Warehouse Hierarchy

Enterprise Data Warehouse

│
┌─────────┼─────────┐
│ │ │
Sales Finance HR
Mart Mart Mart

13. Operational Data Store (ODS)

An Operational Data Store (ODS) is an intermediate database used

before data enters the warehouse.

Functions

 Temporary storage

 Data integration

 Data cleaning

Characteristics

 Near real-time

 Operational reporting

ODS Flow

Operational Systems
│
▼
 ODS
│
Cleansing
│
▼
Data Warehouse

14. Analytical Data Store (ADS)

An Analytical Data Store (ADS) is designed specifically for analytical

processing.

Created Through

 Data Cleansing

 Aggregation

 Transformation

Applications

 OLAP

 Reporting

 Forecasting

 Data Mining

ODS vs ADS

Feature ODS ADS

Operational Analytical
Purpose
Reporting Reporting

Data Current Historical

Updates Frequent Periodic

Analysts &
Users Operational Staff
Managers

Complexit
Low High
y
Summary

A Data Warehouse is a nonvolatile, integrated, historical repository

designed for analytical processing and decision support. It employs a
multidimensional data model, supports OLAP, DSS, and Data
Mining, and relies on the ETL process for data integration. Key
characteristics include multidimensional views, multiuser support,
transparency, accessibility, and flexible reporting. Large-scale
implementations may take the form of Enterprise Data Warehouses,
Virtual Warehouses, Logical Warehouses, or Data Marts, while ODS
and ADS serve as important intermediate and analytical storage systems
in the data warehousing architecture.
Lecture Notes: 29.3 Data Modeling for Data Warehouses

Module: Data Warehouse Modeling and Multidimensional Data

Analysis

1. Introduction to Data Modeling in Data Warehouses

A Data Warehouse stores data in a form that supports:

 Business Intelligence (BI)

 Online Analytical Processing (OLAP)

 Data Mining

 Decision Support Systems (DSS)

Unlike transactional databases that use a relational model, data

warehouses commonly use a Multidimensional Data Model.

Objective

The multidimensional model organizes data into structures called:

 Data Cubes

 Hypercubes (more than three dimensions)

This model enables faster analytical processing and easier business

reporting.

2. Multidimensional Data Model

The multidimensional model organizes data into multiple dimensions and

measures.

Dimensions

Dimensions represent perspectives for analysis.

Examples:

 Time
  Product

 Region

 Customer

 Salesperson

Measures

Measures are numerical values to be analyzed.

Examples:

 Sales Revenue

 Profit

 Quantity Sold

 Cost

Example Dimensions

A corporate warehouse may contain:

Time (Fiscal Quarters)

Product
Region

The measure may be:

Sales Revenue

3. Data Cube

A Data Cube is a multidimensional representation of data.

Two-Dimensional Example

Sales by Product and Region.

Produ Nort Sout Eas Wes

ct h h t t

Laptop 100 120 150 130

Mobile 90 140 160 110

This resembles a spreadsheet.

Three-Dimensional Cube

Adding Time creates a third dimension.

Time
▲
│
│
Region ◄───────────┼──────────► Product

Each cell contains:

Sales(Product, Region, Time)

Example:

Laptop + South Region + Q1

= ₹50,000
4. Hypercube

When dimensions exceed three, the structure becomes a Hypercube.

Example:

Sales
├── Product
├── Region
├── Time
├── Customer
├── Promotion
└── Channel

Although difficult to visualize, DBMS tools can efficiently process such

structures.

5. Advantages of Data Cubes

Faster Queries

Data can be accessed directly without complex joins.

Multiple Perspectives
Users can analyze data from different dimensions.

Simplified Reporting

Business reports become easier to generate.

Better Decision-Making

Supports multidimensional analysis.

6. Pivoting (Rotation)

Definition

Pivoting is the process of rotating a data cube to view data from different
perspectives.

Example 1

Original View

Produ Regio
Sales
ct n

₹50,00
Laptop South
0

Pivoted View

Regio Quart
Sales
n er

₹50,00
South Q1
0

The same data is viewed through a different orientation.

Benefits

 Flexible analysis

 Interactive reporting

 Easy visualization
7. Slice Operation

Definition

A Slice extracts a two-dimensional view from a multidimensional cube.

Example

3-D Cube:

Product × Region × Time

Selecting:

Time = Q1

produces:

Produ Nort Sout Eas

ct h h t

Laptop 100 120 150

Mobile 80 95 110

This is called a Slice.

8. Dice Operation

Definition

Dicing extracts a smaller sub-cube from the original cube.

Example

Selecting:

Products = Laptop, Mobile

Regions = South, East
Time = Q1, Q2

creates a smaller cube.

Slice and Dice

The phrase "Slice and Dice" refers to breaking large datasets into
smaller views for detailed analysis.

9. Roll-Up Operation

Definition

Roll-Up summarizes data by moving upward in a hierarchy.

Example: Time Hierarchy

Day
↓
Week
↓
Month
↓
Quarter
↓
Year

Sales Example
Mont Sale
h s

Jan 50

Feb 60

Mar 70

Roll-Up:

Quart Sale
er s

Q1 180

Benefits

 Summary reporting

 Executive dashboards

 Trend analysis

10. Drill-Down Operation

Definition

Drill-Down moves from summarized data to detailed data.

Example

Country
↓
Region
↓
State
↓
City

Example Analysis

India
↓
Karnataka
↓
Bengaluru
↓
Whitefield

Users can progressively explore finer details.

Roll-Up vs Drill-Down

Roll-Up Drill-Down

Detailed → Summary →
Summary Detailed

Higher Lower
Aggregation Aggregation
Roll-Up Drill-Down

Less Detail More Detail

11. Dimensional Model

The Dimensional Model consists of:

1. Fact Tables

2. Dimension Tables

12. Fact Table

Definition

A fact table contains measurable business data.

Examples:

 Sales

 Revenue

 Profit

 Quantity

Characteristics

 Large table

 Contains numerical values

 Connected to dimension tables

Example Fact Table

Product_I Region_I Time_I Sale

D D D s

1000
P1 R1 T1
0

1500
P2 R2 T1
0
13. Dimension Table

Definition

Dimension tables describe business entities.

Examples

Product Dimension

Product_I Product_Na
Category
D me

Electronic
P1 Laptop
s

Region Dimension

Region_I Region_Na
D me

R1 South

Time Dimension

Time_I Quart Yea

D er r

202
T1 Q1
5
14. Relationship Between Fact and Dimension Tables

Product
│
│
Region ─── Fact Table ─── Time
│
│
Customer

Fact table stores measurements.

Dimension tables provide context.

15. Star Schema

Definition

A Star Schema consists of:

 One central Fact Table

 Multiple Dimension Tables

Structure

Product
│
│
Customer ─ Fact ─ Time
 │
│
Region

Advantages

 Simple design

 Faster queries

 Easy implementation

Example

Fact Table:

Sales_Fact

Dimensions:

Product_Dim
Time_Dim
Region_Dim
Customer_Dim

16. Snowflake Schema

Definition

A Snowflake Schema is a normalized version of the Star Schema.

Dimension tables are split into sub-dimensions.

Example

Product
│
Category
│
Department

Structure
 Category
│
Product ─────────┤
│
Fact
│
Time
│
Region

Advantages

 Reduced redundancy

 Better data integrity

Disadvantages

 More joins

 Slower queries

17. Star Schema vs Snowflake Schema

 Star Snowflake
Feature
Schema Schema

Denormaliz
Structure Normalized
ed

Complexity Simple Complex

Query
Faster Slower
Speed

Storage More Less

Maintenanc
Easy Difficult
e

18. Fact Constellation Schema

Definition

A Fact Constellation contains multiple fact tables sharing dimension

tables.

Example

Product
│
┌───────────┼───────────┐
│ │
Sales Fact Forecast Fact

Shared Dimension:

Product

Advantages
  Supports complex warehouses

 Enables multiple business processes

19. Bitmap Indexing

Definition

Bitmap indexing represents data using bit vectors.

Best suited for:

 Low-cardinality attributes

 Data warehouse environments

Example

Attribute:

Car Size

Values:

Economy
Compact
Mid-size
Full-size

Bitmap representation:

Ca Econom
r y

C1 1

C2 0

C3 1

Benefits

 Faster searching

 Reduced I/O

 Better aggregation performance

20. Join Indexing

Definition

Join indexes maintain relationships between:

 Primary Keys

 Foreign Keys

Example

Fact Table:

Sales

Dimension:

City

Join index stores references connecting:

City → Fact Rows

Benefits

 Faster joins

 Efficient star schema queries

21. Summary Tables

Data warehouses often store precomputed summaries.

Examples

Quart Revenu
er e

Q1 ₹10 Cr

Q2 ₹15 Cr

Instead of calculating repeatedly, summaries are stored.

Advantages
  Faster reporting

 Reduced query time

 Better OLAP performance

22. Master Data Management (MDM)

Definition

Master Data Management (MDM) defines standards, policies, and

governance for critical organizational data.

Examples of Master Data

 Customer

 Product

 Supplier

 Region

These are usually represented through dimension tables.

Objectives of MDM

Data Consistency

Same meaning across systems.

Data Quality

Clean and accurate information.

Governance

Standard rules and policies.

Integration

Unified enterprise-wide view.

23. Importance of Dimension Cleansing

Dimension tables often originate from multiple systems.

Before loading:
  Remove duplicates

 Standardize names

 Resolve conflicts

 Harmonize formats

Example:

Bangalore
Bengaluru
B'luru

All transformed into:

Bengaluru

Summary

Data warehouse modeling relies on the Multidimensional Data Model,

where data is organized into data cubes and analyzed using OLAP
operations such as pivoting, slicing, dicing, roll-up, and drill-down.
The dimensional model consists of Fact Tables and Dimension Tables,
implemented through schemas such as Star Schema, Snowflake
Schema, and Fact Constellation. To improve performance, warehouses
use Bitmap Indexing, Join Indexing, and Summary Tables. Finally,
Master Data Management (MDM) ensures consistency, quality, and
governance of critical business dimensions across the enterprise.
Lecture Notes: 29.4 Building a Data Warehouse

Module: Data Warehouse Construction and Architecture

1. Introduction

Building a Data Warehouse (DW) is a complex process that involves

collecting, integrating, cleaning, transforming, storing, and maintaining
data from multiple sources for decision-support applications.

Objectives of a Data Warehouse

 Support business intelligence

 Enable OLAP analysis

 Facilitate data mining

 Support executive decision-making

 Allow ad hoc querying

Since future analytical requirements cannot be completely predicted,

warehouses must be designed to support flexible and scalable querying.

2. Principles of Data Warehouse Design

A warehouse should be designed based on:

Anticipated Usage
  Who will use the warehouse?

 What type of analysis will be performed?

 What reports are required?

Example

Organization Data Organization

Consumer Products
Product, Sales, Customer Analysis
Company

Donor, Campaign, Fundraising

Nonprofit Organization
Analysis

Customer, Loan, Transaction

Bank
Analysis

Thus, schema design depends on organizational requirements.

3. Data Warehouse Construction Process

The process of building a data warehouse involves five major stages:

Data Sources
│
▼
Extraction
│
▼
Transformation
│
▼
Data Cleaning
│
▼
Data Modeling
│
▼
Loading
│
▼
Data Warehouse
4. Step 1: Data Extraction

Definition

Extraction is the process of collecting data from multiple heterogeneous

sources.

Sources of Data

 Relational Databases

 Legacy Systems

 ERP Systems

 CRM Systems

 Spreadsheets

 Web Services

 Financial Data Feeds

 Environmental Data Sources

Challenges

 Different formats

 Different platforms

 Different database models

5. Step 2: Data Formatting and Integration

Definition

Data from different sources must be standardized before storage.

Problems Encountered

Different Naming Conventions

Cust_ID
Customer_ID
CID

All must be standardized.

Different Data Formats

DD/MM/YYYY
MM/DD/YYYY
YYYY-MM-DD

Need conversion into a common format.

Different Fiscal Calendars

Example:

Quarter
Company
End

Subsidiary
March
A

Subsidiary
April
B

These inconsistencies must be reconciled.

6. Step 3: Data Cleaning

Definition

Data Cleaning improves data quality before loading into the warehouse.

It is often the most time-consuming phase of warehouse development.

Data Quality Problems

Missing Values

Example:

Customer Name = NULL

Duplicate Records

Ashwin Kumar
A. Kumar
Ashwin K.

May represent the same customer.

Invalid Values

Example:

Age = -5

Inconsistent Data

Example:

City = San Francisco

State = Connecticut

This combination is invalid.

Data Cleaning Activities

 Error detection

 Error correction

 Duplicate elimination

 Domain validation

 Standardization

7. Backflushing

Definition

Backflushing is the process of sending cleaned data back to source

systems.

Source System
│
▼
Data Cleaning
│
▼
Data Warehouse
│
▼
Backflushing
│
▼
Source System Updated
Benefits

 Improves source quality

 Reduces future errors

 Enhances consistency

8. Step 4: Data Modeling

Definition

Data from source systems must be transformed into the warehouse data
model.

Source Models

 Relational

 Object-Oriented

 Network

 Hierarchical

 Legacy Databases

Target Model

Usually:

Multidimensional Model

Implemented through:

 Fact Tables

 Dimension Tables

 Star Schema

 Snowflake Schema

9. Step 5: Data Loading

Definition

Loading transfers transformed data into the warehouse.

Challenges
Large Data Volumes

Warehouses may contain:

 Terabytes

 Petabytes

of data.

Requirements

Monitoring Tools

Track loading progress.

Recovery Mechanisms

Recover from failed loads.

Error Handling

Detect incorrect data loads.

10. Refresh Policies

Because reloading the entire warehouse is expensive, organizations use

refresh policies.

Full Refresh

Entire warehouse reloaded.

Advantages

 Consistency

Disadvantages

 Time-consuming

 Resource intensive

Incremental Refresh

Only changed records are updated.

Advantages
  Faster

 Lower cost

 Less downtime

Most commonly used approach.

11. Factors Influencing Refresh Policies

Warehouse administrators must consider:

Data Freshness

How current must the data be?

Downtime

Can the warehouse be offline?

Data Dependencies

What relationships exist between datasets?

Storage Availability

How much storage is available?

Distribution Requirements

Need for:

 Replication

 Partitioning

Loading Time

Includes:

 Extraction

 Cleaning

 Transformation
  Transmission

 Index rebuilding

12. Data Loading Order

Correct loading sequence is critical.

Example

Customer Master
│
▼
Sales Transactions
│
▼
Billing Data

Incorrect Order

Loading billing before customer information may violate:

 Integrity Constraints

 Business Rules

and cause failures.

13. Data Warehouse Storage Processes

Once data is loaded, several storage-related activities occur.

A. Data Storage

Store data according to:

 Star Schema

 Snowflake Schema

 Fact Constellation

B. Data Structures

Maintain:
  Fact Tables

 Dimension Tables

 Summary Tables

C. Access Paths

Create:

 Indexes

 Join Indexes

 Bitmap Indexes

for efficient querying.

D. Time-Variant Data

Store historical records.

Example:

Sales Data (2021–2026)

E. Data Refresh

Update warehouse periodically.

F. Data Purging

Remove outdated information.

Example:

Keep last 12 quarters only.

Older data may be archived or deleted.

14. Data Purging

Definition

Removing old warehouse data that is no longer required.

Benefits
  Saves storage

 Improves performance

 Simplifies maintenance

Example

2020 Data → Purged

2021–2026 Data → Retained

15. Data Warehouse Design Considerations

Several factors influence warehouse design.

1. Usage Projections

Determine:

 User groups

 Query patterns

 Reporting requirements

2. Data Model Fit

Select appropriate model:

 Star Schema

 Snowflake Schema

 Fact Constellation

3. Source Characteristics

Understand:

 Data quality

 Data volume

 Data formats
4. Metadata Design

Metadata describes warehouse contents and operations.

5. Modular Design

Warehouse components should be independent.

Benefits:

 Easier maintenance

 Scalability

 Future expansion

6. Manageability

Warehouse should support:

 Monitoring

 Maintenance

 Evolution

7. Distributed and Parallel Processing

Support:

 Multiple locations

 Large-scale processing

 High-performance analytics

16. Metadata Repository

Definition

A Metadata Repository stores information about warehouse data.

Metadata = "Data about Data"

Types of Metadata

A. Technical Metadata
Contains:

 Data sources

 ETL rules

 Storage structures

 Access paths

 Maintenance information

B. Business Metadata

Contains:

 Business definitions

 Business rules

 Organizational policies

 Reporting standards

Metadata Architecture

Metadata Repository
/ \
/ \
Technical Metadata Business Metadata

17. Distributed Data Warehouse Architecture

A warehouse may be distributed across multiple sites.

Characteristics

 Replication

 Partitioning

 Load Balancing

 High Availability

Architecture
Site A
│
│
Metadata Repository
│
│
Site B
│
│
Site C

Advantages

✔ Scalability

✔ Better performance

✔ Fault tolerance

✔ Improved availability

18. Federated Data Warehouse

Definition

A collection of autonomous warehouses working together.

Each warehouse maintains:

 Its own data

 Its own metadata

Architecture

Warehouse A
│
│
Warehouse B
│
│
Warehouse C

Each warehouse operates independently.

Benefits

 Local autonomy

 Easier management

 Reduced complexity

19. Data Marts in Federation

Large federated warehouses often consist of smaller units called Data

Marts.

Examples:

Sales Data Mart

Finance Data Mart
HR Data Mart
Marketing Data Mart

These provide specialized analytical views.

20. Next-Generation Data Warehousing

Traditional warehouses are evolving due to Big Data requirements.

New Challenges

 Massive Data Volumes

 Real-Time Analytics

 Unstructured Data

 Social Media Data

 IoT Data

Emerging Technologies

Big Data Platforms

Examples include:

 Apache Hadoop

 Spark Ecosystem
Analytic Appliances

Examples from industry include systems developed by organizations such

as:

 IBM

 SAP

These systems are optimized for analytical workloads.

Data Virtualization

Data remains at source locations while appearing as a unified warehouse.

Benefits:

 Reduced duplication

 Faster deployment

 Lower storage costs

Comprehensive Data Warehouse Construction Framework

Data Sources
(DBs, ERP, CRM, Files)
│
▼
Extraction
│
▼
Transformation
│
▼
Data Cleaning
│
▼
Data Modeling
│
▼
Loading
│
▼
 Data Warehouse
│
┌────────┼────────┐
│ │ │
OLAP DSS Data Mining
│
▼
Metadata & Reports
│
▼
Refresh / Purge

Summary

Building a Data Warehouse involves five major activities: Extraction,

Formatting, Cleaning, Modeling, and Loading (ETL). Data must be
integrated from multiple heterogeneous sources, standardized, cleansed,
transformed into multidimensional structures, and loaded efficiently. Key
considerations include refresh policies, data purging, metadata
management, modular design, scalability, and distributed
architectures. Modern data warehouses increasingly incorporate Big
Data platforms, data virtualization, and advanced analytical
technologies to support next-generation business intelligence and
decision-making systems.
Lecture Notes: 29.5 Typical Functionality of a Data Warehouse

Module: Data Warehouse Functionality and OLAP Operations

1. Introduction

A Data Warehouse is designed to support:

 Complex Queries

 Data Analysis

 Business Intelligence

 Decision Support Systems (DSS)

 Data Mining

Unlike transactional databases, which focus on processing daily business

operations, data warehouses are optimized for:

✔ Large-scale analytical processing

✔ Historical data analysis

✔ Ad hoc querying

✔ Strategic decision-making

The primary objective is to help managers, analysts, and executives make

informed decisions using historical and integrated data.

2. Data Warehouse Access Component

The Access Component acts as the interface between users and the
data warehouse.

Functions

 Query Processing

 OLAP Analysis

 Reporting

 Spreadsheet Integration

 Data Mining
  Materialized View Access

Data Warehouse Access Architecture

Users
│
▼
Access Component
│
┌───────────┼───────────┐
│ │ │
OLAP Queries Data Mining
│
▼
Data Warehouse

3. Enhanced Spreadsheet Functionality

Modern data warehouses support advanced spreadsheet tools such as:

 Microsoft Excel

 Business Intelligence Dashboards

 OLAP Reporting Tools

These tools provide powerful analytical capabilities.

4. Roll-Up Operation (Drill-Up)

Definition

Roll-Up summarizes detailed data into higher levels of aggregation.

Example

Day
↓
Week
↓
Month
↓
Quarter
↓
Year
Sales Example

Mont Sale
h s

Jan 100

Feb 120

Mar 130

Roll-Up:

Quart Sale
er s

Q1 350

Applications

 Executive Reporting

 Trend Analysis

 Strategic Planning

5. Drill-Down Operation

Definition

Drill-Down reveals more detailed information from summarized data.

Example

Country
↓
State
↓
District
↓
City

Sales Analysis Example

India
↓
Karnataka
↓
Bengaluru
↓
Whitefield

Users move from summary-level data to detailed information.

Roll-Up vs Drill-Down

Roll-Up Drill-Down

Detailed → Summary →
Summary Detailed

Aggregation Disaggregation

Less Detail More Detail

6. Pivot (Rotation)

Definition

Pivoting changes the orientation of multidimensional data.

Also called:

Rotation
Cross-Tabulation

Example

Original View

Produ Regio Sale

ct n s

Laptop South 100

Pivoted View
Regio Produ Sale
n ct s

South Laptop 100

Advantages

 Multiple viewpoints

 Flexible reporting

 Better visualization

7. Slice and Dice Operations

Slice

Definition

Selecting a single value from one dimension to create a smaller view.

Example

Cube:

Product × Region × Time

Selecting:

Time = Q1

creates a 2-D slice.

Dice

Definition

Selecting multiple values from multiple dimensions.

Example

Products = Laptop, Mobile

Regions = South, North
Time = Q1, Q2

creates a sub-cube.
Benefits

 Detailed analysis

 Customized reporting

 Interactive exploration

8. Sorting

Definition

Organizing data based on a specific attribute.

Example

Produ Sale
ct s

Mobile 100

Laptop 300

Tablet 200

Sorted Descending:

Produ Sale
ct s

Laptop 300

Tablet 200

Mobile 100

Benefits

 Ranking

 Performance comparison

 Top-N analysis

9. Selection (Filtering)
Definition

Retrieving only records that satisfy specified conditions.

Example

SELECT *
FROM Sales
WHERE Region='South'

Result

Only South region sales are displayed.

Applications

 Regional Analysis

 Product Analysis

 Customer Segmentation

10. Derived (Computed) Attributes

Definition

New attributes calculated from existing values.

Examples

Profit

Profit = Revenue − Cost

Growth Rate

Growth Rate =
(Current Sales - Previous Sales)
/ Previous Sales × 100

Average Revenue
Average Revenue =
Total Revenue / Number of Customers

Benefits

 Additional insights

 Better forecasting

 Performance evaluation

11. Efficient Query Processing

Data warehouses are optimized for complex analytical queries.

Why Faster?

Because warehouses:

 Are read-oriented

 Have fewer updates

 Use specialized indexing

 Store aggregated data

12. Query Optimization Techniques

Several techniques improve query performance.

A. Query Transformation

Definition

Rewriting queries into more efficient forms.

Example

Complex query:

SELECT *
FROM Sales
WHERE Year=2025

may be transformed to use summary tables.

Benefits

 Faster execution

 Reduced resource consumption

13. Index Intersection

Definition

Combining multiple indexes to answer a query.

Example

Indexes:

Region Index
Year Index

Query:

Region = South
Year = 2025

Uses both indexes simultaneously.

14. Index Union

Definition

Combining multiple index results.

Example

Region = South
OR
Region = North

Results from both indexes are merged.

15. Advanced Join Methods

Data warehouses use optimized joins.

Examples:

 Hash Join

 Merge Join

 Star Join

 Bitmap Join

Benefits

 Faster fact-dimension table access

 Reduced query time

16. Intelligent Scanning

Definition

Processing multiple queries in a single scan of warehouse data.

Example

Instead of scanning a table three times:

Query 1
Query 2
Query 3

the warehouse scans once and serves all queries.

Benefits

 Reduced I/O

 Faster execution

17. OLAP Technologies

OLAP systems support multidimensional analysis.

There are three major OLAP architectures:

18. ROLAP (Relational OLAP)

Definition

Stores data in relational databases.

Uses:

 Tables

 SQL Queries

 Relational Schema

Architecture

Users
│
▼
ROLAP Engine
│
▼
Relational Database

Advantages

 Scalable

 Handles large datasets

Disadvantages

 Slower than MOLAP

19. MOLAP (Multidimensional OLAP)

Definition

Stores data in multidimensional cubes.

Architecture

Users
│
▼
MOLAP Engine
│
 ▼
Data Cube

Advantages

 Extremely fast query processing

 Excellent aggregation performance

Disadvantages

 More storage requirements

20. HOLAP (Hybrid OLAP)

Definition

Combines ROLAP and MOLAP.

Architecture

HOLAP
/ \
/ \
MOLAP ROLAP
(Summary Data) (Detail Data)

Working

Summary Data

Stored in cubes (MOLAP).

Detailed Data

Stored in relational tables (ROLAP).

Drill Through

Users can move from cube summaries to detailed relational records.

Advantages

✔ Fast performance
✔ Large-scale storage

✔ Flexible analysis

21. Parallel Processing in Data Warehouses

Large warehouses use parallel architectures to improve performance.

A. SMP (Symmetric Multiprocessing)

Characteristics

 Multiple CPUs

 Shared memory

CPU1
CPU2
CPU3
│
Shared Memory

B. Cluster Architecture

Characteristics

 Multiple servers

 Connected network

Server 1
│
Server 2
│
Server 3

C. MPP (Massively Parallel Processing)

Characteristics

 Hundreds or thousands of processors

 Independent processing

Processor 1
Processor 2
Processor 3
Processor N

Benefits of Parallel Processing

 Faster queries

 Better scalability

 Improved availability

22. Managed Query Environment

Data warehouses support:

Parametric Queries

Predefined queries with parameters.

Example:

Sales Report for Year = ?

Ad Hoc Queries

User-created queries.

Example:

Compare South Region laptop sales

for Q1 and Q2.

Benefits

 Flexible analysis

 User independence

 Faster decision-making

23. Data Mining Support

Definition

Data Mining discovers hidden knowledge from warehouse data.

Applications

Classification

Example:

 Loan Approval

 Customer Segmentation

Clustering

Example:

 Similar customer groups

Association Rules

Example:

Customers buying bread

also buy butter.

Prediction

Example:

 Future Sales

 Customer Churn

24. Statistical Analysis Techniques

Data warehouses support statistical processing.

A. Lag Analysis

Compares current and previous values.

Example:

Sales(Current Month)
vs
Sales(Previous Month)
B. Moving Average

Smooths fluctuations.

Formula:

Moving Average =
Sum of Previous N Values / N

C. Regression Analysis

Studies relationships between variables.

Example:

Advertising Cost
vs
Sales Revenue

Used for forecasting.

25. Artificial Intelligence Techniques in Data Warehousing

AI techniques help discover hidden patterns.

Neural Networks

Applications:

 Classification

 Prediction

 Pattern Recognition

Genetic Algorithms

Applications:

 Optimization

 Rule Discovery

Benefits

 Discover unexpected patterns

  Improve forecasting accuracy

 Enhance business intelligence

Typical Data Warehouse Functionality Framework

Data Warehouse
│
┌───────────────────┼───────────────────┐
│ │ │
OLAP Queries Data Mining
│ │ │
▼ ▼ ▼
Roll-Up Structured Statistical
Drill-Down Ad Hoc Analysis
Pivot Parametric AI Techniques
Slice & Dice Filtering Neural Networks
Sorting Selection Genetic Algorithms

Summary

The typical functionality of a Data Warehouse centers on supporting

OLAP, decision support, and data mining. Key analytical operations
include Roll-Up, Drill-Down, Pivot, Slice and Dice, Sorting,
Selection, and Derived Attributes. Efficient query processing is
achieved through techniques such as query transformation, advanced
indexing, intelligent scanning, and optimized joins. Data
warehouses support ROLAP, MOLAP, and HOLAP architectures, while
parallel processing improves scalability and performance. Additionally,
statistical methods and AI-based techniques such as regression
analysis, neural networks, and genetic algorithms help
organizations extract valuable knowledge and make data-driven decisions.
Lecture Notes: 29.6 Data Warehouse versus Views

Module: Data Warehousing Concepts

1. Introduction

In database systems, both Views and Data Warehouses provide users

with customized access to data. Because both offer read-oriented access
and support data analysis, some people consider a data warehouse to be
an extension of database views.

However, a Data Warehouse is much more powerful and sophisticated

than a traditional database view.

2. What is a View?

Definition

A View is a virtual table derived from one or more base tables using a
query.

The data in a view is not stored separately; it is generated from underlying

tables whenever the view is accessed.

Example

Base Table:
Student_ Departme
Name
ID nt

Ashwi
101 CSE
n

102 Ravi ECE

View:

CREATE VIEW CSE_Students AS

SELECT *
FROM Student
WHERE Department='CSE';

Result:

Student_
Name
ID

Ashwi
101
n

3. What is a Materialized View?

Definition

A Materialized View stores the result of a query physically in the

database.

Unlike ordinary views, materialized views improve performance by

avoiding repeated query execution.

Example

Sales Summary by Quarter

can be stored physically and refreshed periodically.

4. What is a Data Warehouse?

Definition

A Data Warehouse is a centralized repository of integrated, historical,

subject-oriented, nonvolatile, and time-variant data used for analytical
processing and decision-making.

Characteristics
  Persistent storage

 Historical data

 Multidimensional structure

 Integrated data sources

 Analytical processing support

5. Similarities Between Data Warehouses and Views

Although they are different technologies, they share certain similarities.

A. Read-Only Access

Both are primarily used for reading and analyzing data.

Example

Users can:

 View reports

 Generate summaries

 Perform analysis

without modifying source data.

B. Subject-Oriented Access

Both can organize data according to business subjects.

Examples:

 Sales

 Customers

 Products

 Employees

C. Support for Querying

Both allow users to retrieve data using queries.

Example:
SELECT *
FROM Sales
WHERE Region='South';

6. Data Warehouse vs View

The following differences clearly distinguish a data warehouse from a

database view.

Difference 1: Persistence

View

A view is generally virtual and generated when requested.

Base Tables
│
▼
View
(On-Demand Creation)

Data Warehouse

A data warehouse exists as permanent storage.

Source Systems
│
▼
Data Warehouse
(Persistent Storage)

Key Point

Data
View
Warehouse

Virtua
Persistent
l

Difference 2: Data Model

View
Typically based on relational tables.

Example:

Rows and Columns

Data Warehouse

Uses multidimensional models.

Examples:

 Data Cubes

 Star Schema

 Snowflake Schema

Key Point

Data
View
Warehouse

Relation Multidimension
al al

Difference 3: Aggregation Levels

View

Provides data exactly as defined in the query.

Limited aggregation.

Data Warehouse

Supports multiple aggregation levels.

Example:

Day
↓
Month
↓
Quarter
 ↓
Year

Users can perform:

 Roll-Up

 Drill-Down

 Slice

 Dice

Key Point

View Data Warehouse

Limited Multiple Aggregation

Aggregation Levels

Difference 4: Indexing

View

Cannot have independent indexes separate from underlying tables.

Performance depends on base table indexes.

Data Warehouse

Supports specialized indexing techniques:

 Bitmap Index

 Join Index

 Star Join Index

Benefits

 Faster querying

 Better OLAP performance

Key Point
View Data Warehouse

Depends on Base Table Independent Warehouse

Indexes Indexes

Difference 5: Specialized Functionality

View

Provides only data retrieval.

Limited analytical capabilities.

Data Warehouse

Supports advanced analytical functions.

Examples:

 Roll-Up

 Drill-Down

 Pivot

 Slice and Dice

 Data Mining

 OLAP

Key Point

Data
View
Warehouse

Data Advanced
Retrieval Analytics

Difference 6: Data Volume

View

Usually extracts data from one database.

Limited scope.
Data Warehouse

Contains huge volumes of enterprise-wide data.

May store:

 Terabytes

 Petabytes

of information.

Key Point

Data
View
Warehouse

Small Massive
Subset Repository

Difference 7: Data Sources

View

Created from tables within a database.

Database
│
▼
View

Data Warehouse

Combines data from multiple heterogeneous sources.

ERP
CRM
Files
Databases
External Sources
│
▼
Data Warehouse

Key Point
 Data
View
Warehouse

Single Multiple
Source Sources

Difference 8: ETL Process

View

Created using SQL queries.

No complex preprocessing.

Data Warehouse

Built through a comprehensive ETL process.

Extract
↓
Transform
↓
Clean
↓
Load

Activities Included

 Data Cleaning

 Data Integration

 Summarization

 Standardization

 Transformation

Key Point
 Data
View
Warehouse

Query-
ETL-Based
Based

Difference 9: Historical Data Support

View

Generally reflects current operational data.

Data Warehouse

Maintains historical and time-variant data.

Example:

Yea Reven
r ue

202
50 Cr
1

202
60 Cr
2

202
72 Cr
3

202
85 Cr
4

Key Point

Data
View
Warehouse

Current
Historical Data
Data

Difference 10: Decision Support

View
Supports operational reporting.

Data Warehouse

Supports:

 Business Intelligence

 Decision Support Systems (DSS)

 OLAP

 Data Mining

 Strategic Planning

Key Point

View Data Warehouse

Operational Strategic Decision

Reporting Support

Comprehensive Comparison Table

Data
Feature View
Warehouse

Storage Virtual Persistent

Data Model Relational Multidimensional

Aggregation Limited Multiple Levels

No Independent Specialized
Indexing
Indexes Indexing

Data Volume Small Very Large

Data Sources Single Database Multiple Sources

Historical Data Limited Extensive

ETL Process Not Required Required

Analytics Basic Advanced OLAP

Data Mining Not Supported Supported

 Data
Feature View
Warehouse

Decision
Limited Extensive
Support

Automatic from
Refresh Periodic Refresh
Source

Architecture Comparison

View-Based Architecture

Base Tables
│
▼
View
│
▼
Users

Data Warehouse Architecture

Multiple Data Sources

(DBs, ERP, CRM, Files)
│
▼
ETL
│
▼
Data Warehouse
│
┌────────┼────────┐
│ │ │
OLAP DSS Data Mining

Advantages of Data Warehouses Over Views

Better Performance

Optimized for analytical processing.

Historical Analysis

Stores years of historical data.

Multidimensional Analysis

Supports cubes and OLAP operations.

Integrated Information

Combines data from multiple sources.

Advanced Decision Support

Supports strategic business decisions.

Data Mining Capabilities

Discovers hidden patterns and trends.

Summary

Although Views and Data Warehouses both provide read-oriented

access to data, a Data Warehouse is far more comprehensive. A view is
simply a virtual or materialized representation of data from one or more
tables, whereas a data warehouse is a persistent, integrated,
multidimensional repository designed specifically for analytical
processing and decision support. Data warehouses support ETL
processing, historical data storage, OLAP operations, specialized
indexing, data mining, and business intelligence, making them
significantly more powerful than traditional database views.

Lecture Notes: 29.7 Difficulties of Implementing Data Warehouses

Module: Challenges in Data Warehouse Implementation and

Management

1. Introduction

A Data Warehouse (DW) provides significant benefits for:

 Online Analytical Processing (OLAP)

  Decision Support Systems (DSS)

 Business Intelligence (BI)

 Data Mining

However, implementing and maintaining a data warehouse is a complex

and challenging task.

The major difficulties arise in:

1. Construction

2. Administration

3. Data Quality Control

4. Change Management

5. Performance Optimization

6. Team Management

2. Major Challenges in Data Warehouse Implementation

Data Warehouse
│
┌─────────────┼─────────────┐
│ │ │
Construction Administration Quality Control
│ │ │
└─────────────┼─────────────┘
│
Change Management
│
Performance Tuning

3. Challenge 1: Construction of the Data Warehouse

Definition

Construction involves:

 Planning

 Designing

 Developing

 Deploying
the warehouse infrastructure.

Why is Construction Difficult?

Large organizations generate data from:

 ERP systems

 CRM systems

 Legacy databases

 Cloud platforms

 External data sources

Integrating all these systems is a major challenge.

Characteristics of Large Warehouse Projects

Enterprise-Wide Warehouses

Often involve:

 Multiple departments

 Multiple locations

 Multiple business units

Time Requirements

Construction may take:

Months → Years

from planning to implementation.

Resource Requirements

Require:

 Large budgets

 Skilled personnel

 Specialized software

 Hardware infrastructure
4. Project Management Challenges

A data warehouse project requires careful project management.

Activities Involved

Planning

Define:

 Scope

 Objectives

 Budget

 Timeline

Design

Determine:

 Data Models

 Schemas

 ETL Processes

Implementation

Build:

 Warehouse Infrastructure

 ETL Pipelines

 Reporting Systems

Testing

Validate:

 Accuracy

 Performance

 Security
Risks

 Cost overruns

 Schedule delays

 Scope creep

 Technical failures

5. Data Marts as an Alternative

Because enterprise-wide warehouses are expensive and time-consuming,

organizations often begin with Data Marts.

Data Mart

A smaller warehouse focused on a specific department.

Examples:

 Sales Data Mart

 Finance Data Mart

 HR Data Mart

Advantages

✔ Faster implementation

✔ Lower cost

✔ Quick business benefits

Comparison

Enterprise
Data Mart
Warehouse

Department-
Organization-wide
specific

Expensive Affordable
Enterprise
Data Mart
Warehouse

Long development
Quick deployment
time

Highly complex Less complex

6. Challenge 2: Data Warehouse Administration

Definition

Administration involves managing and maintaining warehouse operations.

Why is Administration Difficult?

Although warehouses are mainly read-oriented:

Read Mostly ≠ Static System

The warehouse continuously evolves.

Administrative Responsibilities

Data Refresh

Updating warehouse data periodically.

Monitoring

Tracking:

 Performance

 Storage

 Query execution

Security Management

Managing:

 User access

 Authentication
  Authorization

Backup and Recovery

Protecting warehouse data.

Performance Tuning

Optimizing query execution.

7. Dynamic Nature of Data Sources

Source databases continuously change.

Examples:

New Attributes Added

Tables Modified
Business Rules Updated

Impact on Warehouse

Changes may require:

 Schema modifications

 ETL updates

 Metadata updates

Example

Source System:

Customer Table

New field added:

Customer Loyalty Score

The warehouse must be modified to accommodate this change.

8. Challenge 3: Data Quality Control

Definition
Data quality control ensures that warehouse data is:

 Accurate

 Complete

 Consistent

 Reliable

Importance

Poor-quality data leads to:

 Incorrect reports

 Faulty analysis

 Poor business decisions

9. Data Quality Problems

A. Inconsistent Data

Example:

Source Source
A B

Bengalur Bangalor
u e

Both represent the same city.

B. Naming Differences

Example:

Cust_ID
Customer_ID
Client_ID

Different systems use different names.

C. Domain Differences

Example:
Gender = M/F
Gender = Male/Female

Requires standardization.

D. Duplicate Data

Example:

Ashwin Kumar
A. Kumar
Ashwin K.

May refer to the same individual.

10. Data Integration Challenges

Data warehouses combine data from:

ERP
CRM
Legacy Systems
External Sources

Challenges

Different Formats

DD/MM/YYYY
MM/DD/YYYY
YYYY-MM-DD

Different Codes

State Code = KA
State Name = Karnataka

Different Identifiers

Different systems may use different customer IDs.

11. Master Data Management (MDM) Challenges

Dimension data often represent:

 Customers

 Products

 Regions

 Employees

These are called Master Data.

Problems

Different departments may define entities differently.

Example:

Sales Department:

Premium Customer = Purchase > ₹50,000

Finance Department:

Premium Customer = Purchase > ₹75,000

Such inconsistencies must be resolved.

12. Continuous Impact of Source Changes

Every source system change affects the warehouse.

Source Change
│
▼
ETL Update
│
▼
Schema Update
│
▼
Metadata Update

Result

Warehouse maintenance becomes a continuous process.

13. Challenge 4: Usage Projection

Definition

Usage projection estimates:

 Number of users

 Query volume

 Storage requirements

 Future growth

Importance

Incorrect estimates can result in:

Underestimation

 Poor performance

 Resource shortages

Overestimation

 Wasted resources

 Increased costs

14. Continuous Performance Tuning

As user behavior changes:

 Query patterns change

 Data volume grows

 New reports are created

Warehouse performance must be continually optimized.

Optimization Activities

Index Tuning

Examples:

 Bitmap Indexes

 Join Indexes
Storage Optimization

 Compression

 Partitioning

Access Path Optimization

Improving query execution strategies.

15. Challenge 5: Accommodating New Data Sources

Organizations continuously adopt new systems.

Examples:

Cloud Platforms
IoT Systems
Social Media Data
Mobile Applications

Requirement

Warehouse design should allow:

✔ Addition of new sources

✔ Removal of obsolete sources

without major redesign.

16. Challenge 6: Data Modeling Difficulties

One of the most difficult tasks is fitting source data into the warehouse
model.

Why Difficult?

Different sources may have:

 Different structures

 Different meanings
  Different granularity levels

Example

Sales System:

Daily Sales

Finance System:

Quarterly Revenue

Combining them requires careful modeling.

17. Technology Evolution Challenge

Technology changes rapidly.

Examples:

 Cloud Data Warehouses

 Hadoop Ecosystems

 Data Lakes

 AI Analytics

 Real-Time Streaming

Impact

Warehouses must evolve continuously.

Challenges

 Software upgrades

 Hardware upgrades

 Migration activities

 New integration methods

18. Importance of Modular Design

Definition
Modular design divides the warehouse into manageable components.

Benefits

Easier Maintenance

Changes affect only one module.

Better Scalability

New modules can be added.

Reduced Risk

Problems remain localized.

Modular Architecture

ETL Module
│
Metadata Module
│
Storage Module
│
Reporting Module

19. Challenge 7: Human Resource Requirements

Data warehouse administration requires broader skills than traditional

database administration.

Required Expertise

Technical Skills

 Database Management

 ETL Tools

 Data Modeling

 OLAP Technologies
  Performance Tuning

Business Skills

Understanding:

 Business Processes

 Policies

 Regulations

 Organizational Rules

Communication Skills

Coordinate with:

 Managers

 Analysts

 Developers

 Executives

20. Team-Based Administration

Large warehouses cannot usually be managed by one person.

Required Team

Project Manager
│
Data Architect
│
ETL Developer
│
Database Administrator
│
Business Analyst
│
Security Specialist
Benefits

✔ Shared expertise

✔ Better decision-making

✔ Improved maintenance

21. Management Challenges

Managing a warehouse is a major organizational responsibility.

Key Management Activities

Planning

Future warehouse growth.

Coordination

Managing multiple teams.

Governance

Defining:

 Standards

 Policies

 Procedures

Risk Management

Handling:

 Data loss

 Security threats

 System failures

22. Commercial Management Tools

Many commercial tools support warehouse management.

Examples include platforms from companies such as:

 IBM

 SAP

 Oracle Corporation

These tools assist in:

 Monitoring

 Scheduling

 Metadata Management

 Performance Optimization

23. Evolution of Management Skills

As the warehouse evolves:

Technology Changes
│
▼
Warehouse Changes
│
▼
Skill Requirements Change

Therefore

Warehouse administrators must continuously learn:

 New technologies

 New business requirements

 New analytical methods

 New governance practices

Comprehensive Diagram: Difficulties in Data Warehouse

Implementation

Data Warehouse
│
┌──────────────────────┼──────────────────────┐
 │ │ │
Construction Administration Quality Control
│ │ │
Project Mgmt Maintenance Data Consistency
ETL Design Monitoring Data Cleaning
Schema Design Security Data Integration
│ │ │
└──────────────────────┼──────────────────────┘
│
Technology Evolution
│
▼
Continuous Change
│
▼
Skilled Team Required

Summary

Implementing a Data Warehouse is a challenging task involving

construction, administration, data quality control, performance
management, and continuous adaptation to changing
technologies and business requirements. Major difficulties include
integrating heterogeneous data sources, maintaining data consistency,
managing evolving schemas, handling massive data volumes, and
supporting growing user demands. Successful implementation requires
careful project management, modular design, robust governance,
continuous performance tuning, and a multidisciplinary team with
both technical and business expertise. Effective warehouse
management is therefore an ongoing organizational effort rather than a
one-time technical project.
Lecture Notes: Big Data Analytics and Integration with NoSQL
Systems

Course: Advanced Database Management and NoSQL

Module: Big Data Analytics and Integration with NoSQL Systems

1. Introduction

The rapid growth of digital technologies, social media, IoT devices, cloud
computing, mobile applications, and enterprise systems has resulted in
the generation of enormous volumes of data. Traditional Relational
Database Management Systems (RDBMS) often struggle to handle such
large-scale, diverse, and rapidly changing datasets.

Big Data Analytics combined with NoSQL databases provides

organizations with scalable, flexible, and efficient mechanisms to store,
process, analyze, and extract valuable insights from massive datasets.

2. Learning Objectives

After studying this module, students should be able to:

 Understand Big Data concepts and characteristics.

 Explain Big Data Analytics architecture.

  Describe NoSQL database systems.

 Understand integration between Big Data platforms and NoSQL

databases.

 Analyze real-world applications of Big Data Analytics.

 Explain challenges and future trends.

3. What is Big Data?

Definition

Big Data refers to extremely large and complex datasets that cannot be
effectively processed using traditional database management systems.

Examples

 Social media posts

 Online transactions

 Sensor data

 Healthcare records

 Financial market data

 Video and image repositories

4. Characteristics of Big Data (5Vs)

1. Volume

Refers to the enormous amount of data generated.

Examples:

 Terabytes (TB)

 Petabytes (PB)

 Exabytes (EB)

2. Velocity

Speed at which data is generated and processed.

Examples:

 Real-time stock market transactions

  IoT sensor streams

 Social media updates

3. Variety

Different forms of data.

Structured Data

 Relational tables

Semi-Structured Data

 XML

 JSON

Unstructured Data

 Images

 Videos

 Audio

 Text

4. Veracity

Quality and reliability of data.

Challenges:

 Missing values

 Duplicate records

 Inconsistent information

5. Value

Useful insights derived from data.

Examples:

 Customer behavior prediction

 Fraud detection

 Sales forecasting
5. Big Data Analytics

Definition

Big Data Analytics refers to the process of examining large datasets to

discover hidden patterns, correlations, trends, and actionable insights.

6. Types of Big Data Analytics

A. Descriptive Analytics

Answers:

"What happened?"

Example:

 Monthly sales reports

B. Diagnostic Analytics

Answers:

"Why did it happen?"

Example:

 Analysis of sales decline

C. Predictive Analytics

Answers:

"What is likely to happen?"

Example:

 Demand forecasting

D. Prescriptive Analytics

Answers:

"What should be done?"

Example:
  Recommending inventory levels

7. Big Data Analytics Architecture

Data Sources
(Social Media, IoT, ERP, CRM)
│
▼
Data Ingestion
│
▼
Data Storage
│
▼
Data Processing
│
▼
Analytics Engine
│
▼
Visualization & Reports

8. Data Sources in Big Data

Internal Sources

 ERP systems

 CRM systems

 Transaction databases

External Sources

 Social media

 Sensors

 Web logs

 Public datasets

9. Big Data Processing Frameworks

Popular frameworks include:

Apache Hadoop

Open-source distributed storage and processing framework.

Apache Spark

In-memory processing framework for fast analytics.

Apache Flink

Real-time stream processing framework.

Apache Storm

Distributed stream processing system.

10. Hadoop Ecosystem

5

Components

HDFS (Hadoop Distributed File System)

Stores data across multiple nodes.

MapReduce

Processes large datasets in parallel.

YARN

Resource management system.

Hive

SQL-like querying.
Pig

Data flow scripting language.

11. Introduction to NoSQL Databases

Definition

NoSQL (Not Only SQL) databases are non-relational database systems

designed to handle large-scale, distributed, and flexible data structures.

12. Why NoSQL?

Traditional RDBMS limitations:

 Fixed schema

 Limited scalability

 Vertical scaling

 Poor handling of unstructured data

NoSQL solutions offer:

 Horizontal scaling

 Flexible schema

 High availability

 Distributed architecture

13. Characteristics of NoSQL Databases

 Schema flexibility

 Horizontal scalability

 High performance

 Distributed storage

 Fault tolerance

 Cloud compatibility

14. Types of NoSQL Databases

1. Key-Value Databases

Store data as key-value pairs.

Examples:

 Redis

 Riak

2. Document Databases

Store JSON-like documents.

Examples:

 MongoDB

 CouchDB

3. Column-Family Databases

Store data in columns rather than rows.

Examples:

 Apache Cassandra

 Apache HBase

4. Graph Databases

Store interconnected data.

Examples:

 Neo4j

 JanusGraph

15. NoSQL Database Classification

NoSQL Databases
│
┌────┼────┬────┐
│ │ │ │
Key Doc Column Graph
Value Family
16. Integration of Big Data with NoSQL Systems

Big Data platforms frequently use NoSQL databases for scalable storage
and retrieval.

Reasons for Integration

Scalability

Handles billions of records.

Flexibility

Supports structured and unstructured data.

High Availability

Ensures continuous access.

Real-Time Processing

Supports fast data ingestion.

17. Big Data–NoSQL Integration Architecture

Data Sources
│
▼
Data Ingestion Layer
│
▼
NoSQL Database Layer
│
▼
Big Data Processing Layer
(Hadoop/Spark)
│
▼
Analytics Engine
│
▼
Visualization Dashboard

18. Hadoop and NoSQL Integration

Common integrations:

Hadoop NoSQL
Component Database

HDFS Cassandra

Hive HBase

Spark MongoDB

MapReduce Cassandra

19. Apache Spark and NoSQL

Benefits:

 Real-time analytics

 Machine learning support

 Fast in-memory processing

Example:

MongoDB
│
▼
Apache Spark
│
▼
Predictive Analytics

20. Data Analytics Workflow

Data Collection
│
▼
Data Storage (NoSQL)
│
▼
Data Processing
│
▼
Machine Learning
│
 ▼
Visualization
│
▼
Decision Making

21. Applications of Big Data Analytics with NoSQL

Banking

 Fraud detection

 Risk assessment

 Credit scoring

Healthcare

 Disease prediction

 Patient monitoring

 Medical image analytics

E-Commerce

 Product recommendations

 Customer segmentation

 Demand forecasting

Social Media

 Sentiment analysis

 Trend detection

 User behavior analysis

Smart Cities

 Traffic management

 Energy optimization
  Environmental monitoring

22. Machine Learning with NoSQL Data

NoSQL databases support machine learning workflows.

Examples:

 Classification

 Clustering

 Regression

 Recommendation systems

23. CAP Theorem in NoSQL

CAP Properties

Consistency (C)

All nodes contain the same data.

Availability (A)

System remains operational.

Partition Tolerance (P)

System works despite network failures.

CAP Triangle

Consistency
▲
/\
/ \
/ \
/ \
Availability-----Partition Tolerance

NoSQL databases typically optimize two of the three properties.

24. Challenges of Big Data and NoSQL Integration

Data Security
Protecting sensitive information.

Data Quality

Handling inconsistent and incomplete data.

Data Governance

Managing ownership and compliance.

Integration Complexity

Combining multiple systems.

Performance Optimization

Managing huge workloads.

25. Security Considerations

Authentication

Verify user identity.

Authorization

Control data access.

Encryption

Protect stored and transmitted data.

Auditing

Track system activities.

26. Future Trends

Artificial Intelligence Integration

AI-powered analytics systems.

Real-Time Analytics
Streaming analytics for immediate insights.

Edge Computing

Processing data near the source.

Cloud-Native NoSQL Databases

Scalable cloud deployments.

Generative AI Analytics

Automated insight generation and decision support.

27. Advantages of Big Data Analytics with NoSQL

Feature Benefit

Handles massive
Scalability
datasets

Flexibility Supports diverse data

Performance Fast processing

Availability High uptime

Cost Efficiency Commodity hardware

Real-Time
Faster decisions
Analytics

28. Limitations

Limitation Description

Complex Distributed systems are harder to

Management manage

Security Challenges Large attack surface

Data Consistency
CAP trade-offs
Issues
Limitation Description

Skill Requirements Specialized expertise needed

Integration
Multiple technologies involved
Complexity

Summary

Big Data Analytics and NoSQL Systems work together to manage,

process, and analyze massive volumes of structured, semi-structured, and
unstructured data. Big Data platforms such as Apache Hadoop and
Apache Spark provide large-scale processing capabilities, while NoSQL
databases such as MongoDB, Apache Cassandra, and Neo4j offer
scalable and flexible storage. Their integration enables real-time analytics,
machine learning, predictive modeling, and business intelligence
applications across industries such as healthcare, finance, e-commerce,
social media, and smart cities.