Answer:
Fact Table, Granularity and Justification against Granularity:
|
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
|
Justification and Attribute Hierarchy of the Dimension Tables:
|
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
|
|
Data Warehouse Design Features:
|
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
|
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
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
Bibliography:
Bates, E. (2015). UVM Big Data? Aggregating Campus Databases and Creating a Data Warehouse to Improve Student Retention Rates at the University of Vermont.
Dedic, N., & Stanier, C. (2016). An evaluation of the challenges of multilingualism in data warehouse development.
Khojah, M., & Mannino, M. (2017). Mastering Data Warehouse Maturity Concepts Using a Serious Game: Design and Implementation of Emerge2Maturity. In Proceedings of the EDSIG Conference ISSN (Vol. 2473, p. 3857).
Kraus, C., & Valverde, R. (2014). A data warehouse design for the detection of fraud in the supply chain by using the benford’s law. American Journal of Applied Sciences, 11(9), 1507-1518.
Mireku Kwakye, M. (2017). Modelling and Design of Generic Semantic Trajectory Data Warehouse. Science.
Vaisman, A., & Zimányi, E. (2014). Data warehouse systems. Springer, Heidelberg.
