Data Warehouse Design and Analysis

Running head: DATA WAREHOUSE DESIGN AND ANALYSIS
Data Warehouse Design and Analysis
Name of the Student
Name of the University
Author’s name:

DATA WAREHOUSE DESIGN AND ANALYSIS1
Table of Contents
Analysis and Description of Table:.................................................................................................2
Primary and Foreign Key:...............................................................................................................3
Database Diagram:...........................................................................................................................4
Queries:............................................................................................................................................4
Bibliography:.................................................................................................................................17

DATA WAREHOUSE DESIGN AND ANALYSIS2
Analysis and Description of Table:
In star schema of data warehouse, the dimension table is an entity. Each dimension tables
serves a specific purpose in the database. Taken as an example, the date dimension table is used
for showing the dates for business processes such as sales, purchase and more.
CompanyDim: The first table in the database is CompanyDim. This is a dimension table
used to store the details of the branch. This dimension can be considered as slowly changing
dimension. It is because, new attributes and data insertion into this dimension are very rare. This
happens only when changes are done in the organization.
CustomerDim: It is the second dimension table in the data warehouse system. The
customer dimension table can be considered as the conformed dimension. The customer table
can also be Slowly Changing Type 2 dimension table. Based on how the organization uses the
customer dimension, it is category will depend.
DateDim: DateDim is the third dimension table in this warehouse. This dimension table
holds all the date related data. This is a type 0 slow changing dimension. The organization will
never fill any attribute in this dimension table once file first filling is done.
ProductDim: This is the fourth dimension table. All the product data is stored in this
dimension table. The product dimension is type 1 slow changing dimension. This dimensional
table will be reloaded with every data warehouse load. The history of this dimension table is not
recorded. The refreshing of the data is the main difference between type 0 and type 1 slow
changing dimension table.

DATA WAREHOUSE DESIGN AND ANALYSIS3
WarehouseDim: This is the final dimension table. Warehouse related data is stored in the
warehouseDim dimension table. This is a role playing dimension table.
Primary and Foreign Key:
The primary key of the company dimension is the CompanyKey. This is a number data
type attribute. The primary key represent each company branch data uniquely. The customerDim
has the primary key called customerDim. It is also a number data type. There is no foreign key in
the customer dimension. DateKey is the primary key of the Date dimension table. For each order,
this data key provides the data of order, month of order, year of order and other date related
attributes.
In the sales fact table, the primary key is the ID. This ID is used for searching each row
uniquely. Each sales is associated with one product. For this reason, the ProductKey has been
used as the foreign key in this fact table. The Data of purchase is denoted using this date key.
The customer who purchase the product needs to be included into this fact table. This is applied
using CustomerKey. All the foreign keys will be do nothing after delete or update.