Data Warehouse Design Assignment

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.

DATA WAREHOUSE DESIGN 13
VEHICLE_DIMENSION CarhireOZ must
keep all the
information of the
cars they own.
This allows the
organization to
have better insight
of business allow
further growth.
VEHICLE_TYPE_DIMENSION The organization
needs to have the
information on the
types of vehicle
they own
5. Data Warehouse Design Features:
Design feature Brief description Brief justification
Fact less fact tables The fact less fact table is the
type of fact table that do not
have any fact or measure
In the selected data warehouse
consent, the fact tables have proper
and significant facts or measures
Degenerate
dimensions
The degenerated dimension in
the fact table is referred to the
The sales order number can be referred
to as the Degenerate dimension in the

DATA WAREHOUSE DESIGN 14
key which has no dimension
associated with it
proposed Rental and Sales Fact Tale
Role playing
dimensions
Role playing dimension is often
referred to as the dimension
used in the same database
repeatedly for various reasons
The DATE_DIMENSION is the ideal
role playing dimension in the proposed
data warehouse design
Bridge Table The bridge table is used for
diving the many to many
implementing the database
physically
There is no bridge table in the data
warehouse
Junk Dimension The junk dimension stores the
answer of a query in yes no
only. No other query can be
resolved through this
dimension
The bookings_dimension is the junk
dimension. It only refers to answer,
whether the booking has been done
online or not.
6. Identification of Fields:
Question 1:
Advertisement_Commercials: PromotionKey, registrationKey, DateKey, PromotionCost,
PromotionSuccess
Date_Dimension: Day_name, Month_Name

DATA WAREHOUSE DESIGN 15
VEHICLE_DIMENSION: VehicleModel
Question 2:
Advertisement_Commercials: PromotionKey, CompanyKey, registrationKey, DateKey,
PromotionCost, PromotionSuccess
DATE_DIMENSION: DAY_NAME, MONTH_NAME
MEDIA_COMPANY_DIMENSION: CompanyName, CompanyType
Question 3:
RENTAL_SALES: registrationKey, DateKey, customerKey, bookingNO,
CarRentAtPromotion, RevenueAtPromotionPeriod
DATE_DIMENSION: DAY_NAME, MONTH_NAME
BOOKING_DIMENSION: BookingType
CUSTOMER_DIMENSION: CustomerLastName, CustomerFirstName
Question 4:
RENTAL_SALES: registrationKey, DateKey, bookingNO, CarRentAtPromotion,
RevenueAtPromotionPeriod,
Date_Dimension: DATE, Month
BOOKING_DIMENSION: BOOKINGSNO
Question 5:

DATA WAREHOUSE DESIGN 16
RENTAL_SALES: registrationKey, DateKey, customerKey, bookingNO, contractKey,
StoreKey
STORES_DIMENSION: StoreName, StoreAddress
Date_Dimension: Year
CONTRACTS_DIMENSION: Rent_Start_Date
CUSTOMER_DIMENSION CustomerLastName, CustomerFirstName
Question 6:
RENTAL_SALES: registrationKey, BookingNO, returnprotocolKey,
CarRentAtPromotion, RevenueInNormalPeriod, RevenueAtPromotionPeriod
Date_Dimension: Year
VEHICLE_DIMENSION: VehicleModel, VehiclePicture
BOOKING_DIMENSION: BookingType
Question 7:
RENTAL_SALES: customerKey, bookingNO, contractKey
BOOKING_DIMENSION: BookingType
CONTRACTS_DIMENSION: Rent_Start_Date
CUSTOMER_DIMENSION: CustomerLastName, CustomerFirstName
Question 8:

DATA WAREHOUSE DESIGN 17
RENTAL_SALES: customerKey, contractKey
CONTRACTS_DIMENSION: Contact_paymentmethod
CUSTOMER_DIMENSION: CustomerLastName, CustomerFirstName
1.

DATA WAREHOUSE DESIGN 18
Bibliography:
Azez, H. S. A., Khafagy, M. H., & Omara, F. A. (2015). Joum: An indexing methodology for
improving join in hive star schema. International Journal of Scientific & Engineering
Research, 6, 111-119.
Chowdhury, R., Chatterjee, P., Datta, S., & De, M. (2016, April). Pseudo mesh schema based
data warehouse architecture employing encryption request algorithm and intelligent
sensor algorithm for secured transmission and performance enhancement. In Foundations
and Frontiers in Computer, Communication and Electrical Engineering: Proceedings of
the 3rd International Conference C2E2, Mankundu, West Bengal, India, 15th-16th
January, 2016. (p. 467). CRC Press.
Chowdhury, R., Chatterjee, P., Mitra, P., & Roy, O. (2014). Design and implementation of
security mechanism for data warehouse performance enhancement using two tier user
authentication techniques. International Journal of Innovative Research in Science,
Engineering and Technology, 3(6), 165-172.
Chowdhury, R., Roy, O., Datta, S., & Dasgupta, S. (2018). Virtual Data Warehouse Model
Employing Crypto–Math Modus Operandi and Intelligent Sensor Algorithm for Cosseted
Transference and Output Augmentation. In Knowledge Computing and Its Applications
(pp. 111-129). Springer, Singapore.
Dehdouh, K., Boussaid, O., & Bentayeb, F. (2014, September). Columnar nosql star schema
benchmark. In International Conference on Model and Data Engineering (pp. 281-288).
Springer, Cham.