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 reporting Supports pattern discovery

Contains historical data Finds hidden trends

Provides organized data Generates actionable 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 Data Transformed Data

Item Codes Product Categories

ZIP Codes Geographic Regions

Income Values Income Ranges

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

Uses predefined rules Learns patterns from data

Rule-based reasoning Data-driven discovery

Example: Mathematical proofs Example: Data mining

Predictable conclusions Hidden knowledge 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 ID Items Purchased

101 Milk, Bread, Cookies, Juice

792 Milk, Juice

Transaction ID Items Purchased

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 𝑋 ∪ 𝑌
𝑆𝑢𝑝𝑝𝑜𝑟𝑡(𝑋 → 𝑌) =
Total Transactions

Example

Itemset:

{Milk, Juice}

Appears in:

• Transaction 101

• Transaction 792

Total transactions = 4
2
𝑆𝑢𝑝𝑝𝑜𝑟𝑡 = = 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
𝑆𝑢𝑝𝑝𝑜𝑟𝑡(𝑋 ∪ 𝑌)
𝐶𝑜𝑛𝑓𝑖𝑑𝑒𝑛𝑐𝑒(𝑋 → 𝑌) =
𝑆𝑢𝑝𝑝𝑜𝑟𝑡(𝑋)

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
𝐶𝑜𝑛𝑓𝑖𝑑𝑒𝑛𝑐𝑒 = = 66.7%
3

Interpretation

66.7% of customers buying Milk also buy Juice.

Difference Between Support and Confidence

Support Confidence

Measures frequency Measures strength

Support Confidence

Based on all transactions Based on 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

Item Support

Milk 0.75

Bread 0.50

Juice 0.50

Cookies 0.50

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

Itemset Support

Milk,Bread 0.25

Milk,Juice 0.50

Bread,Juice 0.25

Milk,Cookies 0.25

Bread,Cookies 0.50

Juice,Cookies 0.25

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
( ) = 499,500
2

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

Categorical Product Type

Quantitative Salary, Time

Quantitative Data Handling

Convert values into ranges.

Example:

Salary Category

0–29999 Low Income

30000–74999 Middle 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

Feature Apriori Sampling FP-Growth Partition

Candidate Generation Yes Yes No Yes

Database Scans Many Few Two Two

Speed Moderate Fast Very Fast Fast

Memory Usage High Low Moderate Low

Large Database Support Limited Good Excellent Excellent

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:

Customer Salary Age Loanworthy

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

Component Description

Root Node Starting node

Internal Node Decision based on 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
𝑛

𝐼(𝑆1 , 𝑆2 , … , 𝑆𝑛 ) = − ∑ 𝑝𝑖 log⁡2 𝑝𝑖
𝑖=1

Where:
 • 𝑝𝑖 = probability of class 𝑖

Interpretation

Entropy Meaning

0 Pure dataset

1 Maximum uncertainty

0–1 Partial impurity

Expected Entropy After Splitting

For an attribute A:
𝑚
∣ 𝑆𝑗 ∣
𝐸(𝐴) = ∑ × 𝐼(𝑆1𝑗 , 𝑆2𝑗 , . . . , 𝑆𝑛𝑗 )
∣𝑆∣
𝑗=1

Where:

• 𝑆𝑗 = partition produced by attribute A

Information Gain Formula

𝐺𝑎𝑖𝑛(𝐴) = 𝐼(𝑆) − 𝐸(𝐴)

Where:

• 𝐼(𝑆)= Original Entropy

• 𝐸(𝐴)= Expected Entropy after split

Rule

Choose the attribute with the highest gain.

Sample Training Dataset

RID Married Salary Acct Balance Age Loanworthy

1 No ≥50K <5K ≥25 Yes

2 Yes ≥50K ≥5K ≥25 Yes

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

4 No <20K ≥5K <25 No

5 No <20K <5K ≥25 No

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

Step 1: Calculate Initial Entropy

There are:

• Yes = 3

• No = 3

Thus:
3
𝑝(𝑌𝑒𝑠) = = 0.5
6
3
𝑝(𝑁𝑜) = = 0.5
6

Entropy:

𝐼(3,3) = −0.5log⁡2 (0.5) − 0.5log⁡2 (0.5)

𝐼(3,3) = 1

Step 2: Compute Information Gain

Attribute: Married

For Married = Yes:

• Yes = 2

• No = 1

Entropy:
 𝐼(2,1) = 0.92

For Married = No:

• Yes = 1

• No = 2

Entropy:

𝐼(1,2) = 0.92

Expected Entropy:
3 3
𝐸(𝑀𝑎𝑟𝑟𝑖𝑒𝑑) = (0.92) + (0.92)
6 6
𝐸(𝑀𝑎𝑟𝑟𝑖𝑒𝑑) = 0.92

Gain:

𝐺𝑎𝑖𝑛(𝑀𝑎𝑟𝑟𝑖𝑒𝑑) = 1 − 0.92 = 0.08

Attribute: Salary

