Data Warehouse Design : Case Study

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

DATA WAREHOUSE DESIGN 13
Junk Dimension The junk dimension is a
collection of attributes that
provide answer to the simple
questions such as in the form of
yes/no
BOOKING can be the Junk
Dimension as this dimension
illustrates whether a booking is done
online or not
Mini Dimension The quick changes in the
warehouse data is presented
through the mini dimensions
ITEMSALE is the mini dimension as
this table stores the sales of item for
every customer
Bridge Table This dimension is used for
joining two tables and showing
the many-to-many relationship
ItemSAle dimension tables serves as
the bridge table. It divides the many-
to-many relation between the Item and
Customer dimension
Conformed
dimension
This dimension is used for
generating same sets of results
for every fact table
VEHICLETYPE is the conformed
dimension as it is used for deciding the
vehicle renting process
Role playing
dimension
This dimension takes part in
every fact table
The DATE, PROMOTION, ITEM,
BOOKING, VEHICLETYPE,
DRIVERS, CUSTOMERS, VEHICLE

DATA WAREHOUSE DESIGN 14
7. Identification of Fields:
Question 1: Whether the promotion was profitable? That is, whether the vehicles under
promotion experienced an increase in rental during the promotional period?
 Advertisement/commercials: PromotionID, Total_Cost_of_Promotion,
Total_Profit_from_Promotion, registrationID, DateID
 DATE: Day, Month
 VEHICLE: model
Question 2: Which media type is most effective? That is, the profitable promotion used
which media type?
 Advertisement/commercials: PromotionID, Total_Cost_of_Promotion,
Total_Profit_from_Promotion, registrationID, DateID, CompanyID
 DATE: Day, Month
 MEDIACOMPANY: CompanyID
Question 3: Any stores have rented out more vehicles during the promotion on Christmas
holiday?
 Rental and Sales Fact Tale: Total_Rent_Amount,
Total_Rent_of_Vehilce_During_Promotion
 DATE: Date, Month
 BOOKING: bookingNO
Question 4: What vehicles were on promotion but did not rent?

DATA WAREHOUSE DESIGN 15
 Rental and Sales Fact Tale: Total_Rent_of_Vehilce_During_Promotion,
Rent_Start_Date
 DATE: Date, Month
 BOOKING: bookingNO
Question 5: Who are top 5 customers make a year out of all stores
 Rental and Sales Fact Tale: customerID, StoreID, RegistrationID, contractID
 Dimension_Date: Year
 CUSTOMERS: lastName, firstName
Question 6: Which categories of Vehicles have made the highest profit in past year?
 Rental and Sales Fact Tale: registrationID, returnprotocolID, Total_Rent_Amount
 Date: Year
 VEHICLE: model
 BOOKING: bookingNO
Question 7: What is the main age group of those customers renting online?
 Rental and Sales Fact Tale: customerID, bookingNO
 BOOKING: OnlineBooking
Question 8: What is the preferred payment method of customers?
 Rental and Sales Fact Tale: Customer_Key, contractID
 CONTRACT: paymentmethod

DATA WAREHOUSE DESIGN 16
8. Conclusion:
From the above study it can be concluded that the data warehouse is able to fulfill most
of the business decision associated queries. The warehouse allows fast and reliable source of
data. The impact of the Data Warehouse on CarHireOZ is significant.
