Data Warehouse Design Assignment

Running head: DATA WAREHOUSE DESIGN
Data Warehouse Design of CarHireOZ
Name of the Student
Name of the University

DATA WAREHOUSE DESIGN 2
Table of Contents
Introduction:....................................................................................................................................3
1. Data Warehouse Bus Matrix:.......................................................................................................3
2. Star Schema for the Data Marts:..................................................................................................4
2.1 CAR RENT:...........................................................................................................................4
2.2 Driver Assign:........................................................................................................................5
2.3 Sell Item:................................................................................................................................5
2.4 Invoice Generation:................................................................................................................6
2.5 Promotion:..............................................................................................................................7
3. Fact Table, Granularity and Justification against Granularity:....................................................7
4. Justification and Attribute Hierarchy of the Dimension Tables:...............................................11
5. Data Warehouse Design Features:.............................................................................................15
6. Identification of Fields:..............................................................................................................15
Conclusion:....................................................................................................................................17
Bibliography:.................................................................................................................................18

DATA WAREHOUSE DESIGN 3
Introduction:
The data warehouse is the way of storing data in a central location, collected from various
sources, and retrieving data from that storage. The Data Warehouses uses the data mining
processes for efficient and accurate data retrieving. Warehouses have various data marts and fact
tables. The fact tables act as the central table that connects the database with the others.
1. Data Warehouse Bus Matrix:
Business
processes
Dimensions
VEHICLE_TYPE
VEHICLE
CUSTOMER
MEMBER_CLASS
BOOKINGS
DRIVER
CONTRACTS
RETURN_PROTOCO
DATE_TABLE_TABL
PRODUCT_PROMOT
ITEMS
SOLD_ITEM
STORESS
MEDIA_COMPANY
Car Rent P P P P P P P P P
Drier Assign P P P
Sell Item P P P P P
Invoice
Generation
P P P P P P P P
Promotion P P P P P P P P

DATA WAREHOUSE DESIGN 4
2. Star Schema for the Data Marts:
2.1 CAR RENT:

DATA WAREHOUSE DESIGN 5
2.2 Driver Assign:
2.3 Sell Item:

DATA WAREHOUSE DESIGN 6
2.4 Invoice Generation:

DATA WAREHOUSE DESIGN 7
2.5 Promotion:
3. Fact Table, Granularity and Justification against Granularity:
Fact table name Fact granularity Brief justification
Car_RENT_FACT_TABL
E
TotalCarRent The proposed fact table
stores the data of the totl
amount of car rented in a

DATA WAREHOUSE DESIGN 8
time period
TotalDamageCost The proposed fact table
stores the data of the
total fine collected from
customer for damage
TotalEarnFromCustomer The proposed fact table
stores the data of the
entire amount of
business done with a
customer(s)
DRIVER_ASSIGN_FACT
_TABLE
None This table does not have
any fact. This fact table
shows the infroamtion of
assigned driver for each
rent
ITEM_SALE_FACT_TA
BLE
TotalItemsSold The proposed fact table
stores the data of the
number of items sold for
rent
ProfitFromItem The proposed fact table
stores the data of the
capital the organization

DATA WAREHOUSE DESIGN 9
has made by selling
items
INVOICE_GENERATE_F
ACT_TABLE
TotalChargeOfRent The proposed fact table
stores the data of the
entire cost of rent
including digamma cost
(if any)
TotalRevenue The proposed fact table
stores the data of the
total revenue of the
organization based on
year(s)
PayInvoiceFailAmoiunt The proposed fact table
stores the data of the
amount of capital is due
for payment
TotalTaxPercentage The percentage of tax
collected from customer
for each rent
PROMOTION_FACT_TA
BLE
PromotionCost The proposed fact table
stores the data of the cost
of promoting the

DATA WAREHOUSE DESIGN 10
business
PromotionSuccess The proposed fact table
stores the data of the
capital made from
promoting the business
RentAmount The proposed fact table
stores the data of the
quantity of cars rented in
a specific time period
MediaCompanyProfit The proposed fact table
stores the data of the
how much organization
has profited from
advertising through a
media company
PromotionDuration The proposed fact table
stores the data of the
duration of each
promotion

DATA WAREHOUSE DESIGN 11
4. Justification and Attribute Hierarchy of the Dimension Tables:
Dimension table name Brief justification Attribute hierarchies
VEHICLE_TYPE The proposed dimension
is designed to hold the
data of types of vehicle
available at the
organization
BOOKINGS The proposed dimension
is designed to hold the
booking details like how
the customer has booked
the car
RETURN_PROTOCOL The proposed dimension
is designed to hold
policies for returning a
vehicle

DATA WAREHOUSE DESIGN 12
CONTRACTS The proposed dimension
is designed to serve as
the service level
agreement between the
CarHireOZ and customer
CUSTOMER The proposed dimension
is designed for recording
all the customer details
DATE_TABLE The proposed dimension
is designed to allow the
warehouse system to
rectify data based on time
period

