Data Warehouse Design for CarHireOZ: A Comprehensive Case Study Report

This report presents a comprehensive data warehouse design for CarHireOZ, a car rental company. The design includes a data warehouse bus matrix outlining business processes and relevant dimensions such as vehicle type, customer, bookings, and more. The report details star schemas for various data marts, including car rentals, driver assignments, item sales, invoice generation, and promotions. It defines fact tables with their granularity and justifications, such as total car rent, total damage cost, and total revenue. The report also explains the attribute hierarchies of dimension tables, like vehicle type, bookings, and customer details. Furthermore, it discusses data warehouse design features, including a factless fact table, degenerate dimensions, and role-playing dimensions. Finally, the report identifies fields across different fact and dimension tables, providing insights into the relationships between data elements, and concludes with 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. 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:....................................................7
4. Justification and Attribute Hierarchy of the Dimension Tables:...............................................10
5. Data Warehouse Design Features:.............................................................................................14
6. Identification of Fields:..............................................................................................................15
Bibliography:.................................................................................................................................17

DATA WAREHOUSE DESIGN 3
1. Data Warehouse Bus Matrix:
Busines
s
process
es
Dimensions
VEHICLE_TYPE
VEHICLE
CUSTOMER
MEMBER_CLASS
BOOKINGS
DRIVER
CONTRACTS
RETURN_PROTOCO
DATE_TABLE_TAB
PRODUCT_PROMOT
ITEMS
STORESS
MEDIA_COMPANY
Car Rent Y Y Y Y Y Y Y Y Y
Drier
Assign
Y Y Y
Sell Item Y Y Y
Invoice
Generatio
n
Y Y Y Y Y Y Y Y
Promotion Y Y Y Y Y Y Y Y

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 This fact holds
information of the totl
amount of car rented in a

DATA WAREHOUSE DESIGN 8
time period
TotalDamageCost This fact holds
information of the total
fine collected from
customer for damage
TotalEarnFromCustomer This fact holds
information 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 This fact holds
information of the
number of items sold for
rent
ProfitFromItem This fact holds
information of the
capital the organization
has made by selling

DATA WAREHOUSE DESIGN 9
items
INVOICE_GENERATE_F
ACT_TABLE
TotalChargeOfRent This fact holds
information of the entire
cost of rent including
digamma cost (if any)
TotalRevenue This fact holds
information of the total
revenue of the
organization based on
year(s)
PayInvoiceFailAmoiunt This fact holds
information 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 This fact holds
information of the cost
of promoting the
business

DATA WAREHOUSE DESIGN 10
PromotionSuccess This fact holds
information of the
capital made from
promoting the business
RentAmount This fact holds
information of the
quantity of cars rented in
a specific time period
MediaCompanyProfit This fact holds
information of the how
much organization has
profited from advertising
through a media
company
PromotionDuration This fact holds
information of the
duration of each
promotion
4. Justification and Attribute Hierarchy of the Dimension Tables:
Dimension table name Brief justification Attribute hierarchies

DATA WAREHOUSE DESIGN 11
VEHICLE_TYPE This data mart will hold
the data of types of
vehicle available at the
organization
BOOKINGS This data mart will hold
the booking details like
how the customer has
booked the car
RETURN_PROTOCOL This data mart will hold
policies for returning a
vehicle
CONTRACTS This data mart will serve
as the service level
agreement between the
CarHireOZ and customer

DATA WAREHOUSE DESIGN 12
CUSTOMER This data mart will
collect all the customer
details
DATE_TABLE This data mart will allow
the warehouse system to
rectify data based on time
period
MEMBER_CLASS This data mart will assign
a priority to the customer
STORES This data mart will hold
the physical store’s name,
location, contact number
and many more.