Data Warehouse Design and Implementation: CarHireOZ Case Study Report

This report details the data warehouse design for CarHireOZ, a car rental company. It begins with a Data Warehouse Bus Matrix, outlining business processes (Rental and Sales, Advertisement_Commercials) and associated dimensions. The report then presents star schemas for each data mart, followed by a discussion of fact tables, granularity, and justifications. Dimension tables are analyzed, including attribute hierarchies. Key data warehouse design features, such as bridge tables, conformed dimensions, junk dimensions, and mini dimensions, are explained. Finally, the report identifies relevant fields for answering business questions related to promotion profitability, media effectiveness, rental trends during promotions, customer behavior, and vehicle performance, providing a comprehensive overview of the data warehouse design and its practical applications for CarHireOZ.

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

DATA WAREHOUSE DESIGN 2
Table of Contents
1. Data Warehouse Bus Matrix:.......................................................................................................3
2. Star Schema for the Data Marts:..................................................................................................4
2.1 Rental and Sales:....................................................................................................................4
2.2 Advertisement_Commercials:................................................................................................5
3. Fact Table, Granularity and Justification against Granularity:....................................................6
4. Justification and Attribute Hierarchy of the Dimension Tables:.................................................8
5. Data Warehouse Design Features:.............................................................................................12
6. Identification of Fields:..............................................................................................................14
Bibliography:.................................................................................................................................16

DATA WAREHOUSE DESIGN 3
1. Data Warehouse Bus Matrix:
Business
processes
Dimensions
INTERNET_BOOKING
DRIVER_DETAILS
CONTRACTS_DETAILS
RETURN_PROCESS
PROMOTIONAL_ITEMS
PRODUCTS_FOR_SALE
STORE_DETAILS
MEDIA_ORGANIZATION_DETA
Rental and
Sales
X X X X X X X X X X X X
Advertisement
_Commercials
X X X X X X X X
The two business cases here are the Rental and Sales and Adevrtisement_Commercials. The
associated facts and dimensions required for the two business processes are given in the above
table. The crossed marks are presenting the facts and dimensions associated with the business
processes.

DATA WAREHOUSE DESIGN 4
2. Star Schema for the Data Marts:
2.1 Rental and Sales:

DATA WAREHOUSE DESIGN 5
2.2 Advertisement_Commercials:
The two diagrams given in the above section are representing the two business cases’ star
schema. All the associated dimensions and facts of the databases are shown in the diagrams in
table format. The rows of the tables are representing the attributes that will be designed in the
database.
3. Fact Table, Granularity and Justification against Granularity:
Fact table name Fact granularity Brief justification

DATA WAREHOUSE DESIGN 6
RENTAL_SALES RentsInTotal The monetary value that
will be come from the rents
of the cars will be stored in
this fact.
Rents_while_Promoting The amount of the money
that will be get from the
rents at the time of
promotional activities, will
be stored in this fact.
Sold_Products Details regarding the
already sold products will
be stored in this fact.
Total_Earned_Revenue Revenue earned by the
company from all business
activities will be stored in
this fact.
Start_Date_For_Rent When a customer will rent
a car, then the starting date
of the rent will be stored in
this fact.
End_Date_For_Rent The return dates of the
rented car will be stored in
this fact.
ADVERTISEMENT_COM Advertisement_Cost The cost associated with
the advertisements will be

DATA WAREHOUSE DESIGN 7
MERCIAL stored in this fact.
Profit_From_Adds The profit amount got from
the advertisements will be
stored in this fact.
Rents_while_Promoting This fact is for keeping the
numbers of cars given in
rent at the time period of
the promotional activities.
Profit_From_Media_Organization The profits got from the
advertisement companies
will be stored in this fact.
Advertisements_time_Duration This fact is for recording
the time duration required
for the advertisements.

DATA WAREHOUSE DESIGN 8
4. Justification and Attribute Hierarchy of the Dimension Tables:
Dimension table name Brief justification Attribute hierarchies
VEICHLES _TYPES In this table, the description and price of
the cars will be stored with reference to a
unique typeID.
typeID (PK)
description
price
VEICHLES_DETAILS In this table, the details bout the car
models with the other important
specifications will be stored.
registrationID (PK)
colour
Year_Of_Manufacturin
g
model
mileage
availability
photo
CUSTOMER_DETAILS All the necessary details regarding the
customers of the company will be stored
in this table. Each customer has an unique
ID.
Customer_ID (PK)
Last_Name
First_Name
birthDate
address
city
state
zip
phone
MEMBERS_DETAILS Having an unique classID, the details
about the members classes will be stored
ClassID (PK)
Classdescription

