Data Warehouse Design: Analyzing Concepts and Technical Architectural Design

This article discusses the technical architectural design of data warehouses and the advantages and disadvantages of different architectures. It also analyzes the designing concepts of data warehouses and generates a data dictionary for the ETL process. The article includes tables and diagrams to illustrate the concepts. Course code, course name, and college/university are not mentioned.

Data Warehouse Design
Assignment 2b

Table of Contents
1. Introduction 2
2. Analyzing the Designing Concept of Data warehouse 2
3. Technical Architectural Design of Data Warehouse 3
1.1 Enterprise Data Warehouse 3
1.2 Federated Data Warehouse 4
1.3 Dependent Data Warehouse 4
1.4 Dependent Data Warehouse 5
4. Data Dictionary and ETL process 6
5. Conclusion 11
1

1. Introduction
This project aims to investigate the problem regards the typical business intelligence
design of a company and determining the key business decision requirements of the senior
managers of that company which was given in case study. The key performance indicators can
be analyzed through Dashboard which will be created using appropriate tool. Then the basic
concept of data warehouse will be analyzed. The technical architectural design of data warehouse
will be discussed and then advantages and disadvantages of architectures will be examined in
detail. Data dictionary will be generated for the fact and dimension tables and the main purpose
is for ETL process.
2. Analyzing the Designing Concept of Data warehouse
The star schema for data warehouse is designed with fact tables that reference to the
dimension tables. In each dimension tables, there are associated attributes are presented. These
attributes are applied for the construction of dimensional data marts and data warehouses that
support the analytic applications of business intelligence.
Figure 1 Star Diagram for Thomsen Diagram 1
2

In the above start diagram, the underlined attributes are primary keys. ‘Communication
Event’ is a Fact table. The dimensional tables are Fundraising Campaign, Communication Type,
Donation, Animal Treatment and Communication Receipt.
Figure 2 Star Diagram for Thomsen Diagram 2
In the above Star diagram is designed for Thomsen diagram 2. The underlined attributes
are primary keys. The fact table is ‘Revenue fact’. The dimensional tables are Donor, Grants,
Organization, Line Item Invoice and Council Invoice.
3. Technical Architectural Design of Data Warehouse
The data warehouse mainly aims to store the data. Generally, the data warehouse can be
defined as a collected, interrelated and organized data from various data sources in an
organization. The main purpose of designing data warehouse is that designing and developing
business intelligences in various applications which helps in decision making for the
management. It integrates the heterogeneous data from different sources and it produces an
analysis output in the form of reports. It is the basic data storage structure. The data stored in
data warehouse could not be transformed. But this data can be de-normalized into augmented
presentation or scalability. There are various architectures designed and presented for data
warehouse according to their advantages and disadvantages related to Art Works Organization
and its data management system.
3

3.1 Enterprise Data Warehouse
The architecture design of the Enterprise Data Warehouse is a typical and very suitable
for storing the data of big corporations. Here, the data are thoroughly examined, analyzed,
integrated, documented and collected data from heterogeneous resources. These data are
integrated from the extensive fundamentals history of exchange level data. This type of data
warehouse is designed for reporting and data analysis and a central constituent of business
intelligence.
Advantages:
 Single database warehouse used to compare all stats from diversely origin into one
 Centralized database management system and large in scope
 Detailed and organized data to relieve with various retrieval requests
 Helps in strategic decision-making
 Optimizes the origin process
Disadvantages:
 Big gauge which often pass to shoot failures
 Execution is liberal and period dedicated
 Hard to meet the requirements of every association or distinctive
3.2 Federated Data Warehouse
Federated Data Mart or Meta-(computer file full of information)/data management is like
independent data marts and maps many (computer files full of information) which are
independent in nature and are all over the place. This data warehouse supports to eliminate
(having more than one for safety or backup purposes) in the system.
Advantages:
 Development can be done in a parallel manner
 Involves single set extraction process
 Helps in related to a plan to reach a goal decision-making
 Helps in managing ETL processes
Disadvantages
 It causes high operating costs
 Long-term solutions are not able to be done or reached
4

 Historic complex difficulty is uncertain
3.3 Dependent Data Warehouse
In Dependent data warehouse, there are a unit multiple de-normalized knowledge marts
that area unit passionate about knowledge warehouses. The subsections of knowledge of
information area unit derived from enterprise data warehouse and systematic to suit into the
wants of a company system or application.
Advantages:
 Supports in decision-making
 Flexible warehouse such that the upcoming solutions or surprising mistakes can be
cooperatively changed something (to help someone)/took care of someone
 The data from the storage place is (pulled out or taken from something else) to develop
expected/looked ahead to intelligences according to the needed things of an organization
Disadvantages:
 Complex putting into use
 Very hard to buy/own/receive all the data needed/demanded from business/project data
