Construct a data warehouse bus matrix

Assignment Help Database Management System
Reference no: EM13135725

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 showed a drop in rental just prior to or after the promotion thereby cancelling any gain
  • Whether the promotion was profitable
  • Whether the products under promotion experienced an increase in rental during the promotional period?
  • 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 (16 marks).

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 ?

Verified Expert

Reference no: EM13135725

Questions Cloud

How many grams of sulfur dioxide are produced : how many grams of sulfur dioxide are produced when 90.0 g of thionyl chloride reacts with excess water according to the following equation? SOCl2+H2O --> 2HCl+SO2
F the principle stakeholders influence : Write a 1-page paper that describes how each of the principle stakeholders (government, industry, and the subject) influence or contribute to the process and the outcome of a clinical trial
Developed a pattern of average collections : Assuming that you have developed a pattern of average % collections over a three or four month collection period, using quantitative or qualitative methods, do you think you can then use this % model to budget cash collections every month in the c..
Calculate the total pressure of the mixture : A mixture consists of 323 mg of methane, 165 mg of argon, and 223 mg of nitrogen. The partial pressure of nitrogen at 275 k is 19 kPa.
Construct a data warehouse bus matrix : First construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Marts and design the star schema for any Data Marts you have identified.
What unique issues does this bring up : What additional obligations do you have when it comes to donation management that does not exist concerning other types of revenue?
How much income does sean have if the benefit is provided : how much income does Sean have if the benefit is provided on a nondiscriminatory basis?
Whats is the volume in liters : To whats volume in liters must 105 mL of hydrochloric acid containing 47.5g of HCl be diluted to make a 1.05M solution ?
Prepare a memo from ringle to the head of the information : Prepare a memo from Ringle to the head of the information systems department, Sandy Klaus. In this memo, provide some suggestions for including the accounting personnel in the systems development project. Give some very persuasive arguments why ..

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd