Data Warehouse Architecture Explained
Translated by
ScribdTranslationsData Warehouse Architecture Explained
Translated by
ScribdTranslationsData Warehouse Architecture
All the factors that characterize the data warehouse are based on a
architecture formed by layers or levels. This varies depending on the utility
that the data warehouse be given to the organization. However, in lines
In general, the architecture of the DWH is made up of:
1. Data sources
2. ETL
3. Data Warehouse
4. Data marts
5. Data exploitation
Data Sources Layer
It refers to all those sources of the company from where they come from.
data to be entered into the DWH. These sources can be: websites, networks
social media, emails, call logs, CRM software orERP,
administrative systems, among others.
ETL Layer (Extract – Transform – Load)
It refers to the entire process and to theETL technologiesinvolved in:
extract the data from the mentioned sources,
otransform them into a homogenized format,
oand load them into the data warehouse.
For the execution of this process, there are variousETL toolslike the
following:
IBM InfoSphere DataStage
oAWS Glue
oInformatica PowerCenter
3) Data warehouse layer
Once the data has been cleaned and transformed, it is stored in
the data warehouse proper.
4) Layer of data marts
Losdata martsson smaller warehouses that enable accessibility
even more the information that is in the DWH. The aim of the data marts is
group data according to specific categories and facilitate their queries by areas
of individual businesses.
In this sense, a company can have a data mart for the department.
demarketing, another for human resources, another for finance, etc.
5) Data exploitation layer
This is the most superficial layer of the DWH facing the end user. It involves
all those systems that allow for data querying both to
the data marts as the data warehouse directly. Such query can be
for analytical purposes, report generation, comparative procedures,
among others.
In this layer, both the systems [Link] intelligence (BI)how
theOLAP systemsand those fromdata mining.
Some popular tools areMicrosoft Power BIyAWS BI.
Advantages of the Data Warehouse
Any company that has the DWH system and implements it properly
intelligent, will have a large number of benefits in its favor that will allow him/her to
optimize each of its areas. This with the aim of getting the most out of
potential to all registered information.
Thus, among the advantages of the DWH are the following:
If the data is integrated in a structured way, its installation is easy.
The use of the data warehouse provides flexibility and speed for the
access to the documents andcompany data.
The historical record allows for the reuse of data for development of
retrospective or comparative analyses.
The DWH data is useful in the short, medium, or long term.
Phases to design and build a Datawarehouse
The following scheme illustrates the steps and phases we must go through to
design and build a Datawarehouse.
It is not a purely technical process; it involves understanding the requirements.
of the client and the processes of their business, to know the data we are going to
access (use read-only users to access customer data!), your
relationships, their particularities...
Experience is very important for doing work of this type and accumulating
this experience is a work of years, as you find that the data to
access can be in any format, from a simple txt to
unexpectedBtrieve, passing through ODBC standards, Web services...
In the following articles I will continue to share part of my experience and
we will move on to more practical aspects of building a Datawarehouse.
Practical case of Datawarehouse
"The vehicle rental company Rent4car wants to design a
data warehouse as a decision support system
strategic. The company has several branches, where they offer
rentals of a wide variety of vehicles such as cars, motorcycles,
caravans, vans, etc.
The data warehouse aims to centralize the information from all branches.
with the aim of defining new business strategies. There is a desire to analyze
both sales (rentals made at each branch) as well as expenses
to be able to study which sales points are the most profitable
they provide the customer profiles by type of vehicle to adjust the
insurance, employee efficiency by branch, etc.
The data warehouse will be fed from large operational databases.
such as sales, expenses, customers, and HR management.
Construction Phases
In the construction phase, we will model the relational tables in a
large denormalized structure composed of fact tables and more
small ones that will define the n-dimensions called dimension tables.
The dimensional model divides the world of data into two main types: the
measures and the dimensions of these measures. The measures are always
numerical, are stored in fact tables and the dimensions are
textual and are stored in the dimension tables.
The fact table is therefore the primary table of the dimensional model, and
contains the values of the business that is to be analyzed. Understood this
concepts we will detail the steps to follow below in the
construction of the data warehouse.
1. Analysis and design of the star model
In this first stage of construction, the modeling of the
data, in which the dimension tables and the fact table are determined.
2. Load program for dimension and fact tables
At this stage, the ETL (Extraction, Transformation) process must be carried out.
and load) that allows reading the tables of the transactional systems so that
can be loaded into the dimension tables. During this process, it is necessary to
consider all the restrictions and exclusive and necessary logic for
store the data. For example, it may happen that two employees of
different branches have the same employee code, so it will be
necessary to use equivalence or conversion tables during the process
ETL to avoid data inconsistency and duplication.
The first thing to consider for the ETL process is the frequency, which must be
to be established based on the needs of the business, that is, with what
It is necessary to have updated data for the analysis frequently. Also
the availability of resources should be considered, both from the sources of
data (that the data warehouse feeds on) like that of its own
data warehouse, as performance will be affected during the ETL process.
In our case study, the update policy could be established.
the data monthly, extracting the transactions from the previous month
in the early morning of the first day of the following month. This frequency is sufficient
for the analysts, and it is established in a period of time in which there is
availability of resources for processing and does not interrupt usage
normal from the data sources.
3. Development of OLAP cube
OLAP cubes are the tools that are based on the ability to
analyze and explore the data, they provide us with an interactive analysis by the
different dimensions of the data (for example, time, product, customer,
geographical criteria, etc.) and by the different levels of detail. In the
nextlinkIt explains how to create an OLAP cube from Excel 2010.
In conclusion, in the development of a data warehouse or data storage, there is
You must have a clear understanding of the information you intend to analyze and what objectives.
they are pursued, as it is the basis of the ETL process that allows us to feed from
information on the fact and dimension tables defined for our
data warehouse