Designing a Data Warehouse for CarHireOZ: Case Study and Analysis

This project presents a comprehensive data warehouse design for CarHireOZ, a car rental company. It begins with an introduction to data warehousing and its role in supporting organizational decision-making. The project then details the creation of a bus matrix to align dimensions with business processes, followed by the development of star schemas for rental and sales, and advertisement/commercials data marts. It defines fact tables, their granularity, and justifications, as well as the attribute hierarchies for dimension tables like VEHICLE, CUSTOMERS, and DATE. The design incorporates features like junk dimensions, mini dimensions, bridge tables, and conformed dimensions. The project identifies essential fields to answer specific business questions related to promotions, media effectiveness, rental patterns, and customer behavior. Finally, it concludes by highlighting the data warehouse's ability to address business queries and its positive impact on CarHireOZ, supported by a bibliography of relevant sources.

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. Introduction:................................................................................................................................3
2. Data Warehouse Bus Matrix:.......................................................................................................3
3. Star Schema for the Data Marts:..................................................................................................4
3.1 Rental and Sales:....................................................................................................................4
3.2 Advertisement/commercials:.................................................................................................5
4. Fact Table, Granularity and Justification against Granularity:....................................................6
5. Justification and Attribute Hierarchy of the Dimension Tables:.................................................8
6. Data Warehouse Design Features:.............................................................................................12
7. Identification of Fields:..............................................................................................................13
8. Conclusion:................................................................................................................................15
Bibliography:.................................................................................................................................16

DATA WAREHOUSE DESIGN 3
1. Introduction:
The data warehousing is used for supporting the decision making process of an
organization by integrated, non-volatile, subject-oriented and time variant data collection
processes. The bus-matrix is used for aligning the dimensions with the business processes.
Through the star schema, it is possible to represent the design of the data warehouse.
The report is focused on the bus-matrix start schema, using granularity to justify the fact
tables and using the data to support business queries. The data warehouse of CarHireOZ is
designed for supporting the business objectives of the enterprise.
2. Data Warehouse Bus Matrix:
Business
processes
Dimensions
VEHICLETYPE
VEHICLE
CUSTOMERS
MEMBERCLASS
BOOKING
DRIVERS
CONTRACT
RETURNPROTOCOL
DATE
PROMOTION
ITEM
ITEMSALE
STORE
MEDIACOMPANY
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

DATA WAREHOUSE DESIGN 4
3. Star Schema for the Data Marts:
3.1 Rental and Sales:

DATA WAREHOUSE DESIGN 5
3.2 Advertisement/commercials:

DATA WAREHOUSE DESIGN 6
4. Fact Table, Granularity and Justification against Granularity:
Fact table name Fact granularity Brief justification
Rental and Sales Fact Tale Total_Rent_Amount This fact allows to
find the amount of
cars hired in a span
of time period (may
be a single day,
week, month or
year)
Total_Rent_of_Vehilce_During_Promotio
n
This fact is used for
finding the total
sales of the cars or
type of cars during
promotion
Total_Item_Sold This fact decides
the total amount of
an item sold during
a period of time
Total_Revenue Total revenue
allows the
management to
determine overall

DATA WAREHOUSE DESIGN 7
sales based on
Year(s)
Rent_Start_Date The date on which
the rent starts
Rent_End_Date The date on which
the rent ends
Advertisement/
commercials
Total_Cost_of_Promotion The total expense of
promoting
vehicle(s)
Total_Profit_from_Promotion Total profit from
vehicles during
promotion period
Total_Rent_of_Vehilce_During_Promotio
n
Entire amount of
bookings done
during promotion
period
Total_Profit_from_Media_Company The profit from the
media company
promotion
Promotion_Period The duration of a
particular

DATA WAREHOUSE DESIGN 8
promotion
5. Justification and Attribute Hierarchy of the Dimension Tables:
Dimension table
name
Brief justification Attribute hierarchies
VEHICLETYPE Allows the warehouse to determine the
type of the vehicle
typeID (PK)
description
price
VEHICLE The vehicle dimension table holds the
details of the vehicle that is rented to the
customers
registrationID (PK)
colour
madeyear
model
mileage
availability
photo
CUSTOMERS The details of the customers are stored
in the database through this dimension
table
customerID (PK)
lastName

DATA WAREHOUSE DESIGN 9
firstName
birthdate
address
city
state
zip
phone
MEMBERCLASS The member class is used for determine
the hierarchy of the customer status as
per the organization perspective
ClassID (PK)
Classdescription
Recency
Frequency
Life_Time_Value
BOOKING The booking dimension is for storing
the booking type mainly (either online
or not)
bookingNO (PK)
OnlineBooking
DRIVERS The details of the drivers are essential
for contract and rent processes
driverID (PK)
lastName

DATA WAREHOUSE DESIGN 10
firstName
birthdate
address
city
state
zip
phone
licenceNO
creditcard
CONTRACT contractID (PK)
insurance
paymentmethod
condition
limitmileage
RETURNPROTOCO
L
The RETURNPROTOCOL dimension
table assist in understanding the total
cost to be paid during a return of vehicle
returnprotocolID (PK)
damage
usage

DATA WAREHOUSE DESIGN 11
overduedays
totalcost
DATE The date dimension table allows the
organization to search results based on
timescale
DateID (PK)
Date
Day
DayOfWeek
Month
Year
Quarter
PROMOTION The promotion tables holds the type of
promotion such as TV or other and the
cost of that particular promotion
PromotionID (PK)
Type
Cost
ITEM The details of the items are required to
determine the total revenue
ItemID (PK)
Type
Name
Cost
ITEMSALE This dimension table helps in ItemID (PK)(FK)

DATA WAREHOUSE DESIGN 12
understanding the total sales of an item customerID (PK)(FK)
ItemName
CustomerName
STORE The store dimension is for storing the
details of individual stores
StoreID (PK)
Name
Address
ContactNo
FaxNo
MEDIACOMPANY CompanyID (PK)
Type
Charge
6. Data Warehouse Design Features:
Design feature Brief description Brief justification