Data Warehouse Design - PDF

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.

DATA WAREHOUSE DESIGN 13
ITEMS This data mart will allow
organization to details of
the items they sell
VEHICLE This data mart will hold
the details of the vehicle
present for rent
PRODUCT_PROMOTIO
N
This data mart will hold
the promotion related
major details
MEDIA_COMPANY This data mart will allow
the CarHireOZ to have
media company related
data

DATA WAREHOUSE DESIGN 14
DRIVER This data mart will store
the driver details
5. Data Warehouse Design Features:
Design feature Brief description Brief justification
Fact less fact
table
The
DRIVER_ASSIGN_FACT_TABL
E is the fact less fact table in the
warehouse design
This fact table only holds the
information of which driver is
assigned to which contract.
Degenerate
dimensions
SalesOrderNumber is the
degenerated dimension in the
warehouse design.
SalesOrderNumber allows the
organization to identify the rent
related data easily

DATA WAREHOUSE DESIGN 15
Role playing
dimensions
Date_Dimension and
Customer_Dimension can be
considred as roleplaying
dimensions
Both of the dimensions have same
role in every fact table they are
associated with
6. Identification of Fields:
Question 1:
PROMOTION_FACT_TABLE >> PromotionKey, MediaCompanyProfit,
PromotionSuccess
Product_Promotion_DIMENSION >> PromotionCost
Question 2:
PROMOTION_FACT_TABLE >> CompanyKey, PromotionSuccess,
Company_Key, MediaCompanyProfit
Media_Company_DIMENSION >> CompanyCharge, CompanyName
Question 3:
Car_RENT_FACT_TABLE >> DateKey, StoreKey, contractKey
STORES_DIMENSION >> StoreName
CONTRACTS_DIMENSION >> Rent_Start_Date
Question 4:
Car_RENT_FACT_TABLE >> DateKey, registrationKey, contractKey
VEHICLE_DIMENSION >> VehicleModel
Date_Dimension >> Year, Month_Name

DATA WAREHOUSE DESIGN 16
Question 5:
INVOICE_GENERATE_FACT_TABLE >> contractKey, customerKey,
DateKey, StoreKey
CUSTOMER_DIMENSION >> CustomerFirstName, CustomerLastName
Date_Dimension >> Year, Month_Name
Question 6:
Car_RENT_FACT_TABLE >> DateKey, contractKey, registrationKey,
TotalCarRent
VEHICLE_DIMENSION >> VehicleModel
CONTRACTS_DIMENSION >> Rent_Start_Date
Question 7:
INVOICE_GENERATE_FACT_TABLE >> customerKey, bookingNO
CUSTOMER_DIMENSION >> CustomerFirstName, CustomerLastName,
CustomerBirthdate
BOOKING_DIMENSION >> BookingType
Question 8:
NVOICE_GENERATE_FACT_TABLE >> contractKey
CONTRACTS_DIMENSION >> Contact_paymentmethod