ETL Process Project: Data Warehousing and Business Intelligence

This project delves into the ETL (Extract, Transform, Load) process, a critical component of data warehousing. It begins with an introduction to ETL, highlighting its importance in integrating data from various sources into data marts and data warehouses, enabling business intelligence and advanced data insights. The project outlines the three core steps of ETL: extraction, transformation, and loading, detailing how data is identified, retrieved, structured, and loaded into the data warehouse. It categorizes ETL technologies into sophisticated, enabler, simpler, and rudimentary types, discussing their advantages and disadvantages. The project presents an example of ETL technologies, including Informatica, Data Junction, Transact SQL, Java, and COBOL, as well as modern tools like Apache NiFi, Jasper, and Pentaho Data Integration. It emphasizes the significance of defining business requirements before selecting an ETL tool and outlines the key considerations of cost control, revenue generation, security, compliance, and new initiatives in ETL technologies. Furthermore, the project details the ETL plan, encompassing extraction, cleaning, transformation, and loading steps. It specifies target data requirements, including dimension and fact tables, and lists data sources and mappings. Data extraction rules, transformation, and cleansing rules are thoroughly explained, providing a comprehensive understanding of the ETL process.

ETL
Steel L.C. Database
[Type the author name]
[Pick the date]

P a g e | 2
Introduction
ETL process is vital for data warehousing because the data tables are built from stage
areas into data marts, cubes or summary tables. The ETL procedure completes their
data warehousing project. Minus the ETL method, there would not be a way to use data
for BI or advance significant insights from all the data gathered by companies. “The
challenge in data warehouse environments is to integrate, rearrange as well as
consolidate large volumes of data over many systems, thereby providing a new unified
information base for business intelligence (Lane, 2005.).”
Analysis as well as Answers to all the 4 questions
ETL process has three steps: 1. Extract 2. Transform as well as 3. Load. Extraction
consists of valuable data being identified as well as extracted from several sources.
There have been some cases when the useful date was not identified instantly. The
information that was obtained without initial desirability. The second step is extracted
data is transformed into a structured setup may be predefined by DW format. Loading is
when the structured information is loaded into an appropriate table in the DW.
The four categories of ETETL L technologies are Sophisticated, Enabler, Simpler as
well as rudimentary as defined by Solomon (2006). All of these technologies have
advantages as well as disadvantages that should be careful before using them on a DW
project. Example a sophisticated technology will have a high cost, as well as above
average learning curve, it does have good meta-data capabilities. A combination of
enabler, more straightforward as well as necessary techniques can be bought at a low
cost, but DWA’s will need to continue meta-data diligently.
ETL PROCESS 3
Example of ETL technologies where: ETL, Informatica (sophisticated0, data junction
(enabler), Transact SQL simpler, Java, as well as COBOL (rudimentary). (Solomon)
Today, available ETL tools have improved, as well as a nearly free option was available
to incorporate: ApachNifi, Jasper as well as Pentaho Data combination. The commercial
decision includes Confluent, Oracle Data integrator, SAS ETL as well as Alooma. ETL

P a g e | 3
Tools suggest “Begin with defining the business requirements, then consider the
technical aspects, as well as finally, choose the right ETL tool (n.d.),”
ETL stands for extraction,transformation,as well as loading of data as it name suggests
three important word which in itself is a process,i.e. to form data warehousing you need
to extract data from either spreadsheet or other data. It is not only confined to
this,butthe ETL process also plays a vital role in the updating of real-time data
warehousing efforts depending upon the design of the ETL(Berliantara, 2017).
There are three steps of the ETL process which are as follows,i.e.,Extraction,
Transformation,as well as Loading.

P a g e | 4
Extraction
Extraction step of the ETL process connects with source data as well as then collects
necessary data as well as then select the data for further analytical processing. The
main aim of this process is to collect data from different sources.
Transformation
Transformation Process is the second step of the ETL process. The primary objective of
this step is to transform extracted data into the specified standard format by applying
specific rules or functions.
Loading
The next phase of the ETL process is loading that includes transferring the extracted
data or data transformed. These data are transferred or imported to database targeted
or data warehouse.
The most common processes used for these steps are filtering, sorting standardizing,
etc.
The four categories of ETL technologies are as follows:
Cost Control
The primary costs in ETL technologies will constitute hardware, software, support,
consulting, people as well as cloud. The ETL Technologies manufacturer will have to
keep cost as well as benefit analysis in order to explore in the industry(B., M. & A.,
2015).
Revenue Generation
ETL technologies have played a vital role in the driving of revenue generation of an
organization.
Security as well as compliance

P a g e | 5
For attaining superiority you have to take extreme care of the security of data, the ETL
technologies had taken extreme measure in about the security of the data.
New initiatives
ETL technologies must continue to educate as well as re-educate themselves on
technology as well as consumer trends in order to stay in the market.
Examples of ETL technologies are as follows:
1) Informatica – Power Center.
2) IBM – Infosphere Information Server.
3) Oracle Data Integrator.
4) Microsoft – SQL Server Integrated Services (SSIS)
5) Ab Initio.
6) Talend – Talend Open Studio for Data Integration.
7) CloverETL.
8) Pentaho Data Integration
ETL Plan
ETL stands for Extract-Transform-Load. ETL covers a process of how the data is loaded
from the source system to the data warehouse.
Extract
The extraction step covers data extraction from the source system and makes it
accessible for further processing. The primary purpose of the extraction step is to
retrieve all the necessary data from the source system with as few resources as
possible. Extraction steps should be designed to not adversely affect the source system
for performance, response time, or any type of lock.