DATA WAREHOUSE DESIGN 9
in the table. Recency
Frequency
Life_Time_Value
INTERNET_BOOKING The customers are able to book the cars
for rent over internet. This details will be
stored in this table.
BookingNO (PK)
Pre_booking
DRIVER_DETAILS The necessary details regarding the
drivers will be stored in this table. This is
very important for the safety of the cars
and customers at the time of rent.
DriverID (PK)
Last_Name
First_Name
birthDate
address
city
state
zip
phone
licenceNO
creditcard
Contracts_Details The details regarding the insurance,
condition of the cars and the mileage, will
be stored in this table.
CONTRACTID (PK)
insurance
paymentmethod
condition
limitmileage
RETURN_PROCESS When a customer will return a car after
renting, the details will be stored in this
table.
RETURN_PROCESSID
(PK)
damage

DATA WAREHOUSE DESIGN 10
usage
overduedays
totalcost
DATE_DETAILS All the time scales regarding the rent,
purchase, sale and promotions will be
stored in this table.
Date_ID (PK)
Date
Day
DayOfWeek
Month
Year
Quarter
PROMOTION The types of the advertisements and
money required for those will be stored in
this table.
PromotionID (PK)
Type
Cost
ITEMS All the products of the company with the
details like type, name and costs will be
stored in this table.
ITEMSID (PK)
Type
Name
Cost
Products_For_Sale The products that are ready for sale will
be stored in this table.
ItemID (PK)(FK)
Customer_DetailsID
(PK)(FK)
ITEMSName
Customer_DetailsName
STORE_DETAILS Different stores are available for the
organization. The details of all the stores
with a unique id will be stored in this
STORE_DETAILSID
(PK)
Name

DATA WAREHOUSE DESIGN 11
table. Address
ContactNo
FaxNo
MEDIA_ORGANIZATION
_DETAILS
The details about the advertisement
companies will be stored in the table.
CompanyID (PK)
Type
Charge
5. Data Warehouse Design Features:
Design feature Brief description Brief justification
Bridge Table When many to many relation is
not sufficient to represent the
schema, then the bridge table is
used. This is applied in this case.
Products_For_Sale dimension table acts
as the connector between Items and
Sales dimensions. The primary key of
each of the two tables are the foreign
key in Products_For_Sale table.
Conformed
dimension
Using this relationship same
queries and outcomes will be seen
through different perspectives.
CUSTOMER_DETAILS,
STORE_DETAILS AND
RETURN_PROCESS can produce
similar result from different types of
queries.
Junk Dimension This dimension will help to get the
results in just positive or negative
aspects.
The Products_For_Sale is considered as
junk dimension as the database will
hold the information regarding the
product even after sold out.
Mini Dimension Very small changes will be stored
in this dimension.
Internet_booking is an example of this
case. Whatever the status of the

DATA WAREHOUSE DESIGN 12
booking, the database will be updated.

DATA WAREHOUSE DESIGN 13
6. Identification of Fields:
Question 1: Whether the promotion was profitable? That is, whether the
Veichles_Detailss under promotion experienced an increase in rental during the promotional
period?
 Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion,
Total_Profit_from_Promotion, registrationID, DATE_ID
 DATE_DETAILS: Day, Month
 VEICHLES_DETAILS: model
Question 2: Which media type is most effective? That is, the profitable promotion used
which media type?
 Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion,
Total_Profit_from_Promotion, registrationID, DATE_ID, CompanyID
 DATE_DETAILS: Day, Month
 MEDIA_ORGANIZATION_DETAILS: CompanyID
Question 3: Any stores have rented out more Veichles_Detailss during the promotion
on Christmas holiday?
 RENTAL_SALES:Total_Rent_Amount,
Total_Rent_of_Vehilce_During_Promotion
 DATE_DETAILS: DATE, Month
 INTERNET_BOOKING: BOOKINGSNO
Question 4: What Veichles_Detailss were on promotion but did not rent?

DATA WAREHOUSE DESIGN 14
 RENTAL_SALES: Total_Rent_of_Vehilce_During_Promotion,
Start_Date_For_Rent
 DATE_DETAILS: DATE, Month
 INTERNET_BOOKING: BOOKINGSNO
Question 5: Who are top 5 customers make a year out of all stores.
 RENTAL_SALES: Customer_DetailsID, STORE_DETAILSID, RegistrationID,
CONTRACTSID
 DATE: Year
 CUSTOMER_DETAILS: Last_Name, First_Name
Question 6: Which categories of Veichles_Detailss have made the highest profit in past
year?
 RENTAL_SALES: registrationID, returnprotocolID, Total_Rent_Amount
 DATE_DETAILS: Year
 VEICHLES_DETAILS: model
 INTERNET_BOOKING: BOOKINGSNO
Question 7: What is the main age group of those customers renting online?
 RENTAL_SALES: Customer_ID, BOOKINGNO
 INTERNET_BOOKING: BOOKINGSNO
Question 8: What is the preferred payment method of customers?
 RENTAL_SALES: Customer_Details_Key, CONTRACTID
 CONTRACTS_DETAILS : paymentmethod
