Vehicle Rental Oz Data Analysis for Business Optimization
Vehicle Rental Oz, a large vehicle rental company with over 500 stores in Australia, aims to enhance business performance through detailed data analysis. By focusing on historical rental and sales data, revenue streams, and promotion revenue analysis, the company plans to optimize resource utilization and decision-making. The creation of a Data Warehouse will enable comprehensive evaluation of vehicle categories, customer classes, and revenue streams over varying time periods to support strategic growth.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
CSE 4DWD Semester 1 2014 _______________________________ Table of Contents 1. 2. 3. 4. 5. Case Study Defining the business processes Data Warehouse bus matrix Assumptions Data Marts 5.1 Vehicle Rental Income 5.2 Side Product Sales 5.3 Promotion Revenue Analysis 5.4 Revenue Analysis Aggregation 5.5 VIP Card Processing 6. Fact Granularity 7. Dimensions and the Attribute Hierarchies 8. Other design features 9. Questions and Answers from Business Management 10. Bibliography CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
Case Study Vehicle Rental Oz is a large vehicle rental company chain. It has over 500 stores all over all states in Australia. Vehicle Rental Oz lends vehicles such as cars, vans, busses and trucks. Customers can rent vehicles online, as well as from the stores. The stores also sell drinks, candies, posters, maps and VIP cards. Revenue is generated by rental fees, any overdue fees and the sales of side-items . Every week HQ sends a list with all available vehicles , including price, availability information, the ratings and categories of vehicles. The company has different methods of payment. VIP cards are different to cash / credit transactions. HQ management also analyses the usage of the cards for all customer classifications by store. Each store has a local operational database to capture their day to day rental and sales figures. They send the receipts file and the customer file to HQ every night. Management wishes to perform detailed analysis of the company s performance and have decided to build a Data Warehouse to assist their business analysis and decision making for new vehicle purchases. Rental and Sales Analysis The requirements are to see historical data and optimize human resource utilization at the POS. They need to build a monthly/quarterly/yearly top 10 list of vehicle category, per store and per suburb and also per class of customer. Revenue Streams from Rentals, Returns (Overdue Fees) , VIP Cards and Side item sales need to be analyzed. The Revenue streams will be performed using only the actual price of individual vehicles as indicated in the transaction records. The price indicates the price including any promotions. The management want to compare the different revenue streams over various time periods. The Management want to do Promotion Revenue Analysis. CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
Define the Business Processes Business Process 1 Vehicle Rental (Contract) Business Process 2 - Side Product Sales Promotion Revenue Analysis Aggregate Revenue Analysis VIP Card Processing CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
VehicleRentalOZ Data Warehouse Bus Matrix DIMENSIONS Transaction Dimension) Media Type Promotion Group Key Promotion Customer Product VIP Class Side Item Payment Location Provider Vehicle Method Vehicle Rental (Junk Store- Date Vehicle Income Rental X X X X X X X X Side Product BUSINESS PROCESSES X X X X X X X Sales Promotion Revenue X X X X X X X X Analysis Aggregate Revenue X X X X X X X Analysis Processing VIP Card X X X X X X X X CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
4) Assumptions I have made the following assumptions and my Data warehouse is built accordingly: 1) The driver and the customer are the same entity. I am assuming that VehicleRentalOz merely rents vehicles out but does not provide a chauffeur service. All customers will be their own drivers also. 2) I am assuming that all customers are local. A much bigger data warehouse would have to be built to accommodate different countries and overseas addresses. 3) I am assuming that all transactions are made in Australian Dollars and have not made provision for any other currencies. CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
(ii) Star Schema Rental Fee Income Vehicle Dimension ___________________ Payment method (Junk Dimension) ___________________ Payment Key (PK) Payment Type Payment Date Discount Surcharge DATE Dimension views for 3 roles _____________________ Date Key (PK) Day Month Year Day of week Month of year Time Vehicle Reg no (FK) Colour Made year Type ID Model Mileage Availability Condition HirecostperHour Vehicle rental (Contrsct)Fact Table __________________________ Vehicle Rental Group Dimension Minidimension ___________________ Vehicle Type Age group Price Range Gender VIP CLASS Dimension __________________ Date Key (FK) Rental Date (FK) Planned Return Date (FK) Actual return Date (FK) Customer ID (FK) Vehicle Registration no (FK) Vehicle Rental Group (FK) Take away Store ID (FK) Return Store ID (FK) Customer (FK) Promotion (FK) VIP Card (FK) Booking (FK) Payment Ked (FK) VIP Class Dimension (FK) Invoice number (DD) Contract (DD) Rental Fee Total Income in Dollars Rental Fee VIP in Dollars Rental Fee VIP discount in Dollars Overdue Fees in Dollars Cost of Operations Dollar Amount Promotion Cost in Dollars Dollar Sale Side Items Dollar Cost Side Item Damage Payment Penalty LimitMileage DistanceTavelled Total Cost Fuel level on return Customer Dimension ___________________ Customer Key (PK ) Customer ID (NK) First Last Birthdate Address City State Postal Code Telephone no VIP Card Class (FK) Return Protocol Drivers License number Vehicle Rental Group Key(PK) VIP Class Key (PK) Classifiication Value Spend Store Dimension views for 2 roles ___________________ VIP Customer demographics (Outrigger) --------------------------------- - Neighborhoods Store Key (PK) Store ID (NK) Store address Store suburb Store State Promotion Dimension (causal dimension)- __________________ _ VIP card Dimension ______________ VIP card_ID (PK) Card Balance Starting Card Balance Ending Average Transaction amount Average Top up amount Number top ups in month Number Rentals during month Protocol (factless fact table) --------------------------------- - Protocol ID Contract (DD) Promotion Key (PK) Promotion Name Price reduction type Promotion Media Type Ad Type Display Type Coupon Type Ad media Name Display Provider Promotion Cost Promotion Begin date Promotion End date Booking Dimension Factless Fact table ________________ Booking_no Key (PK) Booking Type desciption Registration ID Customer ID Start Date End Date Return Protocol (Factless Fact table) --------------------------------- - Return Protocol ID Protocol ID CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
(ii) Star Schema Sales OTHER ___________________ Dimension) ___________________ Payment Key (PK) Payment Type Payment Date Discount Surcharge Payment method (Junk DATE Dimension _____________________ Product Dimension ___________________ Date Key (PK) Day Month Year Time Product Key (PK) Product Description Category Description VIP CLASS Dimension __________________ VIP Class Key (PK) Classifiication Value Spend Customer Dimension ___________________ Customer Key (PK ) Customer ID (NK) Name Address Birthday Telephone no VIP Card Class (FK) Item Sales- Fact Table ______________________ Transaction type ___________________ Date (FK) Store (FK) Product (FK) Customer (FK) Promotion (FK) VIPClass(FK) Transaction type (FK) Payment method (FK) POS Transaction number (DD) Sales Other Income in Dollars Cost Other in Dollars Transaction type (PK) Transaction description VIP Customer demographics (Outrigger) --------------------------------- - Neighborhoods Store Dimension ___________________ Store Key (PK) Store ID (NK) Store address Store suburb Store State CSE4DWD Assignment ,Semester 1 2014 CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167 Monika Theilig 17656167
(iii) Star Schema - Promotion Revenue Analysis Vehicle Dimension ___________________ DATE Dimension _____________________ Vehicle Key (PK) Vehicle Registration ID Colour Made year Type ID Model Mileage Availability Condition Date Key (PK) Day Month Year Time Promotion Fact Table __________________________ Date Key (FK) Vehicle Key (FK) Store Key (FK) Promotion Key (FK) Media Type (FK) Promotion Provider (FK) Transacion Type (FK) Rental Revenue Dollar Amount Cost Dollar Amount Gross Profit Dollar Amount Media Type ___________________ Media Type Key(PK) Vehicle Type Store Dimension ___________________ Store Key (PK) Store ID (NK) Store address Store suburb Store State Promotion Dimension ___________________ Promotion Provider ___________________ Transaction Dimension ___________________ Promotion Provider (PK) Name Address Promotion Key (PK) Promotion Name Price reduction type Promotion Media Type Ad Type Display Type Coupon Type Ad media Name Display Provider Promotion Cost Promotion Begin date Promotion End date Transaction type (PK) CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
(iv) Star Schema Aggragate Revenue Analysis Vehicle Dimension ___________________ Product Dimension ___________________ DATE Dimension (multiple views) _____________________ Date Key (PK) Day Month Year Time Fiscal Year Month Fiscal Quarter Fiscal year Vehicle Key (PK) Vehicle Registration ID Colour Made year Type ID Model Mileage Availability Condition Foto Product Key (PK) Product Description Category Description Vehicle Rental Group Dimension Minidimension ___________________ Vehicle Type Age group Price Range Gender Vehicle Rental Group Key(PK) Aggregate Revenue Snapshot Fact __________________________ Store Dimension ___________________ Month end Date Key (FK) Year end Date Key (FK) Customer ID (FK) Vehicle ID(FK) Vehicle Rental Group Key (FK) Product Dimension (FK) Promotion (FK) Store (FK) VIP Card (FK) Transaction Type FK Actual PriceVehicle Rental Total Income in Dollars Income Side Items in Dollars Cost Side Items in Dollars Rental Fee VIP in Dollars Rental Fee VIP discount in Dollars Overdue Fees in Dollars Cost of Operations Dollar Amount Promotion Cost in Dollars Damage Payment Penalty Store Key (PK) Store ID (NK) Store address Store suburb Store State Customer Dimension ___________________ Customer Key (PK ) Customer ID (NK) First Last Birthdate Address City State Zip Telephone no VIP Card Class (FK) Return Protocol Drivers License number Transaction Dimension ___________________ Tranaction Type (PK) VIP card Dimension ______________ VIP card_ID (PK) Card Balance Starting Card Balance Ending Average Transaction amount Average Top up amount Number top ups in month Number Rentals during month CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
(v) Star schema VIP Card Processing Vehicle Dimension ___________________ DATE Dimension _____________________ Vehicle Key (PK) Vehicle Registration ID Colour Made year Type ID Model Mileage Availability Condition Foto Date Key (PK) Day Month Year Time VIP Cards Fact Table __________________________ Vehicle Rental Group Dimension Minidimension ___________________ Vehicle Type Age group Price Range Gender Customer Dimension ___________________ Date Key (FK) Customer ID (FK) Vehicle Dimension (FK) Vehicle Rental Group (FK) VIP Card (FK) VIP Class (FK) Transaction Type Dimension (FK) Dollar Amount opening balance Dollar Amount added Dollar Amount subtracted Dollar Amount Closing Balance Vehicle Rental Group Key(PK) Customer Key (PK ) Customer ID (NK) Customer Location (suburb) First Last Birthdate Address City State Zip Telephone no VIP Card Class (FK) Return Protocol Drivers License number Store Dimension ___________________ Store Key (PK) Store ID (NK) Store address Store suburb Store State VIP CLASS Dimension __________________ VIP Class Key (PK) Classifiication Value Spend VIP card Dimension ______________ VIP card_ID (PK) Card Balance Starting Card Balance Ending Average Transaction amount Average Top up amount Number top ups in month Number Rentals during month VIP Customer demographics (Outrigger) --------------------------------- - Neighborhood Transaction Dimension _____________________ Transaction Type (PK) CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
iii)Granularity FACT Table Fact Granularity Brief Justification Vehicle Rental Transaction Vehicle Rental Transaction Fact table is used to register the Contract for the customer ID. Each vehicle Per Rental Analysis per vehicle per month Side Product - Sales One Item of sale One Item per sale Analysis Side product sales per month Promotion Revenue Analysis One promotion transaction in dollars One Promotion Sale Promotion Analysis Aggragate Revenue Analysis One row for each account One row for each account at the end of each month Revenue Analysis VIP Card Processing One VIP Card Transaction One row per VIP card transaction VIP card Analysis
iv) Dimensions Dimension Table name Brief Justification Attribute Hierarchies Date Dimension 3 views of the Date Dimension are necessary to show the Rental date, Planned return date and actual return date. Day, Month Year Time Product Side item Dimension The side item dimension will list all the products other than vehicles that the company sells to make revenue. Description, Category Vehicle Dimension This dimension needs to describe the vehicle and provide all its details, including condition, model, year of manufacture etc Vehicle Reggo, Colour, Made Year, Type, Model, Mileage, Availability, condition, Vehicle Rental Group Dimension (Minidimension) This dimension needs to show what group or range of vehicle the vehicle falls into Vehicle type, Age group, price range, gender Store Dimension This gives all the information regarding the store Number, Street, Suburb, City, Code , State Customer Dimension This gives all the information regarding the customer/driver of the vehicle First name, last name, birthdate, address, tel no, VIP card class, Drivers License no, Insurance Promotion Dimension This will give all the details regarding the promotion Promotion name, price reduction type, Ad type VIP Class Dimension This Dimension shows the classification of the customers VIP cards eg Gold, Platinum, Silver Classification, Value spend VIP Class Demographics Dimension (Outrigger) Payment method (Junk Dimension) This shows how the customer will be paying for the rental of the vehicle or product. Cash, Cr card etc Payment type, Date, Discount, Surcharge Media Type Dimension This shows what media type will be used for the promotion of the vehicle Media type , vehicle Promotion Provider Dimension This will tell us who the promotion provider was Name, address, description CSE4DW Assignment ,Semester 1 2014 Monika Theilig 17656167
v) Design Features Used Design Feature Brief Description Brief Justification Factless Fact table Use a factless fact table for Booking. A factless fact table has no metrics. There are no table rows with zero facts .By putting this in the fact table it would make the table doing so would make the table too large. Factless Fact table Use a factless fact table for Protocol and Return Protocol. A factless fact table has no metrics. There are no table rows with zero facts .By putting this in the fact table it would make the table doing so would make the table too large. Degenerate Dimension for Sales of items such as drinks , posters, candies, maps, VIP cards etc Also for Invoice numbers generated for clients. POS Transaction number keyand Invoice number Key in the Fact table stripped of all its descriptive items that might otherwise fall in a POS transactiond dimension. Since the dimension is empty we refer to the POS transaction number as a degenerate dimension (DD). The grain is a single transaction or line item because the DD represents the Unique Identifier of the parent. Surrogate Keys Every joint between fact tables and Dimension tables in the DW should be based on meaningless integer surrogate keys. Surrogate keys are like an immunisation (flu shot) for the DW. They buffer the DW environment from buffer changes. Surrogate keys can also record conditions that have no operational codes, such as No promotion in effect Surrogate keys are very important for the date dimension eg January 1 = surrogate key 1. Outrigger for Customer Demographics The customers are from different neighborhoods. Rather than repeating this large block of data for every customer within a neighborhood we opt to model it as an outrigger. Junk Dimension for Payment method We create a separate Junk Dimension for the payment type used by customers. We remove these indicators from the order fact table and place them in a single dimension. (Use a surrogate key for the foreign keys) An abstract dimension with the decodes for a group of low-cardinality flags and indicators, thereby removing the flags from the fact table. Minidimension for VIP Class The VIP class is a dimension that would be changing frequently. Customers would be going from solver to gold and back down from gold to silver etc. A separate minidimension is created . Continuosly variable attributes such as top up amounts and rental amount would be in the minidimension. To address both the performance and change tracking challenges we break off frequently changing attributes into a separate dimension called a minidimension. The changes are also captured. There are different dates when the vehicle was rented out, the planned return date an the actual return date. 3 Different views are needed Role playing occurs when a single dimension appears simultaneously several times in the same fact table. The dimension exists as a single physical table, but each of the roles should be presented in a separate labelled view. Dimension Role Playing for Date
v) Design Features Used (continued) Design Feature Brief Description Brief Justification The vehicles will be rented in one store but can be returned in another store. 2 different views are needed. Role playing occurs when a single dimension appears simultaneously several times in the same fact table. The dimension exists as a single physical table, but each of the roles should be presented in a separate labelled view. Dimension Role Playing for Store No Promotion In Effect No promotion took place A null key will be used . A single row will be used in the promotion dimension with its own unique key to identify No promotion in effect , and avoid a null promotion key in the fact table. No Foreign Key Handling A null key will be used . A single row will be used in the Customer dimension with its own unique key to identify Non Customer purchases. No Foreign Key Handling Non Customer Purchases Somebody purchased a product from a store but was not a customer. Customer information will change over time. A Type 2 slowly changing dimension will add a new row to the table. A type 2 response is the primary technique for accurately tracking slowly changing dimension attributes. The new dimension row automatically partitions history in the fac ttable. Type 2 Slowly Changing Dimension CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
Identify which fields from your Fact / Dimensions are required to answer each of the following business question: 1) Whether the promotion was profitable? That is , whether the products under promotion experienced an increase in rental during the promotion period ? You would look at your Rental Fee Income in dollars for the period between the Promotion Begin Date and the Promotion end date and subtract the Promotion cost as well as the Cost of Operations for that same period. 2) Did any stores rent out more Vehicles during the Promotions? Does this vary across different months or event types? You would look at your Take Away Store ID and analyze the highest Rental Revenue Income per month for each event during the Promotion Begin date and the Promotion End date. 3) What products were on promotion but did not sell? You would look at the Product category and the Promotion Begin and end date Dollar Sale Side Item. The Items with zero or very low sales would answer your question. 4) For which customers have we provided the most products? How much do we make a year out of our top 5 customers? You would look at the customer, the product, the vehicle and the the Total Revenue Income per customer 5) Which categories of Vehicles have made the highest profit? You would look in the Vehicle Rental Group Dimension under Vehicle type and then Subtract the Cost from the Total Income per vehicle type. 6) What is the main location of those people (customers) renting online? What event types are they attending? You would look at the booking type description in the Booking factless fact table and also their demographics in the customer demographics outrigger. You would also look at the Promotion name in the Promotion dimension CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
Identify which fields from your Fact / Dimensions are required to answer each of the following business question: Continued 8) Whether the products under promotion showed a drop in rental just prior to or after the promotion thereby cancelling any gain. You would have to look at the Rental Fee income and analyze the dates between the Promotion Begin date and the Promotion End date. 9) How much do we make on overdue fees in last 5 years? You would have to analyze the Overdue Fees in Dollars as well the the Aggregate Revenue in Dollars fact per year and add the last 5 years together. How much do we make on rental in the last financial year? How does this compare to the past 2 years? You would look at the Aggregate Revenue for Rental and the Aggregate Revenue for your side products and add them together for the current year, You would then compare these figures with the history figures for the last 2 years as per your date dimension. CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
10) Bibliography Dr Jinli Cao Lecture Notes Kimball , Ross, The Data Warehouse Toolkit,2002, second edition Various Internet websites CSE4DWD Assignment ,Semester 1 2014 Monika Theilig 17656167
