PRACTICAL FILE

SESSION: 2025-26

Data Warehousing and Data Mining

Lab
(CIE - 425P)
IV Year, 7th Sem

Submitted to: Submitted by:

Name: Ms. Shrishti Priya Chaturvedi Name: Vaibhav Pratap Singh
Designation: Assistant Professor Enrollment No.: 03618002722

Department of Computer Science Engineering

Delhi Technical Campus, Greater Noida
 INDEX

[Link] EXPERIMENTS DATE OF DATE OF SIGN

EXPERIMENT SUBMISSION

1 Study of ETL process and its tools.

2 Introduction to WEKA tool.

3 Implementation of Visualization
technique on ARFF files using
WEKA.

4 Perform Data Similarity Measure

(Euclidean, Manhattan Distance).

5 Program of Data warehouse

cleansing to input names from users
(inconsistent) and format them.

6 Program of Data warehouse

cleansing to remove redundancy in
data.

7 Implementation of Classification
technique on ARFF files using
WEKA.

8 Implementation of Clustering
technique on ARFF files using
WEKA.

9 Data Cleaning in Customer

Database.

10 Product Recommendation Using

Association Rules (Market Basket
Analysis).
 Experiment 1
AIM: Study of ETL process and its tools.
Introduction:
ETL stands for Extract, Transform, and Load. It is a data integration process that:

· Extracts data from multiple sources.

· Transforms the data into a suitable format.
· Loads the transformed data into a data warehouse.
ETL forms the backbone of modern data warehousing and business intelligence systems.
Theory / Explanation:
1. Extract Phase:
· Data is collected from various sources such as databases, flat files, and APIs.
· Raw data from structured, semi-structured, and unstructured formats is gathered.
2. Transform Phase:
· Data is cleaned, validated, and formatted for consistency.
· Common transformation tasks include:
o Removing duplicates
o Standardizing formats
o Applying business rules
o Aggregation and summarization
· Ensures data quality and uniformity.
3. Load Phase:
· Transformed data is loaded into a data warehouse or target database.
· Types of loading:
o Full Load: Load everything at once.
o Incremental Load: Load only new/updated data.

Importance of ETL:
· Integrates data from multiple sources
· Improves data quality and consistency
· Prepares data for analysis and reporting
· Essential for BI systems
Popular ETL Tools:
· Talend Open Studio
· Apache NiFi
· Pentaho Data Integration (Kettle)
· Informatica PowerCenter
· Microsoft SSIS
· AWS Glue
· Google Cloud Dataflow
 Experiment 2
AIM: Introduction to WEKA tool.

Introduction:
• WEKA is a popular data mining and machine learning tool developed at the Univer
sity of Waikato, New Zealand.
• GUI-based and easy to use.
• Supports .arff, .csv, .xrff formats.
• Useful for learning and small-scale ML applications.
Features:
1. Preprocess:
• Clean, transform, and filter data.
2. Classify:
• Apply supervised algorithms such as Decision Tree, Naive Bayes.
3. Cluster:
• Apply unsupervised algorithms like K-Means, EM.
4. Associate:
• Generate association rules using Apriori.
5. Visualize:
• Scatter plots, histograms, attribute graphs.
Importance of WEKA:

· Helps understand ML concepts

· Provides built-in datasets (Iris, Weather)

· Saves time by avoiding coding

· Useful for classification, clustering, association rule mining

Procedure:
1. Download WEKA.
2. Install and open GUI.
3. Open Explorer.
4. Load dataset (eg. [Link]).
5. Explore tabs:
a. Preprocess: View and clean data
b. Classify: Apply classification algorithm like J48, Naive Bayes
c. Cluster: Apply clustering like K-means
d. Associate: Apply apriori for market basket analysis
e. Visualize: Apply scatter plots and attribute distribution
 Experiment 3
Aim: Implementation of visualization techniques on ARFF files using WEKA.
Introduction:
· Visualization is the process of graphically representing data to understand patterns,
trends, and relationships among attributes.
· WEKA provides an easy-to-use Visualize tab for scatter plots, histograms, and multi-
attribute analysis.
· Helps in data exploration, anomaly detection, and understanding distributions be-
fore applying data mining algorithms.
Procedure:
1. Download and Install WEKA from [Link]
2. Open WEKA GUI ‣ Click on Explorer.
3. Load an ARFF dataset (e.g., [Link]) using Preprocess → Open file.
4. Open Visualize tab.
5. Explore scatter plots for all numeric attributes.
6. Observe:
· Relationships between attributes
· Clusters of similar instances
· Outliers of anomalies
7. Change X-axis and Y-axis attributes for custom plots.
8. Save plots if needed.
Observations:
· Scatter plot shows relationship between Iris attributes
· Petal Length vs Petal Width shows clear class separation.
· Histograms reveal attribute distribution.
· Outliers shall now be visible.
Conclusion:
• WEKA’s visualization makes data exploration easier.
• Helps understand data structure before applying ML algorithms.
• Useful for feature selection and preprocessing.
 Experiment 4
