Data Analytics and Business Analytics: Data Warehouse and ETL Report

This report examines the crucial Extract, Transform, and Load (ETL) process within data warehousing, focusing on its role in supporting business intelligence. It details the operation of data warehouses, emphasizing their ability to collect and manage data from various sources to provide meaningful business insights. The report breaks down the ETL process into its three primary phases: extraction, transformation, and loading, discussing the specific steps and challenges involved in each. It highlights the significance of ETL in constructing data warehouse solutions and explores the tools provided by Microsoft SQL Server Integration Services (SSIS), such as the Data Flow Engine, Scripting Environment, and Data Profiler, relating them to the phases within ETL. The report also provides examples from case studies to reinforce the discussions, offering a practical understanding of ETL processes.

Running head: DATA ANALYTICS AND BUSINESS ANALYTICS
DATA ANALYTICS AND BUSINESS ANALYTICS
Name of the Student
Name of the university
Author Note:

1DATA ANALYTICS AND BUSINESS ANALYTICS
Table of Contents
Introduction..........................................................................................................................2
Discussion............................................................................................................................3
Operation of Data Warehouse in support of Business Intelligence.................................3
ETL for construction of Data Warehouse........................................................................7
Extraction of Data........................................................................................................8
Transformation of Data................................................................................................8
Loading Data...............................................................................................................9
Tools provided by SSIS.............................................................................................12
Conclusion.........................................................................................................................15
References..........................................................................................................................17

2DATA ANALYTICS AND BUSINESS ANALYTICS
Introduction
Data warehousing is a well-known technique which is used for collecting and proper
management of data. It is done with the help of sources to provide some meaningful business
(Gill and Singh, 2015). It is considered to be part of a technological component that allows the
use of data. It is defined as the storage for a huge amount of information by business. The
method of changing data into available information makes it available to a large number of users.
ETL stands for Extract, transform and load. The data staging layer mainly hosts the ETL process
for extract, integrate and cleaning of data from different operational sources which is used for
feeding the data warehouse layer (Dembczynski , 2015). In this particular layer, ETL process
provides feedback to the recoiled data layer in terms of single and top quality of sources which
are provided. ETL process as a whole can be easily stated as reconciliation. ETL takes place at
the time when the data warehouse comes up with population. After that, it checks or analyzes the
fact that the data warehouse is updated on a regular basis or interval. ETL mainly comprises of
three phases that are extraction, transformation and lastly loading.
In the coming pages of the report, a research has been done regarding the concepts and
technology of ETL data. After that, a description has been provided regarding the operation of a
data warehouse which is used for support of Business Intelligence. ETL is considered to be a
well-known process for construction of Data warehouse solution. It mainly comprises of three
important phases like extraction, transformation and lastly loading of data. Microsoft SSIS
comes up with a large number of tools like data flow engine, scripting environment and lastly
Data Profiler.

3DATA ANALYTICS AND BUSINESS ANALYTICS
Discussion
Operation of Data Warehouse in support of Business Intelligence
The main purpose which is related to the business intelligence in the sector of business is
to incorporate corporate executive, manager of business and other workers related to operational
to make better and more decision of business in an informed manner (Gill and Singh, 2014). It
can be stated here that most of the organization tend to include the business intelligence concept
to cut down the cost, identify business opportunity which does not pertain in the industry and
directly spot inefficient process of business which may be hampering the overall working of the
organization.
The business intelligence can be referred to a technology, tool, application and practice
which can be used to integrate, collect, present and analyze the raw data of the organization
which would be useful in the aspect of creating an actionable and insight of the information of
the business (Leitch et al. 2016). The process of business intelligence can be considered as a
discipline as well as a process of technology-driven mechanism which is made up of several
activities which are interrelated.
 Data mining
 An analytical process which is done online
 Reporting
 Querying.
Benefits of warehouse
The above discussion majorly gives a clear idea of the concept of business intelligence.
The potential benefit which is related to the concept of business intelligence are stated below:

4DATA ANALYTICS AND BUSINESS ANALYTICS
 Optimization of the processes of internal business
 Driving revenue which is new
 Improving and accelerating the aspect of decision making.
 Operational efficiency is increased.
 Gaining competitive advantage in the field of operation over business rivals.
 Identification of the trend of business which is currently being introduced.
