Data Warehouse Design: An Overview of Types, Steps, and Components

This report delves into the intricacies of data warehouse design, elucidating its importance in transforming data into actionable insights for effective decision-making. It begins by defining the data warehouse and its role in integrating data from multiple sources. The report then categorizes the types of data warehouse designs, including enterprise data warehouses, operational data stores (ODS), and data marts, highlighting their unique characteristics and applications. Following this, the report details the sequential steps involved in data warehouse design, from requirement gathering and physical environment setup to data modeling, ETL processes, OLAP cube design, and front-end development. It also covers report development, performance tuning, query optimization, quality assurance, production rollout and maintenance, and incremental enhancements. Finally, the report outlines the key components of a data warehouse, including the overall architecture and the surrounding components, providing a comprehensive understanding of the data warehousing process. This detailed analysis equips readers with a strong foundation in data warehouse design and implementation.

Running head: Blog writing
BLOG WRITING

Running head: Blog writing
DATA WAREHOUSE DESIGN
August 7th, 2019
INTRODUCTION TO THE DATA WAREHOUSE DESIGN
The data warehouse is also called by the name of the enterprise data warehouse. In this
regard, it can be depicted that data warehouse design is being regarded as the process of building
solution with an aim to integrate different data from the multiple sources in order to give support
to the data analysis and analytical reporting. Here, if the data warehouse is not being designed in
an effective manner then in this situation the significant impact of the same can be seen in the
growth and profitability related condition of the company in an effectual way.
Further, it can also be said that data warehousing enable enterprise with regard to collect
data from that specific sources that tend to provide meaningful insight in relation to the problem
of the company. In simple words, it is very right to say that the data warehouse helps in
transforming data into useful information. It is by complying with the given type of activity only
a firm is able to take an effective decision on the basis of information that is available to them.
Thus, it is very correct to say that data warehouse design is being regarded as one of the
most important concepts for understanding. In accordance with the given context, here at first
description will be given about the types of data warehouse design. The given thing will enable
the user to get the idea about the different types of data warehouse design. Afterward, a
description will be given about the steps that are being used in data warehouse design. Finally,
the explanation will also be given about the main components of the data warehouse.
1

Running head: Blog writing
2

Running head: Blog writing
3

Running head: Blog writing
TYPE OF DATA WAREHOUSE DESIGN
The data warehouse design is basically of three types and they are all detailed below:
 Enterprise data warehouse: It is being regarded as a very first type of data warehouse
design In other words, it can be said that it is basically a centralized data warehouse. It
tends to provide decision support services across the organization. On the other hand, it
also provides a unified approach with an aim to represent and organize the data in an
effectual way. It also offers the ability to perform classification of data as per the subject
and as per the division.
 Operational data store: It is another type of data warehouse design. In this context, it
can be said that it is also called by the name of ODS. The given form of data warehouse
design is being taken into consideration when neither OLTP nor data warehouse gives
support to the reporting related needs of the corporation. Here, in the operational data
store, the data warehouse is being refreshed in real time. Thus, it is the reason why it is
4

Running head: Blog writing
being widely preferred for the various types of routine activities such as to store the
records of the firm’s employees.
 Data mart: It is considered as the subset of the data warehouse. In this regard, it can be
said that it is basically being designed for some specific department of the company such
as finance and sales, etc. Further, in the independent data mart, the user can collect data
directly from the specific sources. Overall, it can be said that these are being regarded as
the main type of data warehouse design. Hence, by having the knowledge of the same
manager of an organization can make a selection of effective design as per its need and
want.
STEPS OF DATA WAREHOUSE DESIGN
Till now we have gathered some basic information about the data warehouse design,
now it’s time to go in more detail in relation to the respective concept. In accordance with the
given context, it can be said that data warehouse design consists of different steps. The detail
descriptions about the steps are depicted below:
Requirement gathering
The very first step that is related to data warehouse design is requirement gathering. The
main aim of the respective step is to make the decision about the criteria which will be used for
the purpose to successfully implement the data warehouse. Here, the long term business strategy
of the company is as important as the current technical requirement. In this phase, basically
information is being gathered about the reporting requirement, development, testing and user
training, etc. After making the decision about the technical and business strategy, manager of a
firm will make a decision that how it will back up the data warehouse and how it will recover the
data if the system does not work well or it fails. Thus, here along with the task of gathering
information about requirements, the manager of the firm also formulates the disaster recovery
plan.
Physical environment set up
The second step of data warehouse design is the physical environment set up. Herein, it
can be depicted that database servers, physical application and OLAP should be separate. It is the
responsibility of the manager that it should test all the necessary changes with the help of
5

Running head: Blog writing
physical environment set up. Hence, after performing given activity only it should move them
towards development, production, and testing. Additionally, if the IT staff member will found
that there is some problem in the integrity of data then in this situation they can perform a
thorough investigation of the respective issue without causing an impact on the production
environment.
Data modeling
After gathering the requirements and setting the physical environment of the company, in
the next phase efforts are being made in terms of defining that how data structure will be
accessed, processed as well as stored in the data warehouse. Thus, the given activity is called by
the name of data modeling. In the respective step of data warehouse design, information about
the sources of data is being gathered. The information is gathered about the original source of
data as well as the availability of data that are very much necessary for the success of the project.
After assessing the data sources, the team will start building a logical structure on the basis of the
established requirement.
ETL
The given process will tend to take most of the time for the purpose to eat as well as
develop the majority of the implementation. It can be depicted that the development time of the
ETL can be reduced if data sources are being identified during the data modeling phase.
Additionally, it is also assessed that the main goal of ETL is to provide the optimized load speed
without performing any compromise on the quality of data. However, if the respective phase will
be failed then the given thing will be lead to the poor performance of the ETL process as well as
the whole data warehouse system in an effectual way.
OLAP cube design
In the respective step of data warehouse design, OLAP (On-Line Analytical Processing)
is the answer engine that tends to provide infrastructure for the multi-dimensional analysis as
well as an ad-hoc query. The design in relation to OLAP should come from those people who
tend to perform a query of the data. There are three critical elements are related to OLAP. These
are groupings measures, dimensions, and granularity. It is through this way only cube design is
being formed.
Front end development
6