P a g e | 6
Cleaning
The cleaning step is one of the most important steps to ensure the quality of the data in
the data warehouse. Cleaning ensures that identifiers are unique, null values are
converted to standardized, unavailable/not provided values, phone numbers are
converted to standardized format, address fields are validated against each other
(state/country, city/state, City / ZIP code, City / Street).
Tranform
This includes a set of rules for converting data from source to target. This includes
converting data to an Fit dimension using the same units so that it can be joined later.
This includes joining data from many sources, generating aggregation, surrogate keys,
sorting, and deriving calculated values.
Load
It is essential to properly load with as few resources as possible. To make the load
process more efficient, you can disable constraints and indexes before loading, and
then enable them only after loading. Referential integrity must be maintained by the ETL
tool to ensure consistency.

P a g e | 7
i. Target Data needed in the Data Warehouse
In our data warehouse we have two data marts, one is customer count as well as other
is product sales. In the target data warehouse on the whole for these two data marts we
needed five tables. Those are three dimension tables product dimension, store
dimension as well as time dimension, as well as two fact tables Product Sales fact as
well as Product Category fact
Target Table Columns Data type
DIM_STORE STORE_KEY int
NAME varchar(50)
CITY varchar(50)
STORE varchar(50)
Target Table Columns Data type
DIM_TIME Time_key int
WeekNumber int
Start varchar(50)
End varchar(50)

P a g e | 8
Special Events varchar(50)
Target Table Columns Data type
DIM_PRODUCT Product_key int
ProductID varchar(50)
Category varchar(50)
Description varchar(50)
Target Table Columns Data type
Product Category varchar(50)
Sales float
STORE int
WEEK int
Target Table Columns Data type

P a g e | 9
Demo NAME varchar(50)
CITY varchar(50)
STORE int
WORKWOM varchar(50)
Target Table Columns Data type
Movement STORE varchar(50)
UPC varchar(50)
WEEK int
PROFIT float
SALE varchar(50)
CATEGORY varchar(50)
Target Table Columns Data type
FactProductSales Storekey int
Timekey int

P a g e | 10
Category varchar(50)
Sales varchar(50)
CITY varchar(50)
WORKWOM varchar(50)
Target Table Columns Data type
FactProductCategor
y
PROFIT int
SALE int
Storekey int
Timekey int
Productkey int
ii. Data Sources
Data needed Data Source
Dominick Stores
details
Dominick_Store.xls (created using Dominick’s Data
Manual.pdf)

