Data Warehouse Design: Analyzing Concepts and Technical Architectural Design

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