𝐸(𝑆𝑎𝑙𝑎𝑟𝑦) = 0.33
𝐺𝑎𝑖𝑛(𝑆𝑎𝑙𝑎𝑟𝑦) = 1 − 0.33
𝐺𝑎𝑖𝑛(𝑆𝑎𝑙𝑎𝑟𝑦) = 0.67

Attribute: Account Balance

𝐸(𝐴𝑐𝑐𝑜𝑢𝑛𝑡𝐵𝑎𝑙𝑎𝑛𝑐𝑒) = 0.92
𝐺𝑎𝑖𝑛(𝐴𝑐𝑐𝑜𝑢𝑛𝑡𝐵𝑎𝑙𝑎𝑛𝑐𝑒) = 0.08

Attribute: Age

𝐸(𝐴𝑔𝑒) = 0.54
𝐺𝑎𝑖𝑛(𝐴𝑔𝑒) = 0.46
Attribute Selection

Attribute Gain

Married 0.08

Salary 0.67

Account Balance 0.08

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

Attribute Gain

Married 0

Age 1

Account Balance 1

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 Required Yes No

Class Labels Known Yes No

Objective Predict predefined classes Discover hidden groups

Example Loan approval Customer 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:

𝑟𝑗 = (𝑟𝑗1 , 𝑟𝑗2 , . . . , 𝑟𝑗𝑛 )

𝑟𝑘 = (𝑟𝑘1 , 𝑟𝑘2 , . . . , 𝑟𝑘𝑛 )

Distance:

2
𝐷𝑖𝑠𝑡𝑎𝑛𝑐𝑒(𝑟𝑗 , 𝑟𝑘 ) = √(𝑟𝑗1 − 𝑟𝑘1 )2 + (𝑟𝑗2 − 𝑟𝑘2 )2 + ⋯ + (𝑟𝑗𝑛 − 𝑟𝑘𝑛 )
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

RID Age Years of 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

Record Assigned 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:

∑𝑟 ∑𝑟 ∑𝑟
𝑀𝑒𝑎𝑛 = ( 𝑛 1 , 𝑛 2 , . . . 𝑛𝑚 )

Where:

• m = number of dimensions

• n = number of records

Squared Error Criterion

K-Means attempts to minimize the total clustering error.

Formula
𝑘

𝐸𝑟𝑟𝑜𝑟 = ∑ ∑ 𝐷𝑖𝑠𝑡𝑎𝑛𝑐𝑒( 𝑟𝑗 , 𝑚𝑖 )2
𝑟𝑗 ∈𝐶𝑖
𝑖=1
Where:

• 𝐶𝑖 = Cluster i

• 𝑚𝑖 = 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:

𝑂(𝑛)

where

𝑛 = 𝑁𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑅𝑒𝑐𝑜𝑟𝑑𝑠
This makes it highly efficient for big data applications.

K-Means vs BIRCH

Feature K-Means BIRCH

Memory Requirement High Low

Scalability Moderate Very High

Dataset Size Medium Very Large

Structure Used Centroids CF Tree

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

𝐸𝑢𝑐𝑙𝑖𝑑𝑒𝑎𝑛 𝐷𝑖𝑠𝑡𝑎𝑛𝑐𝑒

K-Means Steps

1. Select K centroids

2. Assign records

3. Recompute centroids

4. Repeat until convergence

Objective

𝑀𝑖𝑛𝑖𝑚𝑖𝑧𝑒 𝑆𝑞𝑢𝑎𝑟𝑒𝑑 𝐸𝑟𝑟𝑜𝑟

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
𝑆𝑢𝑝𝑝𝑜𝑟𝑡(𝑆) = × 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:

𝑌 = 𝑓(𝑋1 , 𝑋2 , 𝑋3 , … , 𝑋𝑛 )

Where:

• Y = Target variable

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

Linear Regression

When the relationship is linear:

𝑌 = 𝑎 + 𝑏𝑋

or

𝑌 = 𝑎 + 𝑏1 𝑋1 + 𝑏2 𝑋2 + ⋯ + 𝑏𝑛 𝑋𝑛

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 Mining Discover event sequences Frequent sequences

Time Series Mining Analyze temporal data Trends & forecasts

Regression Predict numeric values Continuous outputs

Neural Networks Learn complex patterns Predictive model

Genetic Algorithms Optimization & search Near-optimal 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

𝑌 = 𝑓(𝑋1 , 𝑋2 , … , 𝑋𝑛 )

• 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

Segment Characteristics

Regular Customers Frequent buyers

Occasional Customers Seasonal 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 Type Risk Level

Good Risk Loan Approved

Fair Risk Additional 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 behavior

Marketing
analysis, store layout design

Finance Credit scoring, investment analysis, fraud detection, risk management

Resource optimization, process improvement, product design, quality

Manufacturing
control

Medical diagnosis, gene analysis, drug effectiveness, hospital

Healthcare
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

CrossZ Data Distilleries Statistical Analysis, Optimization

DBMiner
DBMiner Comprehensive Data Mining
Technology

IBM Intelligent Miner Classification, Association Rules

Megaputer
PolyAnalyst Symbolic Knowledge Acquisition
Intelligence

Management Discovery Tool

