CSE5DWD Data Warehouse Concepts And Design

Fact table name

Fact granularity

Brief justification

Car_RENT_FACT_TABLE

TotalCarRent

This fact holds information of the totl amount of car rented in a 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_TABLE

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 items

INVOICE_GENERATE_FACT_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_TABLE

PromotionCost

This fact holds information of the cost of promoting the business

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

Dimension table name

Brief justification

Attribute hierarchies

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  

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.

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_PROMOTION

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

DRIVER

This data mart will store the driver details

Design feature

Brief description

Brief justification

Fact less fact table

The DRIVER_ASSIGN_FACT_TABLE 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

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