DATA WAREHOUSE DESIGN 13
MEMBER_CLASS The proposed dimension
is designed to assign a
priority to the customer
STORES The proposed dimension
is designed to hold the
physical store’s name,
location, contact number
and many more.
ITEMS The proposed dimension
is designed to allow
organization to details of
the items they sell
VEHICLE The proposed dimension
is designed to hold the
details of the vehicle
present for rent

DATA WAREHOUSE DESIGN 14
PRODUCT_PROMOTIO
N
The proposed dimension
is designed to hold the
promotion related major
details
MEDIA_COMPANY The proposed dimension
is designed to allow the
CarHireOZ to have
media company related
data
DRIVER The proposed dimension
is designed to store the
driver details

DATA WAREHOUSE DESIGN 15
5. Data Warehouse Design Features:
Design feature Brief description Brief justification
Fact less fact
table
The
DRIVER_ASSIGN_FACT_TABL
E is the fact less fact table in the
warehouse design
This fact table only holds the
information of which driver is
assigned to which contract.
Degenerate
dimensions
SalesOrderNumber is the
degenerated dimension in the
warehouse design.
SalesOrderNumber allows the
organization to identify the rent
related data easily
Bridge
Dimension
SOLDITEM dimension has been
used as the bridge dimension.
The SOLDITEM has been able to
mitigate the issue of many to many
relationship among the associated
tables.
Role playing
dimensions
Date_Dimension and
Customer_Dimension can be
considred as roleplaying
dimensions
Both of the dimensions have same
role in every fact table they are
associated with
6. Identification of Fields:
Question 1:

DATA WAREHOUSE DESIGN 16
PROMOTION_FACT_TABLE  PromotionCost, PromotionSuccess,
MediaCompanyProfit, PromotionKey
Product_Promotion_DIMENSION  PromotionCost
Question 2:
PROMOTION_FACT_TABLE  PromotionSuccess, MediaCompanyProfit,
CompanyKey
Media_Company_DIMENSION  CompanyName, CompanyCharge
Question 3:
Car_RENT_FACT_TABLE  StoreKey, DateKey, contractKey
STORES_DIMENSION  StoreName
CONTRACTS_DIMENSION  Rent_Start_Date
Question 4:
Car_RENT_FACT_TABLE  registrationKey, DateKey, contractKey
VEHICLE_DIMENSION  VehicleModel
Date_Dimension  Month_Name
Question 5:
INVOICE_GENERATE_FACT_TABLE  customerKey, contractKey,
StoreKey, DateKey
CUSTOMER_DIMENSION  CustomerLastName, CustomerFirstName
Date_Dimension  Year
Question 6:

DATA WAREHOUSE DESIGN 17
Car_RENT_FACT_TABLE  registrationKey, DateKey, contractKey,
TotalCarRent
VEHICLE_DIMENSION  VehicleModel
CONTRACTS_DIMENSION  Rent_Start_Date
Question 7:
INVOICE_GENERATE_FACT_TABLE  bookingNO, customerKey
CUSTOMER_DIMENSION  CustomerBirthdate
BOOKING_DIMENSION  BookingType
Question 8:
NVOICE_GENERATE_FACT_TABLE  contractKey
CONTRACTS_DIMENSION  Contract_paymentmethod
Conclusion:
From the above study it can be concluded that the warehouse system is a powerful way of
collecting and storing data. The data warehouse has been pressed accurately. The data warehouse
considers all the business processes of CarhireOZ. The organization will benefit immensely after
implementing the Warehouse.

DATA WAREHOUSE DESIGN 18
Bibliography:
Chevalier, M., El Malki, M., Kopliku, A., Teste, O. and Tournier, R., 2015, April. How can we
implement a Multidimensional Data Warehouse using NoSQL?. In International Conference on
Enterprise Information Systems (pp. 108-130). Springer, Cham.
Dembczynski, K., 2015. Data Integration and ETL Process.
George, J., Kumar, V. and Kumar, S., 2015. Data Warehouse Design Considerations for a
Healthcare Business Intelligence System. In World Congress on Engineering.
Hamoud, A.K., Adday, H., Obaid, T. and Hameed, R.A., 2016. Design and Implementing Cancer
Data Warehouse to Support Clinical Decisions.
Lam, W.K.S., Sahama, T. and Gajanayake, R., 2016. Constructing a traditional Chinese
medicine data warehouse application. arXiv preprint arXiv:1606.02507.
Rahman, L., Riyadi, S. and Prasetyo, E., 2015. Development of Student Data Mart Using
Normalized Data Store Architecture. Advanced Science Letters, 21(10), pp.3225-3229.
Vaisman, A. and Zimányi, E., 2014. Data warehouse systems. Springer, Heidelberg.
Wang, J. and Kourik, J.L., 2015. Data Warehouse Snowflake Design and Performance
Considerations in Business Analytics. ASBBS Proceedings, 22(1), p.472.