Comprehensive Data Warehouse Design for CarHireOZ: Project Report

This project presents a comprehensive data warehouse design for CarHireOZ, a car rental company. It begins with a data warehouse bus matrix outlining business processes and dimensions. The project then details star schemas for various data marts, including car rental, driver assignment, item sales, invoice generation, and promotions. It defines fact tables, their granularity, and justifications, along with detailed explanations of dimension tables, their attributes, and hierarchies. The design incorporates key features such as a fact-less fact table, degenerate dimensions, bridge dimensions, and role-playing dimensions. Finally, the project identifies specific fields and their associations across different fact and dimension tables, offering a complete and practical data warehousing solution for CarHireOZ. The conclusion emphasizes the benefits of implementing the designed data warehouse for efficient data storage, retrieval, and business process analysis.

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