The CarHireOZ is an Australian company associated with the renting of vehicles. The organization has a large chain business that is distributed over the cities in Australia. The organization is having over 1000 stores across the whole of Australia. The organization lends cars, minibuses and vans and other vehicles as well. They also sell different other products as well which includes drinks, posters, and candies, travelling maps and VIP cards as well. The organization also has a website in which the customers can come and book their vehicles. The rental fees and the sales generate the main revenue of the organization. The rate of renting might vary according to the time and occasion in the country. Hence, the company maintains a database to cover the data of the system and maintain their business records.
The HQ sends the list that contains the information for the available vehicles from the different sectors of the suppliers. It also includes facilities such as supplier, availability, ratings and the vehicle category for the stores.
Additionally, VIP cards are provided to the hirers that visit the site regularly. The HQ management also analyses the usage of the VIP cards for the customers. The following classification of the customers for the system are done:
- Starting balance
- Ending balance
- Average transaction amount
- Average top-up amount
- Number of top-ups during the month
- Number of rentals during the month
The store also have a separate database that is used for storing the data of the individual shops. The individual data stores would be storing information about the sales and the rental services.
It has been known that the management is looking to perform an analysis of the system in details and hence, they are looking to implement a data warehouse that would help the organization in performing business analysis and also help them in maintaining the efficiency in the decision making system of the organization.
- Car Management
- Car Maintenance
- CarRentalOZ Inventory
- CarRentalOZ Sales
- Car rental Service
- VIP Customers services
- Membership
|
Dimensions |
||||||||||||||
|
Business Processes |
Date |
Time |
Customers |
Vehicle |
Products |
Member Class |
Booking |
Rental Contract |
Driver |
Payment |
Services |
Return Protocol |
Promotion |
Store |
|
Car Management |
X |
X |
X |
X |
||||||||||
|
Car Maintenance |
X |
X |
X |
X |
||||||||||
|
CarRentalOZ Sales |
X |
X |
X |
X |
X |
X |
||||||||
|
CarRentalOZ Inventory |
X |
X |
X |
X |
X |
X |
||||||||
|
X |
X |
X |
X |
X |
X |
X |
X |
|||||||
|
VIP Customer Service |
X |
X |
X |
X |
||||||||||
|
Membership |
X |
X |
X |
X |
For the development of the data warehouse for the CarRentalOZ the following assumptions have been made by me:
- I have assumed that all the transaction are made in Australian dollars and hence, the database would not have scope for any other type of currencies.
- I have also assumed that all customers are not members and all members are not customers and hence the members and the customers have been categorized into different classes.
- The design features will not be represented in the table and will be discussed as a part of design feature portion.
The fact granularity table provided below is used for the description of the fact granularity of each business process.
|
Business Process |
Fact Granularity |
Fact Table Type |
Description |
|
Car Management |
The granularity of the car management fact table is to provide with the information about all the vehicles in the organization and the information about the fuel consumption by the vehicles |
Periodic Snapshot table |
This table would help the organization to identify the amount of fuel consumed by their vehicle and keep track of all the vehicles in the organization. |
|
Car Maintenance |
The granularity of the car maintenance fact table is to provide the information about the date of servicing and the charge of servicing. |
Transaction fact table |
The car maintenance fact table provides the system with servicing details of the cars. |
|
CarRentalOZ Sales |
The granularity of CarRentalOZ Sales fact table is to provide the information about the total sales of the organization. |
Periodic Snapshot Table |
The CarRentalOZ Sales fact table provides the details of the sales. |
|
CarRentalOZ Inventory |
The granularity of the CarRentalOZ Inventory is to provide number of products sold and the number of products in hand. |
Periodic Snapshot Table |
The CarRentalOZ inventory provides the information about the products which are stored in the inventory of the system. |
|
Car Rental Service |
The granularity of the table is provide the service end time, start time and service charge |
Transaction fact table |
The Car Rental Service fact table provides the details of the service hired by the customers. |
|
VIP Customer Service |
The granularity of the VIP customer service table is to provide the information about the Start date, End Date , Renewal Fee andVIP services. |
Transaction Fact Table |
The VIP customer service table provides the information about the VIP services provided to the customer. |
|
Dimension Table Name |
Brief Justification |
Attribute Hierarchies |
|
Date Dimension |
The Date dimension would be providing the answers to the queries base on the day, month and year and it cane be declared as an independent dimension. |
Week day, Month, CalendarYear, Week Day Name, Week Day Type |
|
Time Dimension |
The time dimension provides the hours, minutes and seconds so that all the queries related to this dimension can be answered. |
Hours, Minutes, Seconds, AM indicator, PM indicator |
|
Return Protocol Dimension |
The return protocol provides the information such as the usage and the overdue days so that the queries related to maintenance can be answered very easily. |
Damage, Usage, Overduedays, Total Cost |
|
Member class Dimension |
The member class provides the class description life time value and the frequency that are related to the members. |
Class Description, Recency, Frequency, Life Time Value |
|
Product Dimension |
The details of the products available in the company are provided by this dimension. |
Product Name, Quantity, Price, Availability |
|
Rental Contact Dimension |
The rental contract provides the details of the rental contracts |
Insurance, Payment method, Condition, limit mileage |
|
Payment Dimension |
The payment details are displayed by this dimension |
Payment Group, Member Name, Member Class |
|
Service Dimension |
The service dimension provides the information about the service |
Service Number, Service Type, Service Engineer |
|
Store Dimension |
The store dimension provides the details of the stores of the organization. |
Store Name, Location, Pincode |
|
Promotion Dimension |
The Promotion dimension provides the details of the Promotion. |
Promo Code, Promo Name, Price increase, Start Date, End Date |
|
Driver Dimension |
The driver details are provided by this dimension. |
Last, first, birth date, address, city, state, zip, phone, license No, credit card |
|
Booking Dimension |
The booking dimension provides the details of the booking details. |
Start date, end date |
|
Vehicle Dimension |
The vehicle dimension provides the details of the vehicles of the system. |
Color, Made Year, model, mileage, vehicle Type, availability, photo |
|
Customers Dimension |
The details about the customers that answers the queries are provided in this dimenasion. |
Last, first, birth date, address, city, state, zip, Phone |
|
Design Feature |
Definition |
Evidence |
|
Junk Dimension |
The junk dimensions are the dimensions that provide the connections to the foreign key to the junk dimension. |
The Promotion Dimension can be considered as the junk dimension. |
|
Foreign Key |
The foreign key are the ones that connect the fact table with the primary key of the dimensions |
The Date Id, Time Id, store Id and the vehicle id are instances of the foreign key used in the car management fact table. |
|
Degenerate Dimensions |
The natural keys for the parent element in the fact table that represents the single transaction element is known as the degenerated dimension. |
The Date Id, Payment Id, Class Id and customer ID are the DD in the membership and VIP service table. |
|
Fact Less Fact Tables |
The fact less fact table are the ones that only contains the primary keys. |
The membership table is a fact less table. |
|
Questions |
Answers |
|
Whether the promotion was profitable? That is, whether the vehicles under promotion experienced an increase in rental during the promotional period? (Hint: need to get the original priced rentals before or after the promotions) |
Business Process: Car Management Fact Table: Car Management Fact Table Dimension: Vehicles, Promotion |
|
Which media type is most effective? That is, the profitable promotion used which media type? (Hint: the result from question one as the part of the answer to this question) |
Business Process: CarRentalOZ sales Fact Table: CarRentalOZ Sales Fact Table Dimension: Product, store, Promotion. |
|
Any stores have rented out more vehicles during the promotion on Christmas holiday? |
Business Process: Car Rental Service Fact Table: Car Rental Service Fact table Dimension: Vehicles |
|
What vehicles were on promotion but did not rent? |
Business Process: Car Rental Service Fact Table: Car Rental Service Fact table Dimension: Vehicles, Promotion |
|
Who are top 5 customers make a year out of all stores. |
Business Process: Car Rental Service Fact Table: Car Rental Service Fact table Dimension: Customers |
|
Which categories of Vehicles have made the highest profit in past year? |
Business Process: CarRentalOZ sales Fact Table: CarRentalOZ sales Fact Table Dimension: Vehicles |
|
What is the main age group of those customers renting online? |
Business Process: VIP Customer Service Fact Table: VIP Customer Service Fact Table Dimension: Customer |
|
What is the preferred payment method of customers? |
Business Process: VIP Customer Service Fact Table: VIP Customer Service Fact Table Dimension: Payment |
Déraspe, M., Binkley, G., Butano, D., Chadwick, M., Cherry, J.M., Clark-Casey, J., Contrino, S., Corbeil, J., Heimbach, J., Karra, K. and Lyne, R., 2016. Making linked data SPARQL with the InterMine biological data warehouse. In CEUR Workshop Proceedings (Vol. 1795).
Vaisman, A. and Zimányi, E., 2014. Data warehouse systems. Springer, Heidelberg.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