P a g e | 11
Time details Week_Decode.xls (created using Dominick’s Data
Manual.pdf)
Product (UPC) details UPC<Product Acronym>.csv
Movement details W<Product Acronym>.csv
Demographic DEMO.csv
CCount CCOUNT.csv
iii. Mappings
10.3 Mapping tables for staging as well as data mart loads
Dimension: Store
DW Target
Table
DW
Target
Column
Targ
et
Data
type
Source
System
/ Table
Sourc
e
Colum
n
Transformation/
Business Rule
Error
Handl
ing
Rules
[601-group5-dw-
area].[dbo].
[Store_Dim]
Store_Id int Surrog
ate
key
Store_nu
mber
int Domini
ck’s
Stores
Store

P a g e | 12
Store_Cit
y
varc
har
Domini
ck’s
Stores
City
Store_Zo
ne
int Domini
ck’s
Stores
Zone
Store_Zip
code
varc
har
Domini
ck’s
Stores
Zipcod
e
Dimension: Time
DW Target Table DW
Target
Column
Targ
et
Data
type
Sour
ce
Syste
m/
Table
Source
Colum
n
Transformation/
Business Rule
Error
Handli
ng
Rules
[601-group5-dw-
area].[dbo].
Time_Id int Surrog
ate key

P a g e | 13
[Time_Dim] Year int Week
s
Deco
de
table
Start The start column
is of format
MM/DD/YY. Split it
to get YY
Month int Week
s
Deco
de
table
Start The start column
is of format
MM/DD/YY. Split it
to get MM
Week int Week
s
Deco
de
table
Week# Populated from 1
to 400
Special_E
vent
varc
har
Dimension: Product_Category
DW Target Table DW
Target
Column
Tar
get
Dat
a
type
Source
System
/ Table
Source
Colum
n
Transformatio
n/Business
Rule
Error
Hand
ling
Rule
s

P a g e | 14
[601-group5-dw-
area].[dbo].
[Product_Category_D
im]
Category
_Id
int Surrog
ate key
Category
_Name
varc
har
UPCXX
X.csv
XXX in
the
source
file
name
corres
ponds
to the
respect
ive
categor
y
Dimension: Demographic
DW Target Table DW
Target
Column
Targ
et
Data
type
Sourc
e
Syste
m/
Table
Sourc
e
Colum
n
Transformation/
Business Rule
Error
Handl
ing
Rules
[601-group5-dw-
area].[dbo].
[Demographic_Dim]
Demo_Id int Surro
gate
key

P a g e | 15
Store int Demo.
csv
Store
Poor_Pe
rcent
int Demo.
csv
Povert
y
Poor_Percent=
Poverty*100
Income_
Level
varc
har
Demo.
csv
Incom
e
Income in
source is log of
median income.
We define 3
levels for
income_level
which are
low(<10.3),
med(10.3 to
11.0) as well as
high(>11.0)
Fact: Store_Growth
DW Target Table DW Target
Column
Tar
get
Dat
a
typ
e
Sourc
e
Syste
m/
Table
Sour
ce
Colu
mn
Transformation
/Business Rule
Error
Handl
ing
Rules
[601-group5-dw-
area].[dbo].
[Store_Growth_Fact
Time_Id int Time_
Dim
Time
_Id
Foreign key
corresponding
to primary key

P a g e | 16
]] Time_Id of
Time_Dim
dimension
table
Store_Id int Store
_Dim
Stor
e_Id
Foreign key
corresponding
to primary key
Store_Id of
Store_Dim
dimension
table
Category_Sal
es
int Ccou
nt
Sums up sales
for product
categories
Cat_Coup_R
edeemed
int Ccou
nt
Sums up
coupons
redeemed
across relevant
product
categories
If
coup
ons
redee
med
value
is
missi
ng for
any
produ
ct
categ
ory

