VehicleRentalOZ Data Warehouse
VehicleRentalOZ is a large chain of vehicle rental company over 500 stores distributed over all states in Australia. VehicleRentalOZ lends vehicles such as cars, vans and buses, trucks. Customers can rent products online via the VehicleRentalOZ website, as well as from rental stores. The stores also sell drinks, candies, posters, traveling maps and VIP cards etc. The revenue of the company is generated by rental fees, possible overdue fees (collected as part of returns). Rental price may vary between sessions, promotion periods or special events. The company has an operational database for the company.
The database describes the following data:
- Each vehicle is identified by a registration number, the colour of the vehicle, the year of made, the model, mileage, type of the vehicle: family, small, four-wheel drive, truck, bus etc. There is also a state for the availability of the vehicle and the rental price (according to the type of the vehicle).
- Each customer is described by a customer ID, a name, an address and birthday, telephone when booking a vehicle.
- A booking record includes a booking number, type of the car and customer's information.
- A contract record is made when the customer collecting the vehicle. The contract stipulating the rental period, the rental rate, the is-state and vehicle insurance policy, so on. The driver's license, the registration number of the vehicle, the driver's credit card number and payment method must be included in the contract.
- The data about each returning of vehicle will be recorded. The contract file will be read out when the driver returns the vehicle. Fill in all items for the state of returned vehicle to check any damage, the fuel, insurance Status, kilometre usage, the rental extension or earlier return etc. The final payment including damage or extension penalty will be calculated.
- The sales of the drinks are also recorded.
Every week, HQ sends a central list with all available vehicles from the different suppliers, including price, availability information, the ratings and categories of Vehicles.
The VIP Cards transactions are different than pay cash/credit transaction.
- Each time a Vehicle is rented, a certain amount (5% less than the regular rental price) is deducted from the card balance.
- Customers can put more money on their cards with top-ups to increase the card amount by reloading.
HQ management also analyses the usage of the cards for all customer classifications by store.
They want the following information about each card (monthly figures):
- Starting balance
- Ending balance
- Average transaction amount
- Average top-up amount
- Number of top-ups during the month
- Number of rentals during the month
Each store has a local operational database to capture their day-to-day rental and sales and sends the following files to headquarter (HQ) at middle night of every day:
- A receipts file containing all the individual receipts with their rental items.
Every day, the detail rental items are incorporated into the summary rows for the current month
- A customer file containing
- A list of all customer data captured by stores when new customers apply for membership as well as changes that existing customers communicate (such as change of address or changes in family composition).
- The identification of the class they are currently assigned to.
- Customers are assigned to classes (Platinum, Gold, Silver, Regular) based on all sorts of personal (age, occupation, and so on) and demographics information about the neighbourhoods people live in. Classes are identified by a number of thresholds (LOW and HIGH values) for the following three attributes:
1. Recency: How long ago did the customer visit one of our stores.
2. Frequency: During the x last months, how often did the customer visit us?
3. LTV (Life Time Value): What is our estimation of the potential business this customer could generate over the next x months?
Management wishes to perform detailed analysis of their company's performance and have decided to build a data warehouse to assist their business analysis and decision making for new vehicle purchase.
Rental and Sales Analysis:
Requirements were gathered from all interested business departments as follows:
- Historical analysis of rentals and sales volumes
- Historical analysis of rentals and sales profit (revenue, costs)
- Optimize human resource utilization at the POS (Point Of Sale= Cashier's desk)
Therefore, it is required to build a monthly/quarterly/yearly top 10 list of
- Categories of Vehicles (Eg., family, small, four-wheel drive, truck, bus)
- Per individual store and per suburb of stores (for example, Bundoora, Melbourne).
- Per class of customers (Regular, Gold, Silver, ...) or across the entire customer base
The business questions are often asked as follows:
- What categories of Vehicles are less popular?
- What Vehicle categories most often rent to full capacity? What are the best ratios of the different Vehicle categories for sustained profit maximisation?
- Are there certain times of the year when more events are held? Are certain types of events more common at particular times of the year?
- What is the main location of those people (customers) renting online? What event types are they attending?
Revenue analysis:
In order to get a complete picture of the revenue this business is generating, the following revenue streams must be analysed:
- Rentals
- Returns (overdue fees)
- VIP cards
- Sales Item sales.
The revenue streams will be performed using only the actual price of individual vehicles as indicated in the transaction records. That price indicates the price including any promotions.
The management compare the different revenue stream:
· How much do we make on overdue fees in last 5 years?
- How much do we make on rental in the last financial year? How does this compare to the past 2 years ?
- For which customers have we provided the most vehicles? How much do we make a year out of our top 5 customers?
- Which categories of Vehicles have made the highest profit?
Promotion Revenue analysis:
In order to compute the effectiveness of the promotion, several factors have to be considered:
- The total cost of the promotion
- The rental revenues generated for the targeted vehicles (lift)
- The impact on revenues for other vehicles within the same category during that same period (cannibalization and/or upsell)
The need to analyze rental/sales revenue that includes the promotions were applied for the Vehicles that were rented. The Rental and Sales records contain information about which promotion affected the actual price of the rented or sold item.
The promotions can be decided centrally or locally, depending on the cases. Each promotion is specified with a promotion_id and the applied state. It is then the responsibility of the store manager to apply promotions during the sales/rental activities. Promotion files contain information about the different promotions.
- Subject (on which objects)
- Media campaign type (for example, TV Ad, Coupon, Local Newspaper Ad)
- Start and end dates (that is, for seasonal promotions)
- Overall cost Vehicle
Vehicles will refer to the applied promotions using the promotion_id.
The subject of the promotion describes its scope (which products are affected
- a list of specific Vehicles (for example, all 4WD Vehicles),
- a specific category of Vehicles (all van Vehicles during Xmas)
- and which customers may benefit from it
- all customers or a specific class (Gold and Platinum)).
The management is looking for determining whether the promotion was effective:
- Whether the products under promotion experienced an increase in rental during the promotional period?
- Whether the products under promotion showed a drop in rental just prior to or after the promotion thereby cancelling any gain
- Whether the promotion was profitable
- Which stores have rented out more Vehicles during the promotions? Does this vary across different months or event types?
- What products were on promotion but did not sell?
Your Task:
Your task is to design a data warehouse for VehicleRentalOZ using Star Schema/Snowflake Modelling. Your design needs to encompass the following steps:
(i) First construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Marts.
(ii) Design the star schema for any Data Marts you have identified, ensuring your Dimensions are conformed, primary and foreign keys are clearly labelled, and that your attributes are named using verbose textual descriptions.
(iii) Create the following table with a row for each fact table in your design, indicating the granularity of each fact and a brief justification for choosing that granularity.
|
Fact table name
|
Fact granularity
|
Brief justification
|
|
|
|
|
(iv) Create the following table with a row for each dimension table in your design, giving a brief justification for choosing that dimension, and indicating any attribute hierarchies that exist within the dimension.
|
Dimension table name
|
Brief justification
|
Attribute hierarchies
|
|
|
|
|
(v) Create the following table with a row for each design feature you have used, such as handling of possible null foreign keys, and the inclusion of any fact-less fact tables, degenerate dimensions, role playing dimensions, junk dimensions, outriggers, mini-dimensions, or any other design techniques discussed in the lectures. Provide a brief description of each design feature used (how and where it is used - not the theory behind the concept) and a justification for its use.
|
Design feature
|
Brief description
|
Brief justification
|
|
|
|
|
(vi) Identify which fields from your facts/dimensions are required to answer each of the business questions listed below.
- Whether the promotion was profitable? That is, whether the products under promotion experienced an increase in rental during the promotional period?
- Any stores have rented out more Vehicles during the promotions? Does this vary across different months or event types?
- What products were on promotion but did not sell?
- For which customers have we provided the most products? How much do we make a year out of our top 5 customers?
- Which categories of Vehicles have made the highest profit?
- What is the main location of those people (customers) renting online? What event types are they attending?
- Whether the products under promotion showed a drop in rental just prior to or after the promotion thereby cancelling any gain
- How much do we make on overdue fees in last 5 years?
- How much do we make on rental in the last financial year? How does this compare to the past 2 years ?