Aim: Perform Data Similarity Measures (Euclidean, Manhattan Distance).
Introduction:
• Similarity measure shows how similar two data points are.
• Euclidean Distance: Straight-line distance.
• Manhattan Distance: Sum of absolute differences.
Formulas:
1. Euclidean Distance:
d = Σ (xi − yi) 2d
2. Manhattan Distance:
d = Σ |xi − yi|

Source Code:

import pandas as pd
import numpy as np
from [Link] import distance
# Step 1: Create or load a sample dataset
data = {
'Feature1': [2, 4, 5, 9],
'Feature2': [3, 6, 8, 12]
}
df = [Link](data)
print("Dataset:")
print(df)
# Step 2: Select two data points (rows) to measure similarity
point1 = [Link][0] # (2, 3)
point2 = [Link][1] # (4, 6)
# Step 3: Calculate Euclidean Distance
euclidean_dist = [Link](point1, point2)
# Step 4: Calculate Manhattan Distance
manhattan_dist = [Link](point1, point2)
# Step 5: Display results
print("\nSelected Points:")
print("Point 1:", [Link])
print("Point 2:", [Link])

print("\nSimilarity Measures:")
print("Euclidean Distance between points:", round(euclidean_dist, 3))
print("Manhattan Distance between points:", round(manhattan_dist, 3))
Algorithm:
1. Define data points.
2. Compute Euclidean distance.
3. Compute Manhattan distance.
4. Compare and interpret similarity.

Output:
 Experiment 5
Aim: Program for data warehouse cleansing to input inconsistent names and format them.
Introduction:
• Names may contain extra spaces, inconsistent case, or misspellings.
• Data cleansing ensures consistency and accuracy.
• Common cleansing tasks include:

• Remove extra spaces

• Convert to Title Case
• Remove duplicates
Algorithm:

1. Accept the number of names to be input by the user.

2. Take input of each name (may be inconsistent).

3. Strip extra spaces and convert to Title Case.

4. Store cleaned names in a new list.

5. Display the formatted names.

Source Code:

while True:
try:
n_input = input("Enter number of names: ")
n = int(n_input)
break
except ValueError:
print("Invalid input. Please enter an integer for the number of names.")

names = []
for i in range(n):
name = input(f"Enter name {i+1}: ")
[Link](name)

cleaned_names = []
for name in names:
name = [Link]().title()
cleaned_names.append(name)

print("\nFormatted (Cleaned) Names:")

for name in cleaned_names:
print(name)

Output:

Observation:
• Extra spaces removed.
• Capitalization standardized (Title Case).
• Names ready to be stored in the data warehouse.
Conclusion:
• Data cleansing is an essential step in data warehousing.
• Ensures consistency, accuracy, and quality of stored data.
• Cleaned and formatted names improve data usability for analysis, reporting, and BI tasks.
 Experiment 6
Aim: Program for data warehouse cleansing to remove redundancy (duplicates).
Introduction:
• Data redundancy occurs when the same information is stored more than once in a dataset.
• Redundant data increases storage cost, inconsistency, and may lead to inaccurate analysis.
• Data cleansing removes these duplicates to maintain data integrity and quality.
• Pandas in Python provides easy-to-use functions like drop_duplicates() for this task.
Algorithm:
1. Load dataset.
2. Identify duplicate rows.
3. Remove duplicates.
4. Display cleaned data.
Source Code:
import pandas as pd

data = [Link]({
'CustomerID': [101, 102, 103, 101, 104, 102],
'Name': ['Vaibhav', 'Suryansh', 'Nitesh','Vaibhav', 'Sanskriti','Suryansh'],
'City': ['Hogwarts', 'Durmstrang', 'Beauxbatons','Hogwarts', 'Ilvermorny ', 'Durmstrang',]
})

print("Original Dataset:\n")
print(data)
cleaned_data = data.drop_duplicates()

print("\nCleaned Dataset:\n")
print(cleaned_data)

Output:
Conclusion:
• Redundant data was successfully removed using Python’s drop_duplicates() function.
• The cleaned dataset ensures accuracy, consistency, and efficiency in data warehousing.
• Data cleansing improves the quality of analytical results and optimizes storage.
 Experiment 7
Aim: Implementation of Classification technique on ARFF files using WEKA.
Introduction:
1. Classification assigns data to predefined classes.
2. WEKA supports:
• J48 (Decision Tree)
• Naive Bayes
• Random Forest
• IBk (k-Nearest Neighbor)
3. Performance is measured using accuracy, confusion matrix, precision, recall.
Procedure:
1. Open WEKA GUI → Explorer.
2. Click on Open File, and load an ARFF file (e.g., [Link], [Link]).
3. Go to the Classify tab.
4. Click on Choose and select any classification algorithm.
5. Under Test options, select:
• Use training set or
• Cross-validation (commonly 10-fold cross-validation).
6. Click on Start to run the algorithm.
7. Observe the results in the Classifier output panel:
• Accuracy (% of correctly classified instances)
• Confusion Matrix
• Precision, Recall, F-Measure
8. Optionally, visualize the decision tree (right-click → Visualize tree)