P a g e | 17
assu
me
value
to be
0
Total_Sales int Sums up sales
for particular
store for a
particular week
for a particular
category
Fact: Income_Rank
DW Target Table DW
Target
Column
Tar
get
Dat
a
typ
e
Source
System/
Table
Source
Column
Transformatio
n/Business
Rule
Error
Hand
ling
Rule
s
[601-group5-dw-
area].[dbo].
[Income_Rank_Fac
t]
Demo_I
d
int Demograp
hic_Dim
Demo_I
d
Foreign key
corresponding
to primary key
Demo_Id of
Demographic_
Dim dimension
table

P a g e | 18
Store_I
d
int Store_Dim Store_I
d
Foreign key
corresponding
to primary key
Store_Id of
Store_Dim
dimension
table
Poor_P
ercent
int Demograp
hic_Dim
Poor_P
ercent
Rank int Using Rank()
on sorted
values of
Poor_Percent
Fact: Sales
DW Target
Table
DW
Target
Column
Tar
get
Dat
a
typ
e
Source
System/ Table
Sour
ce
Colu
mn
Transformation/
Business Rule
Error
Handl
ing
Rules
[601-group5-
dw-area].[dbo].
[Sales_Fact]
Time_Id int Time_Dim Time
_Id
Foreign key
corresponding
to primary key
Time_Id of
Time_Dim

P a g e | 19
dimension table
Store_I
d
int Store_Dim Store
_Id
Foreign key
corresponding
to primary key
Store_Id of
Store_Dim
dimension table
Categor
y_Id
int Product_Categ
ory_Dim
Foreign key
corresponding
to primary key
Category_Id of
Product_Catego
ry_Dim
dimension table
Sale_A
mt
int Movement_Fin
al
Sums up sale
for particular
store for a
particular week
for a particular
bathroom
product
category
iv. Data Extraction Rules
Data has been extracted from the provided CSV files as well as data manual of DFF.
The extraction rules used in the report are as follows.

P a g e | 20
 The data from the sources are in the Comma Separated Value (.csv) formats.
 The data for Week as well as Store details was extracted from the data manual
for Dominick
 Finer Foods. Comma Separated Value (.csv) files were created for such tabular
data.
 The data from the source files has been loaded into the data staging area
<603><Group 4>Staging.
 We truncated data with invalid store numbers from the CCount table
 Additionally, week number will be used to analyze chronological trends, so we
removed the junk rows with missing week numbers during extraction process
 After extraction with above rules from ccount.csv, CCount table looks as follows:
 The datatype of store IDs, Week as well as product category coupon redeemed
values have been changed from varchar to int during extraction

P a g e | 21
 The Demo Table is being extracted from demo.csv. The rows with non-Int Store
IDs have been cleaned during extraction process
 During extraction of all data, only the columns required for building data marts
have been imported dropping other columns in the source files
v. Data transformation as well as cleansing rules;
The Transformation as well as data cleansing rules used are as follows:
CCount data transformation:
 The columns such as Date etc. which were not required were removed
 The junk values that were present in miscellaneous forms were removed to
finally create CCountFinal staging table
Store data transformation:
 The data types were converted while loading the data. This was required to avoid
any mismatch in the data types.
 The data types were converted to Int to suit our requirements
 The junk values were removed from the table to match the store numbers
present in the data manual to finally create StoreDataFinal staging table
Week data transformation:
 The week values that were out of the range as per the data manual i.e. the
values that were not in the range of 1 to 400 were removed
 We used Week’s Decode Table for this transformation. For this transformation,
we converted start date for each week from varchar to datetime datatype as well

P a g e | 22
as then derived Month as well as Year value from the data using following SSIS
expressions.
 YEAR = YEAR([Datatype convert for Week].Start)
 MONTH = MONTH([Datatype convert for Week].Start)
Finally, WeekFinal staging table was created with above transformations
Demographic data transformation:
 Data types of the values were changed while creating the DemoFinal table
 The junk values were removed using ‘ignore failure’ in ole db destination SSIS
toolbox to finally create DemoFinal staging table
Movement data transformation:
 The records where the value for ‘OK’ as well as ‘QTY’ field was 0 were removed
 The data types were converted while loading the data into the table
 The sales amount for each row in all three Movement tables in staging area has
been calculated using following transformation.
Sales = (Price*Move/Qty)
 The three bathroom Movement tables were merged using sort as well as merge