Running head: Blog writing
In the respective phase, the decision will be taken that how the user will get access to the
data. The front end developers work here and he will also decide how reports will be run. For the
given purpose, there are different options available and these are purchasing the off the shelf
product and building the front end in the house. The devices such as tablets, desktop, and phone
are being used for the purpose to access the data in a secure way.
Report development
For many users, the only way to have contact with the data warehouse is through the
report that they tend to generate. Here, the option to deliver the report is another significant
matter of consideration. Here, along with the process of receiving the report by using a secure
web interface, here user will need a report which is sent as the spreadsheet and email attachment.
The process of the report should be changed after making the implementation of the same. On
the other hand, an effectively designed data warehouse should also be able to handle the new
reporting requests and in doing so no modification should be performed in the data warehouse
system.
Performance tuning
In this phase of data warehouse design, the organization will tend to perform tuning of
performance system on ETL, report delivery and query processing without influencing the
current production environment. The developer here has to make sure that the testing
environment, as well as application, should mimic the production environment. This will help in
the task of improving the performance of the company in an effectual manner.
Query optimization
There are some ways with the help of which query can be optimized. These understand
the execution of data, retrieving the data as little as possible and to perform the storing of results
immediately.
Quality assurance
When the development team declares that everything is ready for testing. Then in this
situation, the role of the quality assurance team comes into existence. The given team is always
from the side of the client. Generally, the quality team does not know very much about data
warehouse, but some of them may need to have to learn another tool. Thus, the given team will
basically assure that the data collected should fulfill the need and requirements of the data.
7

Running head: Blog writing
Rolling out to production
When an individual will get approval from the quality assurance team, then the respective
phase will come into existence. Thus, it can be considered as the live section. There are some
people who must think that the given process will be as easy as switching on the light. But, this
is not the fact as sometime it will take around a full week to bring each and every people online.
But, now things have changed totally as today user can also access data warehouse over the web.
Production maintenance
When the data warehouse will go for the production, then in this situation there is a need
for maintenance is being arisen. The different tasks that are involved here such as crises
management and regular backups etc will become very much important and it should also be
planned out effectively. Further, it is very much important that the usage of the end-user should
be monitored consistently. The given activity will enable in capturing the new activity and it will
also help in getting information that how the user is utilizing the data warehouse.
Incremental enhancement
It is being considered as the last step of data warehouse design. The need for the given
step arises just after when data warehouse will go live. In this phase, small changes need to be
made in the data warehouse as per the requirement of the business. For example, the original
geographical destination of the company may be different, suppose it has 4 sales regions. Hence,
now the sales of the company are going very well, so it will have 10 sales regions. Overall, it is
very correct to say that these are being regarded as the main steps of data warehouse design.
COMPONENTS OF DATA WAREHOUSE
Now we will get in the more details about the data warehouse design. In the respective
section, we will know more about the major components of the data warehouse. The details
about the same are given below:
Overall architecture
The very first component of a data warehouse is overall architecture. It is being based on
the relational database management system server that functions as the central repository for the
informational data. Here, processing, as well as operational data, are completely separate from
each other. On the other hand, the central informational repository is being surrounded by the
8

Running head: Blog writing
different types of key components that are being designed in such a manner so that the entire
environment can become functional as well as manageable. In addition to this, the source data
for the warehouse is coming from operational applications. Here, when data entered in the
warehouse than in this situation it is cleaned up and after that, is being transferred into integrated
format and structure. Further, the process of transformation involves activities like
summarization, conversion, condensation of data and filtering, etc. This involves because data
contains the historical component.
Data warehouse database
The central data warehouse database is being considered as the cornerstone of the data
warehousing environment. The given form of database is always implemented on technology
such as the relational database management system. But, the given type of implementation is
usually constrained by the fact that traditional relational database management systems are being
optimized for the processing of the transactional database. There are certain attributes of
warehouse examined and these are very large size of the database, ad-hoc query processing,
multi-table joins, drill-down and need of the flexible user view creation, etc.
Meta data
It is being considered as another most important component of the data warehouse
design. In accordance with the given context, it can be said that Meta data is being regarded as
the activity of describing the data warehouse. Here, it is basically used for building, maintaining,
managing and using the data warehouse. Additionally, it is also being classified into two forms
such as technical Meta data and the business Meta data. Both have significant importance in their
respective place. Here, the technical data warehouse possesses information about warehouse data
that is being used by the warehouse designers. Similarly, business Meta data possess that
information that is being given to the user for the purpose to get understanding about the
importation that is being stored in the data warehouse.
Access tools
It can be considered as the last component of the data warehouse design. The main aim
of the data warehouse is to provide useful information to the business so that they can take an
effective decision. Here, front end tools are used for the purpose to interact with the data
9

Running head: Blog writing
warehouse. There are many tools that require information specialist and there are many end-users
that tend to develop expertise in the tools. There are four main categories of tools such as
application development tool, data mining, online analytical processing tool, and query tools.
CONCLUSION
It can be concluded from the conducted analysis that data warehouse has significant
importance in today’s business environment. This is due to the reason that it is with the help of
given tool only a business manager is able to make an effective decision in relation to any kind
of business-related problems that tend to occur in front of it. Further, the given thing will enable
manager with regard to putting the activities of the organization in the correct direction in an
effectual manner. This will help in the process of improving p
10