Output:
Observations:
• J48 achieved around 96% accuracy on Iris dataset.
• Some misclassification between Versicolor and Virginica.
• Decision tree explains classification rules clearly.
Conclusion:
• Classification using WEKA helps in predicting categorical outcomes from historical data.
• The J48 decision tree algorithm performed efficiently on the Iris dataset.
• WEKA provides easy visualization and evaluation of classification models.
• This technique is widely used in predictive analytics and decision-making systems.
 Experiment 8
Aim: Implementation of Clustering technique on ARFF files using WEKA.
Introduction:
· Clustering groups similar data without predefined labels.
· WEKA supports:
• Simple K-Means
• EM (Expectation Maximization)
· Hierarchical Clustering
Applications:
• Customer segmentation
• Document grouping
• Market analysis
• Image segmentation
Procedure:
1. Open WEKA GUI → Explorer.
2. Click on Open File, and load an ARFF dataset (e.g., [Link]).
3. Select the Cluster tab.
4. Click on Choose and select a clustering algorithm (e.g., SimpleKMeans).
5. Click on the algorithm name to set parameters, e.g.:
• Number of clusters (default = 2 or 3)
6. Click on Start to run clustering.
7. Observe the results in the Cluster output area:
• Cluster centroids (mean values)
• Number of instances in each cluster
8. Click Visualize cluster assignments to view data points in a scatter plot.

Output:
Observations:
• K-Means algorithm successfully divided data into 3 clusters, each roughly matching a
species.
• Cluster centroids show average values of numeric attributes in each group.
• Visualization clearly shows natural grouping of similar data points.

Conclusion:
• Clustering groups similar data points without predefined labels.
• WEKA's SimpleKMeans effectively discovered natural groupings in the Iris dataset.
• Clustering is useful for pattern discovery, segmentation, and exploratory data analysis.
• WEKA provides an intuitive interface to experiment with various clustering algorithms.
 Experiment 9
Aim: Data Cleaning in Customer Database.
Introduction:

1. Data cleaning is an essential step before data is stored in a data warehouse or ana-
lyzed.

2. It ensures accuracy, consistency, and reliability by fixing missing, incorrect, or dupli-

cate data.

3. Common cleaning tasks include:

· Handling missing values

· Removing duplicates

· Correcting inconsistent formatting

· Standardizing text and numerical values

Algorithm:

1. Create or load a sample customer dataset.

2. Identify missing values and replace them appropriately.

3. Remove duplicate records.

4. Standardize inconsistent formats (e.g., name, city).

5. Display cleaned data.

Source Code:
import pandas as pd
import numpy as np

data = [Link]({
'CustomerID': [101, 102, 103, 101, 104, 102],
'Name': ['Vaibhav', 'Suryansh', 'Nitesh','Vaibhav', 'Sanskriti','Suryansh'],
'School': ['Hogwarts', 'Durmstrang', 'Beauxbatons','Hogwarts', 'Ilvermorny ', 'Durmstrang',],
'Age': [25, [Link], 30, 28, [Link]]
})
print("Original Dataset:\n")
print(data)

data = data.drop_duplicates()
data['City'] = data['City'].fillna('Unknown')
data['Age'] = data['Age'].fillna(data['Age'].mean())
data['Name'] = data['Name'].[Link]().[Link]()
data['City'] = data['City'].[Link]().[Link]()

print("\nCleaned Dataset:\n")
print(data)

Output:

Observations:
• Duplicate entries removed
• Missing city → "Unknown"
• Missing age → mean value
• All names and cities formatted in Title Case for uniformity.
Conclusion:
• The dataset was successfully cleaned by removing redundancy, filling missing values, and
standardizing text.
• Clean data ensures accuracy, reliability, and better analytics in a data warehouse.
• Data cleaning is an essential preprocessing step for all data-driven systems.
 Experiment 10
Aim: Product Recommendation Using Association Rules (Market Basket Analysis).
Introduction:
• Association Rule Mining discovers relationships between items in large transactional
datasets.
• It is used in Market Basket Analysis to find patterns like:
If a customer buys Bread, they are likely to buy Butter.
• WEKA provides the Apriori algorithm to automatically find frequent itemsets and associa-
tion rules.
Key Concepts:
• Support – frequency of itemset
• Confidence – probability that rule holds
• Lift – strength of association versus random chance
Procedure:
1. Open WEKA GUI → Explorer.
2. Click on Open File and load a transaction ARFF file (e.g., [Link]).
3. Go to the Associate tab.
4. Click on Choose → Apriori (under association algorithms).
5. Set parameters (optional):
• Lower minimum support (e.g., 0.1)
• Minimum confidence (e.g., 0.9)
6. Click on Start to run the algorithm.
7. Observe the association rules generated in the output window.