Data Warehouse Design: A Comprehensive Case Study of CarHireOZ

This project provides a detailed data warehouse design for CarHireOZ, a car rental company. It begins with a data warehouse bus matrix, outlining business processes and dimensions. The core of the project involves the design of star schemas for Rental and Sales, and Advertisement_Commercials data marts. The document then elaborates on fact tables, defining granularity and providing justification. Dimension tables are also analyzed, with justifications and attribute hierarchies provided for each. The design features, including factless fact tables, degenerate dimensions, role-playing dimensions, bridge tables, and junk dimensions, are explained. Finally, the project identifies specific fields relevant to various queries, demonstrating practical application of the design principles. The project 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:....................................................6
4. Justification and Attribute Hierarchy of the Dimension Tables:.................................................8
5. Data Warehouse Design Features:.............................................................................................13
6. Identification of Fields:..............................................................................................................14
Bibliography:.................................................................................................................................17

DATA WAREHOUSE DESIGN 3
1. Data Warehouse Bus Matrix:
Business
processes
Dimensions
VEHICLE_TYPE_DIMENSION
VEHICLE_DIMENSION
CUSTOMER_DIMENSION
MEMBER_CLASS_DIMENSION
BOOKING_DIMENSION
DRIVER_DIMENSION
CONTRACTS_DIMENSION
RETURN_PROTOCOL_DIMENSION
DATE_DIMENSION
PRODUCT_PROMOTION_DIMENSION
ITEM_DIMENSION
STORES_DIMENSION
MEDIA_COMPANY_DIMENSION
Rental and Sales X X X X X X X X X X
Advertisement_Co
mmercials
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 and Sales Fact Tale TotalRevenue The organization is
running the business to
make profit and grow
business. The growth of
the business is reflected
through the revenue. For
this reason, it is very
crucial to collect the
information of revenue.
CarRentAtPromotion This fact shows the
business growth at
processing level
ItemSellQuantity The organization also
make profit from selling
the items
RevenueInNormalPeriod Most of the time
business runs on normal
period
RevenueAtPromotionPeriod Business can find the

DATA WAREHOUSE DESIGN 7
quantity of rented car so
that proper business
decision can be made
ADVERTISEMENT_CO
MMERCIAL
PromotionCost It is essential for the
organization to reduce
cost of promotion so that
profit can be increased
PromotionSuccess The total amount of
capital collected form a
promotion
RentAmount The amount of car rented
because of a promotion
MediaCompanyProfit The amount of additional
capital CarhireOZ makes
from a media promotion
ProtionDuration The duration of the
promotion. This assists
in understanding the
impact of promotion and
reduce business by
adjusting promotion
duration next time

DATA WAREHOUSE DESIGN 8
4. Justification and Attribute Hierarchy of the Dimension Tables:
Dimension table name Brief justification Attribute hierarchies
BOOKING_DIMENSION The booking
allows the
organization to
determine whether
full online
business is ready
to be accepted or
not
CONTRACTS_DIMENSION Contacts act as the
proof of settled
deals. This
dimension allows
the organization to
collect additional
processes when
customer violates
a rule. Based on
these data, the
organization can
collect payment

DATA WAREHOUSE DESIGN 9
legally
CUSTOMER_DIMENSION Customers are the
base of business.
Having required
data of the
customer allows to
expand business
and better
promotion option
DATE_DIMENSION The date
dimension allows
the system to have
full information of
the values in
timeline aspect

DATA WAREHOUSE DESIGN 10
DRIVER_DIMENSION Having the details
of the drivers
allows the
organization to
manage the
business legally
and manage
processes
ITEM_DIMENSION The dimension
table allows the
organization to
have item related
data

DATA WAREHOUSE DESIGN 11
MEDIA_COMPANY_DIMENSION This dimension
allows the
organization to
find the details of
the media
companies
MEMBER_CLASS_DIMENSION CarhireOZ will be
using these data to
identify the
customer expense
capacity. The
promotions will be
made as per the
customer
capabilities
PRODUCT_PROMOTION_DIMENSIO
N
The promotion of
the product
ensures that
whether the
product sale will
increase or not.

DATA WAREHOUSE DESIGN 12
RETURN_PROTOCOL_DIMENSION The return details
are very crucial so
that business
operations can be
operated
smoothly.
STORES_DIMENSION The sales from
each store is
significant data.
CarhireOZ will
make various
decisions based on
sales made from
stores.