warehouse
3.4 Dependent Data Warehouse
Independent Data Mart consists of clear/separate, (separated far from others) and many
small dimensional data warehouses for each business unit all over the place, used to extract
information (surviving with no outside help).
Advantages:
 Putting into use time is (producing a lot with very little waste)
 Aids in decision-making
 Separate and clear/separate marts for meeting different business needed things
 Tend to be very successful
Disadvantages:
 High operating cost due to (making copies of something/more than one person or
company doing the same thing) of data sources
 Poor putting into use because of many marts
5

 Medium to long term problems with (the ability to be made bigger or smaller) and
combination
 Not agreeing/not happening in the same way and combination of different things together
that work as one unit
After figuring out the worth, amount, or quality of the advantages and disadvantages of
the above described technical related to the beautiful design and construction of buildings, etc.,
Business/project Data Warehouse is the most appropriate and completely fits to the needed
things and systems of Art Works organization which will benefit them to complete their goals
and detailed descriptions of exactly what is required and because of this, the recommended
structure.
4. Data Dictionary and ETL process
Name of Table &Description Name of Attribute & Description
FUNDRISING CAMPAIGN
This dimension table uniquely
identifies different time values
Campaign ID
Primary Key to identify different and unique ID of
Campaign
Campaign Name
Attribute for name of Campaign
Notes
Attribute for different values of notes
Managing Staff Member ID
Attribute for identifying Managing staff members
Campaign Start Date
Attribute for identifying the starting date of each
Campaign
6

Campaign End Date
Attribute for identifying the completion date of each
Campaign
Total Raised from Campaign
Attributes for Total amount raised from different
Campaign
DONATIONS
This dimension table provides
details of the donations made
Donation ID
Primary Key to uniquely identify and provide details
of a particular donation
Donation Date
Attribute for displaying the date on which a particular
donation was made
Donation Amount
Attribute to display the amount of donation made in
dollars
Notes
Description attribute to designate how the donation
was made and whether it was part of a show or just a
walk-in
Organisational Donor ID
Attribute to identify the details of the donor
organisation through the organisation’s ID
Contact Donor ID
Attribute to identify the details of the donor
individual through the individual’s contact ID
7

Event ID
Attribute to identify the details of the event in which
and for which the donation was made
GRANTS
This dimension table provides
details of the grants asked for and
thus made towards the organisation
Grant Application ID
Primary Key to uniquely identify the application and
details of a grant
Grant Submission Date
Attribute to identify the date on which grant was
application submitted
Grant Outcome
Attribute to identify the outcome of the grant
application submitted
Grant Amount Applied for
Attribute to identify the amount of grant applied, in
dollars, in the application
Amount Granted
Attribute to identify the amount of grant approved
and thus given
Amount Received
Attribute to identify the amount of approved grant
received till a particular date as compared to the total
approved grant
8

Notes
Description attribute to designate how the grant was
made and whether it was part of a show or not
Programme ID
Attribute to recognize the details of the programme
for which grant was made
GRANTING ORGANISATIONS
This dimension table provides the
particulars of the granting
organisations who make the grant
towards the organisation
Granting Organisation ID
Primary Key to uniquely identify the details of a
particular business unit who made the grant
Name
Description attribute to display the name of the
granting organisation
Contact
Description attribute to display the contact details of
the granting organisation
Street Address
Description attribute to display the street address of
the granting organisation
City
Description attribute to display the city where
granting organisation is based
9

Postcode
Description attribute to display the postcode of the
place where granting organisation is based
State
Description attribute to display the state where
granting organisation is based
Country
Description attribute to display the country where
granting organisation is based
REVENUE FACT
This fact table provides the revenue
generated and expenses incurred by
the Art Works organisation
Time Key ID
Foreign Key from Time dimension to identify the
time details
Donation ID
Foreign Key from Donation dimension to identify
donations details
Sale ID
Foreign Key from Donation dimension to identify
sales details
10

Grant Application ID
Foreign Key from Grants dimension to identify the
details of the grants made
Granting Organisation ID
Foreign Key from Granting Organisation dimension
to identify the details of the granting organisations
who make the grants
Total Revenue
Attribute which calculates and displays total revenue
generated from the three revenue/income streams
Total Expenses
Attribute which calculates and displays total expenses
incurred
5. Conclusion
A suitable decision support is chosen according to the data visualization approach. The
application design model is identified based on the business requirements and the model is
designed well. Then the basic concept of data warehouse is analyzed. The technical architectural
design of data warehouse is discussed and then advantages and disadvantages of architectures
will be examined in detail. Data dictionary is generated for the fact and dimension tables and the
main purpose is for ETL process.
11

12