NCR Association Rules
(MDT)

Purple Insight MineSet Decision Trees, Association Rules

Neural Networks, Regression,

SAS Enterprise Miner
Clustering

Comparison of Popular Tools

Tool Major Strength

IBM Intelligent Miner Enterprise analytics

SAS Enterprise Miner Advanced predictive modeling

MineSet Visualization

PolyAnalyst Knowledge discovery

DBMiner Comprehensive mining 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

Feature Traditional Database (OLTP) Data Warehouse (OLAP)

Purpose Transaction Processing Decision Support & Analysis

Operations Insert, Update, Delete Query and Analysis

Data Type Current Operational Data Historical and Integrated Data

Users Clerks, Operators Managers, Analysts

Optimization Fast Transaction Processing Fast Query Processing

Data Sources Single System Multiple Systems

Data Volume Moderate Very Large

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 Warehouse Repository of integrated 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

Ad Hoc Query User-defined query

Canned Query Predefined frequently used query

Subject-Oriented Organized around business subjects

Integrated Combined from multiple sources

Term Meaning

Nonvolatile Data is stable and rarely 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

Feature Transactional Database (OLTP) Data Warehouse (OLAP)

Purpose Operational Processing Analytical Processing

Data Current Data Historical Data

Updates Frequent Periodic

Queries Simple Complex

Users Clerks, Operators Managers, Analysts

Data Model Relational Multidimensional

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:

Year Sales

2022 ₹50 Crore

2023 ₹62 Crore

2024 ₹75 Crore

2025 ₹89 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

Purpose Operational Reporting Analytical Reporting

Data Current Historical

Updates Frequent Periodic

Users Operational Staff Analysts & Managers

Complexity Low High

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.

Product North South East West

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

Product Region Sales

Laptop South ₹50,000

Pivoted View

Region Quarter Sales

South Q1 ₹50,000

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:

Product North South East

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
Month Sales

Jan 50

Feb 60

Mar 70

Roll-Up:

Quarter Sales

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 Aggregation Lower 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_ID Region_ID Time_ID Sales

P1 R1 T1 10000

P2 R2 T1 15000
13. Dimension Table

Definition

Dimension tables describe business entities.

Examples

Product Dimension

Product_ID Product_Name Category

P1 Laptop Electronics

Region Dimension

Region_ID Region_Name

R1 South

Time Dimension

Time_ID Quarter Year

T1 Q1 2025
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

Feature Star Schema Snowflake Schema

Structure Denormalized Normalized

Complexity Simple Complex

Query Speed Faster Slower

Storage More Less

Maintenance Easy Difficult

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:

Car Economy

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

Quarter Revenue

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 Company Product, Sales, Customer Analysis

Nonprofit Organization Donor, Campaign, Fundraising Analysis

Bank Customer, Loan, Transaction 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:

Company Quarter End

Subsidiary A March

Subsidiary B April

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

Month Sales

Jan 100

Feb 120

Mar 130

Roll-Up:

Quarter Sales

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

Product Region Sales

Laptop South 100

Pivoted View
Region Product Sales

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

Product Sales

Mobile 100

Laptop 300

Tablet 200

Sorted Descending:

Product Sales

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_ID Name Department

101 Ashwin CSE

102 Ravi ECE

View:

CREATE VIEW CSE_Students AS

SELECT *
FROM Student
WHERE Department='CSE';

Result:

Student_ID Name

101 Ashwin
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

View Data Warehouse

Virtual Persistent

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

View Data Warehouse

Relational Multidimensional
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 Aggregation Multiple 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 Indexes Independent Warehouse 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

View Data Warehouse

Data Retrieval Advanced 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

View Data Warehouse

Small Subset Massive 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

View Data Warehouse

Single Source Multiple 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

View Data Warehouse

Query-Based ETL-Based

Difference 9: Historical Data Support

View

Generally reflects current operational data.

Data Warehouse

Maintains historical and time-variant data.

Example:

Year Revenue

2021 50 Cr

2022 60 Cr

2023 72 Cr

2024 85 Cr
Key Point

View Data Warehouse

Current Data Historical 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 Reporting Strategic Decision Support

Comprehensive Comparison Table

Feature View Data Warehouse

Storage Virtual Persistent

Data Model Relational Multidimensional

Aggregation Limited Multiple Levels

Indexing No Independent Indexes Specialized Indexing

Feature View Data Warehouse

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

Decision Support Limited Extensive

Refresh Automatic from Source Periodic Refresh

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 Warehouse Data Mart

Organization-wide Department-specific

Expensive Affordable

Long development time Quick deployment

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 A Source B

Bengaluru Bangalore

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 Component NoSQL 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

Scalability Handles massive datasets

Flexibility Supports diverse data

Performance Fast processing

Availability High uptime

Feature Benefit

Cost Efficiency Commodity hardware

Real-Time Analytics Faster decisions

28. Limitations

Limitation Description

Complex Management Distributed systems are harder to manage

Security Challenges Large attack surface

Data Consistency Issues CAP trade-offs

Skill Requirements Specialized expertise needed

Integration Complexity Multiple technologies involved

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.