Implementation details
The tool of business intelligence can be considered to be essential relating to the data-
driven aspect of the decision support system (DSS). The concept of business intelligence is
sometimes considered interchangeable with the report, briefing books, query tools and
information system which is executive (Ferdynus and Huiart, 2016). These tools can be utilized
in a different manner according to the need of the organization, for example, the employee of the
business can directly use the tool to analyze the data themselves rather than waiting for the
information technology to run complex reports on the data. The access of the information helps
the user to back up the aspect of a business decision with a complex and hard number rather than
only gut anecdotes and feelings.
Software tools in the optimization of business intelligence.
The business intelligence software system can directly provide current, historical and
predictive view relating to the operation of the business. These aspects which are taken into
consideration are done using the data which has been gathered from the data warehouse or a data
mart and working occasionally from the data which are operational (Andersen, Thomsen and
Torp, 2018). The software elements in this context can directly support reporting, interactive

5DATA ANALYTICS AND BUSINESS ANALYTICS
slice and slice analyses, statistical mining of data and visualization. The application directly
tackles financial, production, sales and many other sources which are related to business data for
the purpose which directly include management of business performance (Kholod, Efimova and
Kulikov, 2016). The concept of benchmarking can be implemented in a concept which usually
gets the data of other organization which are in the same industry.
Example of business intelligence software solution
Some of the example which is related to the business intelligence software are stated
below:
 Sisense
 Looker
 Demo
 Cyfe
 Kissmatrices
 Visualr
 Indicative
 StarBI
 Centro
 ReportPlus
 Hevo data
Potential business intelligence problem
User resistance: The concept of implementation can be done using the aspect of dogged
relating to cultural challenges.

6DATA ANALYTICS AND BUSINESS ANALYTICS
Poor quality and irrelevant data: This aspect can be included in a manner of gaining the
appropriate standard of the data. Getting the idea of the data in an order which is working well
before the action and extracting on the insight (Mukherjee and Kar, 2017). The quality of the
data which is generated can be considered to one of the most important factors in the concept
which directly reflects the internal working of the organization and how the working framework
of the organization would be implemented. In most of the cases, security of the data can be one
of the most important factors.
Tools of BI: The core concept which is related to the BI can be considered to be reporting
not only in the aspect of management of the process. It can be stated here not to get confused
with the aspect of business intelligence with the concept of business analytics (Gill and Singh,
2014). There can be different tools which can be implemented in the concept and it directly
depends upon the need of the organization and the effect it would be imposing on the internal as
well as the external working of the organization.
The organization does not understand their business operations: The concept can be
related to the understanding aspect which is related to all the activity which makes up a particular
business process before starting the aspect of business intelligence project.
Latest trends in the business intelligence concept relating to warehouse
This section majorly deals with all the aspects which are related to the concept and the
latest trend in the business intelligence in the sector:
 Integration of content and data: Currently most of the organization are starting to see that
the content and the data should not be considered as a separate aspect relating to the
management of the information (Gill and Singh, 2014). On the other hand, apart from

7DATA ANALYTICS AND BUSINESS ANALYTICS
this, it can be stated that the concept must be integrated with the enterprise approach. The
management of enterprise information can directly bring business intelligence and
content enterprise management concept together.
 Business intelligence operation: currently it can be stated that most of the organization
are moving towards the concept of operational business intelligence which is currently
being uncontested and served by the vendors (Mukherjee and Kar, 2017). Traditionally it
can be stated that business intelligent vendors are directly targeting only the topmost
pyramid but it the paradigm is moving the focus towards the business intelligence to the
bottom of the pyramid with a focus of self-service business intelligence (SSBI)
 Conversational and embedded business intelligence: the concept in recent times cannot
be considered as an ordinary report which is related to the BI software but it can be
directly be integrated with the concept of advanced integrated analysis and reporting
(Rahman, Riyadi and Prasetyo, 2015).
ETL for construction of Data Warehouse
ETL is considered to be a complex process which consumes or takes up a part of Data
Warehouse development efforts. Apart from this, it requires skills and knowledge of business
analyst and application developer (Narra, Sahama and Stapleton, 2015). It is not considered to be
a one-time event instead of that it is a periodic event which is available on the monthly and daily
basis. The operation of ETL should be carried out on the relational database server and data
warehouse database. It builds up a logical and physical separation between source from system
and Data Warehouse. Along with this, it minimizes the effect of intense ETL activity which is
present on the source and databases of the warehouse. In the extraction phase, the required data
is extracted from various sources. The integration of data warehouse across the various

