Dimension Tables And Attributes In Car Rental Data Warehouse

Fact table name

Fact granularity

Brief justification

RENTAL_SALES

TotalRentAmount

This fact is for storing the amount of rent price collected for each vehicle or model in a span of time

TotalRentOfVehilceDuringPromotion

The quantity of rents made for a particular vehicle or model is stores in this fact.

TotalItemSold

The items are stored in a dimension table. This fact represents the quantity of items sold.

TotalRevenue

Revenue reflects the total business growth. This fact stores the details of the total revenue in currency form

Rent_Start_Date

The DATE_TABLE on which the rent starts

Rent_End_Date

The DATE_TABLE on which the rent ends

ADVERTISEMENT_COMMERCIAL

TotalCosOfPromotion

This fact allows the organization to find the entire cost of promotions

TotalProfitFromPromotion

The entire profit made from individual promotions are stored in this fact

TotalRentOfVehilceDuringPromotion

This fact stores the sales of vehicles based on the models

TotalProfitFromMediaCompany

The total profit made from promotions made through media company. This fact will assist in better investment

PromotionPeriod

This fact stores the time period of the promotions

Dimension table name

Brief justification

Attribute hierarchies

VEHICLE_TYPE

Vehicle type is generated solely for collecting the details of the type of vehicle. This dimension has huge impact on business as various cost of rent highly depends on the vehicle type.

typeID (PK)

description

price

VEHICLE

The vehicle dimension table is proposed for storing the data associated with every vehicle present at the organization. This dimension table holds the registration number of the vehicle and the model number also.

registrationID (PK)

colour

madeyear

model

mileage

availability

photo

CUSTOMER

Customers are source of business. They are the key of producing more profit. In order to make more money, the organization must be able to analyse the customer behaviours and other details properly. The promotions are based mostly on this dimension table

customerID (PK)

lastName

firstName

birthDate

address

city

state

zip

phone

MEMBER_CLASS

The member class has a high place in the warehouse designs. It is because, the class of the member is base of rent charge and discounts

ClassID (PK)

Classdescription

Recency

Frequency

Life_Time_Value

BOOKINGS

The bookings are made for each rent.  collecting and storing these records are very essential for the business. The bookings is for identifying whether the booking has been done online or not.

BookingNO (PK)

OnlineBooking

DRIVER

The driver details are very essential for the organization. Without the details of the drivers, the organization cannot rent a car

driverID (PK)

lastName

firstName

birthDate

address

city

state

zip

phone

licenceNO

creditcard

CONTRACTS

The contracts act as the proof of settled services and returns. This dimension allows the business to find the details of mode of payment for each rent, start and end dates of rent.

CONTRACTSID (PK)

insurance

paymentmethod

condition

limitmileage

RETURN_PROTOCOL

This dimension allows the organization to find the damages for each rent or damages a vehicle has faced during lifetime.

RETURN_PROTOCOLID (PK)

damage

usage

overduedays

totalcost

DATE_TABLE

The DATE_TABLE dimension table allows the organization to search results based on timescale

Date_ID (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

ITEMS

The details of the ITEMSs are required to determine the total revenue

ITEMSID (PK)

Type

Name

Cost

ITEMS_SALE

Total sales of items is find through this dimension table

ItemID (PK)(FK)

customerID (PK)(FK)

ITEMSName

CustomerName

STORES

The details of the stores are very essential for understanding business perspective and taking various decisions

STORESID (PK)

Name

Address

ContactNo

FaxNo

MEDIA_COMPANY

This dimension assist in finding the details of media company

CompanyID (PK)

Type

Charge

Design feature

Brief description

Brief justification

Bridge Table

The many-to-many relations are not suitable for any database. The bridge table serves as the connection between two relations in many-to-many relation and divide into two relations.

ITEMS_SALE dimension table acts as the connector between Items and Sales dimensions. The primary key of each of the two tables are the foreign key in ITEMS_SALE table.

Conformed dimension

This conformed dimensions allows the organization to find similar results for different perspectives. Such as sales amount for individual employee or a whole store

Custmer, STORE and RETURN_PROTOCOL generate similar results but for different time line or propose.

Junk Dimension

This dimension provides the answer of a query in positive or negative.

The ITEMS_SALE can be considered as a junk dimension as the item sold at least once will be recorded into this dimension. By finding the existence of an item in this table will allow the organization finding whether that particular item has even sold or not.

Mini Dimension

This dimension is for portraying the quickest change in the warehouse.

booking dimension is the first dimension that is called when a car rent process initiates. No matter what the status of booking is this table will be updated. Therefore, this dimension reflects the quickest changes.