SSIS toolboxes to form MovementFinal staging table
vi. Plan for aggregate tables
No need for aggregation as all the business questions were answered without
aggregation. The data granularity was on week basis. The questions that required
aggregation were not listed by the professor.

P a g e | 23
Aggregation if required will be performed in the report if the Management needs data
sales in a monthly or yearly basis, we can perform that aggregation in the SSRS
reporting tool.
vii. Write out the organization of data staging area;
#Need to do
viii. Write procedures for all data extractions as well as loadings;
ix. Creating as well as extracting CCOUNT table:
Cleaning CCOUNT table:

P a g e | 24

P a g e | 25
Creating as well as extracting WGRO (Grooming product movement):
Cleaning WGRO:

P a g e | 26
Data conversion in WGRO:

P a g e | 27
ix. Write the ETL for dimension tables; as well as
a) ETL for DIMENSION tables
DIM_STORE
Source data for the DIM_STORE is Dominick’s Stores data table. This data was
cleaned as well as ETL was performed on it as shown in the below diagram. Few

P a g e | 28
columns had no zone value in Dominick Stores table as well as then data conversion
was performed on it from Varchar to int. Similarly Store_Number was changed to int.
This transformed data was stored in DIM_STORE table.
Creating Dim_Store:

P a g e | 29
DIM_TIME
Weeks Decode table from Dominick’s manual acts as the source table for the
DIM_TIME table. Initially the data type for Week is Varchar as well as it is converted to
Int. Thereafter the Month as well as Year attributes in DIM_TIME was derived from the
date as per the Transformation described previously. This final transformed table is
stored in DIM_TIME.
Creating dimTime:

P a g e | 30

P a g e | 31
DIM_PRODUCT
The product dimension has the details of the grooming product taken from the
movement file WGRO.
Creating dimProduct:

P a g e | 32

P a g e | 33
x. Write the ETL for fact tables.
FactProductSales
Write steps of how we filled data as well as which tables
(loading ticks)

P a g e | 34
FactProductSales data flow
FactCategorySales
Write steps of how we filled data as well as which tables
(ticks)

P a g e | 35
FactCategorySales data flow
SQL statements used for the ETL operations

P a g e | 36
 Look at the data granularity needed at the independent data mart level.
Include these in the report.
Data Granularity refers to the level of detail that is included in the data marts based on
the needs of the users. If the users rarely go deeper into details, the summarised data is
preferred like monthly or yearly trends as well as when the users want to dig deeper into
the details, then we need to keep very detailed level of data like daily or weekly trends.
If we look at the time dimension that we have used, we can see that the level of detail
that we have used in the time dimension is in the order of week, month, year as well as

P a g e | 37
special events. So, we have provided different levels of details so that the user can
access any level of detail in the data as required by him.
Software development process involves several sub-processes that have to be carefully
done to produce working software. Due to the complexity of the processes, many risks
are experienced leading to low success rates of all software developed. Statistics have
revealed the sad truth that majority of software development projects end in failure. A
software project is a success if it is delivered “on the budget, on time, as well as to user
specification.” Precisely, 25% of all software development projects end up canceled
while 80% of all software projects run over budget. The high failure rate, therefore, calls
for scientific proofs on how to manage the high levels of risks in software development
process. Research has shown that most of the causes of project failures are
management related. Therefore, several methods to come up with a managerial action
to solve the problem have been devised by many researchers. Consequently, the
concept of software development risk management has been brought into the spotlight.
The process of identifying risk sources as well as taking appropriate actions to manage
them as been assumed by many as the sure way to minimize the chances of software
failure. Knowledge of the most common risks forms the basis for formulating a
comprehensive as well as efficient software risk management plan as well as
consequently reduced software project failure.
Risk management forms an important part of the main software project management
together with the phases of software development lifecycle. There are numerous risks
facing software projects throughout the development lifecycle. By definition, risk refers
to any potential event that can negatively affect a project’s success rate. Software
project risk, on the other hand, refers to the product of uncertainty linked to the project
risk factor as well as the extent of a potential loss caused by the project failure. Project
Risk Management Institute has devised specific guidelines to help a project manager
deal with eminent risks during the development process. Risk management planning,
risk identification, risk analysis, risk response planning, as well as risk monitoring as
well as tracking are the most important phases of risk management. Every particular
guideline is based on inputs, tools, techniques as well as outputs derived from every