8DATA ANALYTICS AND BUSINESS ANALYTICS
enterprises is considered to be a bit challenging to get the warehouses to the next state which is
very much usable (Vaisman and Zimányi, 2014). Generally, data is extracted from various
heterogeneous sources. Each of the data sources is extracted comes up with distinct sources
which need to be managed. Along with it needs to integrate into the ETL system so that data can
be extracted in an efficient way. ETL process requires a proper system for which is different for
DBMS, operating system and lastly communication protocol. The logical data map aims in
providing regarding the start point and end point.
Extraction of Data
Relevant and useful data is extracted from various sources in this phase. An individual
makes use of static extraction at the data warehouse requires population for the time. This can be
considered like a snapshot of various operational data. Incremental extraction is used for
updating data warehouse on regular basis (White, 2018). Sizes are a large number of changes
which are given to source data for extraction. Incremental extraction is applied to update data
warehouse the log which is maintained by database DBMS. Generally, a timestamp is given with
an operational database which is used for recording data, when it is changed or added then it can
be used for streamlining the process of extraction. Extraction is considered to be source driven if
an individual writes it to the operational databases. The extracted data is generally selected on
the basis of the quality which is provided (Kimball et al. 2015). This particular thing depends on
the fact how perfectly the constraints are applied
Transformation of Data
Transformation is the main part of the total reconciliation phase. It mainly converts the
operational format into the required format of the data warehouse (Talib et al. 2016). If a three-
layer architecture is implemented, then the output phase is provided to the reconciled layer of

9DATA ANALYTICS AND BUSINESS ANALYTICS
data. In the presence of reconciled data layer, a proper establishment is required for mapping the
data between the source layer and warehouse layer. In this particular case, there can be an
instance of a complex integration phase is needed for designing the data warehouse (Khan and
Hoque, 2015). A list of parameters should be taken into account like lose text can easily hide
valuable information and different formats are needed for individual data which is provided. A
list of this should be taken into account for transforming the recoiled data layer.
 Conversion and normalization which tends to operate on both of the given formats
of storage and proper units are required for measuring the each of the provided
data.
 Matching which is associated with equivalent fields which works from various
phases.
 Selection is associated which a large number of source fields and records for it.
At the time of populating of data warehouse, normalization can be changed by
denormalization as data warehouse are sometimes denormalized and provides aggregation to
provide a sum to easily upgrade. The transformation process is almost connected to the ETL
tools.
Loading Data
Loading in the data warehouse is considered to be the last step. It is can be carried by
two methods like refresh and update.
Refresh: Data warehouse is completely being rewritten. It merely focuses on the fact that
older data is being replaced (Astriani and Trisminingsih, 2016). Refresh is achieved by a

10DATA ANALYTICS AND BUSINESS ANALYTICS
combination by the static extraction which aims in initiating the initial population of the data
warehouse.
Update: The changes are given to source data which is added to the data warehouse.
Different updates are carried out without any kind of detection or modification of existing data.
Fig 1: Example of the ETL process
(Source: Chevalier et al. 2015)
ETL tool plays an important role in the processing of data in the warehouse. They come
up with flexible functional which is used for transforming, cleaning and providing assurance to
data quality (Mireku Kwakye, 2017). The functionalities are mainly designed a useful form of
data for the various end-user application. ETL technology comes up with a wide range of ETL
processes, methods and modeling process. Such a process can be considered to be an acyclic
graph with comes up with no activities and recordsets are present in each and every node which
represents each and every section of the graph (Chevalier et al. 2015). One can easily look into
the fact that the ETL process as the synthesis of the various individual task which is used for
extraction, cleaning and execution graph which is also referred to as workflow. As a result of its

11DATA ANALYTICS AND BUSINESS ANALYTICS
nature of design and user interface tools, an ETL process can be accompanied by a plan which
needs to be executed.
The fact should be taken into consideration that the ETL process is not a novel in the
domain of computer science, several issues have been encountered. The ultimate issue is also
known as traditional ETL which is provided in the agreement which is provided in provided
language for formal ETL process (Mireku Kwakye, 2017). Optimizing of whole ETL process
creates a large number of research issues. Processing of parallel ETL is considered to be vital.
Standardization is a well-known problem which requires a note of attention. The functionality of
ETL depends on new areas of traditional data warehouse environment. Such cases are inclusive
of
 On-Demand ETL process which is executed from web data and they require it to
manually initiated by the demand of some specific individual.
 Streaming of ETL process requires the possible kind of filtering, conversion of
value, transformation for streaming information in the given format.
 Real-time ETL focus on capturing the requirement of data warehousing which
contains fresh data as much as possible.
With the development of internet and technology, the interest has moved to multiple
kinds of data. It does not require the important format of traditional data (Meehan et al. 2017).
Current ETL application should be focused to handle the data in the more efficient way.
The ultimate goal of this makes use of some architecture for data warehouse along with
mediated systems (Dakrory, Mahmoud and Ali, 2015). ETL tools create a flexible management
and design for the process of ETL. It is a method to solve the issues of data integration and allow