Data Warehouse Design: CarHireOZ Case Study Report Analysis

This report presents a detailed data warehouse design for CarHireOZ, a car rental company. The design includes a data warehouse bus matrix, star schemas for rental and sales, and advertisement commercials. The report defines fact tables, their granularity, and justifications. It also explains dimension tables, attribute hierarchies, and various data warehouse design features like bridge tables, conformed dimensions, junk dimensions, and mini-dimensions. The design identifies key fields for different business processes and concludes with a bibliography of relevant resources. The student has provided a comprehensive analysis of the data warehouse design, covering various aspects of data warehousing, including schema design, fact and dimension tables, and design features. The report effectively explains the rationale behind each design choice and provides a well-structured and informative analysis of a real-world business case.

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
VEHICLE_TYPE
VEHICLE
CUSTOMER
MEMBER_CLASS
BOOKINGS
DRIVER
CONTRACTS
RETURN_PROTOCO
DATE_TABLE_TAB
PRODUCT_PROMOT
ITEMS
ITEMS_SALE
STORESS
MEDIA_COMPANY
Rental and Sales X X X X X X X X X X X X
Advertisement_C
ommercials
X X X X X X X X

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

DATA WAREHOUSE DESIGN 5
2.2 Advertisement_Commercials:

DATA WAREHOUSE DESIGN 6
3. Fact Table, Granularity and Justification against Granularity:
Fact table name Fact granularity Brief justification
RENTAL_SALES TotalRentAmount This fact is for storing
the amount of rent price
collected for each
vehicle or model in a
span of time
TotalRentOfVehilceDuringPromotio
n
The quantity of rents
made for a particular
vehicle or model is
stores in this fact.
TotalItemSold The items are stored in a
dimension table. This
fact represents the
quantity of items sold.
TotalRevenue Revenue reflects the
total business growth.
This fact stores the
details of the total
revenue in currency form
Rent_Start_Date The DATE_TABLE on

DATA WAREHOUSE DESIGN 7
which the rent starts
Rent_End_Date The DATE_TABLE on
which the rent ends
ADVERTISEMENT_CO
MMERCIAL
TotalCosOfPromotion This fact allows the
organization to find the
entire cost of promotions
TotalProfitFromPromotion The entire profit made
from individual
promotions are stored in
this fact
TotalRentOfVehilceDuringPromotio
n
This fact stores the sales
of vehicles based on the
models
TotalProfitFromMediaCompany The total profit made
from promotions made
through media company.
This fact will assist in
better investment
PromotionPeriod This fact stores the time
period of the promotions

DATA WAREHOUSE DESIGN 8
4. Justification and Attribute Hierarchy of the Dimension Tables:
Dimension table name Brief justification Attribute hierarchies
VEHICLE_TYPE Vehicle type is generated solely for
collecting the details of the type of
vehicle. This dimension has huge
impact on business as various cost of
rent highly depends on the vehicle type.
typeID (PK)
description
price
VEHICLE The vehicle dimension table is
proposed for storing the data associated
with every vehicle present at the
organization. This dimension table
holds the registration number of the
vehicle and the model number also.
registrationID (PK)
colour
madeyear
model
mileage
availability
photo
CUSTOMER Customers are source of business. They
are the key of producing more profit. In
order to make more money, the
organization must be able to analyse the
customer behaviours and other details
properly. The promotions are based
customerID (PK)
lastName
firstName
birthDate

DATA WAREHOUSE DESIGN 9
mostly on this dimension table address
city
state
zip
phone
MEMBER_CLASS The member class has a high place in
the warehouse designs. It is because,
the class of the member is base of rent
charge and discounts
ClassID (PK)
Classdescription
Recency
Frequency
Life_Time_Value
BOOKINGS The bookings are made for each rent.
collecting and storing these records are
very essential for the business. The
bookings is for identifying whether the
booking has been done online or not.
BookingNO (PK)
OnlineBooking
DRIVER The driver details are very essential for
the organization. Without the details of
the drivers, the organization cannot rent
a car
driverID (PK)
lastName
firstName

DATA WAREHOUSE DESIGN 10
birthDate
address
city
state
zip
phone
licenceNO
creditcard
CONTRACTS The contracts act as the proof of settled
services and returns. This dimension
allows the business to find the details of
mode of payment for each rent, start
and end dates of rent.
CONTRACTSID (PK)
insurance
paymentmethod
condition
limitmileage
RETURN_PROTOCO
L
This dimension allows the organization
to find the damages for each rent or
damages a vehicle has faced during
lifetime.
RETURN_PROTOCOLI
D (PK)
damage
usage

DATA WAREHOUSE DESIGN 11
overduedays
totalcost
DATE_TABLE The DATE_TABLE dimension table
allows the organization to search results
based on timescale
Date_ID (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
ITEMS The details of the ITEMSs are required
to determine the total revenue
ITEMSID (PK)
Type
Name
Cost
ITEMS_SALE Total sales of items is find through this ItemID (PK)(FK)

DATA WAREHOUSE DESIGN 12
dimension table customerID (PK)(FK)
ITEMSName
CustomerName
STORES The details of the stores are very
essential for understanding business
perspective and taking various
decisions
STORESID (PK)
Name
Address
ContactNo
FaxNo
MEDIA_COMPANY This dimension assist in finding the
details of media company
CompanyID (PK)
Type
Charge
5. Data Warehouse Design Features:
Design feature Brief description Brief justification
Bridge Table The many-to-many relations
are not suitable for any
database. The bridge table
serves as the connection
between two relations in many-
ITEMS_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