Open Ended Lab Task
Data Warehousing and Data Mining (Lab)
Name: Syed Hamza Jaffar Zaidi
Reg id: 52031
Instructor: Sir Sartaj Ahmed Malik
Question:
Demonstrate ETL (Extract, Transform, and Load) process step wise using SQL Server Integration
Services for the following scenario.
Methodology:
A company named Shahzain Market maintains customer data from small outlets in Excel files and
sends this data to its main branch as total sales per month. This data is necessary at the
headquarters (main branch) to track the performance of each outlet. We will collect customer
product purchase sales data from small outlets (extract) in Excel files. Since the main branch of the
company is located in the USA, we need to transform the currency into US dollars. Finally, we will
load this transformed data into a database/data warehouse.
Code:
CREATE DATABASE ShahzainMarketDB;
CREATE TABLE CustomerSales (
CustomerNo INT PRIMARY KEY NOT NULL,
CustomerName VARCHAR(255),
OrderID INT,
Product VARCHAR(255),
Quantity INT,
OrderDate INT,
Amount_PKR DECIMAL(10, 2)
);
GO
INSERT INTO CustomerSales (CustomerNo, CustomerName, OrderID, Product, Quantity,
OrderDate, Amount_PKR)
VALUES
(1501, 'John', 266868, 'Chocolate Chip', 292, 43862, 584),
(1502, 'Hussain', 140794, 'Chocolate Chip', 974, 43862, 1948),
(1503, 'Adil', 203604, 'Fortune Cookie', 921, 43891, 460.5),
(1504, 'Hassan', 830805, 'Fortune Cookie', 2518, 43983, 1259),
(1505, 'Daniyal', 138739, 'Fortune Cookie', 1899, 43983, 949.5),
(1506, 'Usman', 830819, 'Fortune Cookie', 1545, 43983, 772.5),
Data Warehousing and Data Mining (Lab)
Name: Syed Hamza Jaffar Zaidi
Reg id: 52031
Instructor: Sir Sartaj Ahmed Malik
Question:
Demonstrate ETL (Extract, Transform, and Load) process step wise using SQL Server Integration
Services for the following scenario.
Methodology:
A company named Shahzain Market maintains customer data from small outlets in Excel files and
sends this data to its main branch as total sales per month. This data is necessary at the
headquarters (main branch) to track the performance of each outlet. We will collect customer
product purchase sales data from small outlets (extract) in Excel files. Since the main branch of the
company is located in the USA, we need to transform the currency into US dollars. Finally, we will
load this transformed data into a database/data warehouse.
Code:
CREATE DATABASE ShahzainMarketDB;
CREATE TABLE CustomerSales (
CustomerNo INT PRIMARY KEY NOT NULL,
CustomerName VARCHAR(255),
OrderID INT,
Product VARCHAR(255),
Quantity INT,
OrderDate INT,
Amount_PKR DECIMAL(10, 2)
);
GO
INSERT INTO CustomerSales (CustomerNo, CustomerName, OrderID, Product, Quantity,
OrderDate, Amount_PKR)
VALUES
(1501, 'John', 266868, 'Chocolate Chip', 292, 43862, 584),
(1502, 'Hussain', 140794, 'Chocolate Chip', 974, 43862, 1948),
(1503, 'Adil', 203604, 'Fortune Cookie', 921, 43891, 460.5),
(1504, 'Hassan', 830805, 'Fortune Cookie', 2518, 43983, 1259),
(1505, 'Daniyal', 138739, 'Fortune Cookie', 1899, 43983, 949.5),
(1506, 'Usman', 830819, 'Fortune Cookie', 1545, 43983, 772.5),