P a g e | 38
step. Software development risk management is an important activity in the software
project as well as mostly involves two distinct stages; risk assessment as well as risk
control. The first stage involves identifying, analyzing as well as prioritizing the risk
identified while the latter later involves the ways adopted by the project manager to
mitigate the risk.
From the definition of software project risk, the prevailing idea is the risk factor. Risk
factor refers to a condition that can lead to a dire threat to the success of a software
project. Therefore, understanding of risk factor forms the basis of understanding of a
more advanced way of managing software development risks. This paper is aimed at
identifying the most common risk factors experienced by project managers as well as
considered most destructive, as well as the best management model that can be used
to decrease risk effects as well as increase project success rate. The paper is based on
theoretical analysis of similar research that has already been conducted combined with
a qualitative research based on data collected from professional software development
project managers. The paper is organized into literature review where analysis of similar
studies, methods where the qualitative research is comprehensively described, results
where the research of the research are presented, a discussion where the results are
described, as well as conclusions.
Extensive research on software development risk management has been conducted by
researchers from all wake of life. However, many of the researchers revolved around
developing models to manage risks while little research has been comprehensively
done on identifying risk forms. Most of such research did not involve data collected from
the field. Additionally, the data used did not have proper statistical analysis. Over the
years, software development has undergone a dynamic metamorphism rendering
previous risks forms obsolete. This is because some of the latest technological
advancement in software development have reduced the relevance of some risk items.
According to advocates of software project risk management, project managers are
supposed to identify as well as control risk factors when they occur to increase the
chance of project failure. The meaning of this is that risk assessment process must start
with identifying the risk factors in question to be able to control the project risk factors.

P a g e | 39
Literature has so far outlined several lists of risk factors, but petite of the research has
established the extent of every risk factor as well as the effect it has on the project
success.
In order to solve our business questions, the granularity of our data marts
chosen is at store, week as well as product category level.
Once loading of each table is done, remove all temp tables from the staging area.
Make sure to write the list in the report.
Conclusion
Of the many processes that have been developed over years to on the need to come up
with a more practical risk management, an iterative approach has been rendered the
most efficient approach. The iterative approach is highly favored because it has distinct
phases (J. Keizer, 2002). These phases are: context analysis, risk identification, risk
analysis, risk evaluation, risk treatment, risk monitoring, as well as risk communication
as well as consulting. An iterative approach is favored because it is dynamic as well as
thus can be used in managing risks in any size of the software project.

P a g e | 40
Reference (They are less because of the practical work)
ETL Enterprise Data Integration. (n.d.). ETL Tools. Retrieved from
https://www.etltools.net/
Lane, P. (2005). Data warehousing guide 10g release 2 (10,2) [PDF]. Retrieved from
https://does.oracle.com/ed/cd/B19306_01/server.102/b14223.pdf
Solomon, M.D. (2005). Ensuring a successful data warehouse initiative. Information
Systems
Management, 22(1), 26-36.
TatvaSoft. (2015, July 15). ETL process (extract transform load). Retrieved from
http://www.tatvasoft.com/blog/etl-process-extract-transform-load/
B., S., M., T., & A., A. (2015).Automated ETL Testing on the Data Quality of a Data
Warehouse. International Journal Of Computer Applications, 131(16), 9-16. doi:
10.5120/ijca2015907590
Berliantara, A. (2017). SCHEDULING OPTIMIZATION FOR EXTRACT, TRANSFORM,
LOAD (ETL) PROCESS ON DATA WAREHOUSE USING ROUND ROBIN METHOD
(CASE STUDY: UNIVERSITY of XYZ). Journal Of Information Technology And
Computer Science, 2(2). doi: 10.25126/jitecs.20172232