Designing A Data Warehouse For CarHireOZ Using Star Schema/Snowflake Modelling, An Essay.

Introduction to CarHireOZ

CarHireOZ is a large chain of vehicle Rental Company over 1000 stores distributed over all cities in Australia. CarHireOZ lends vehicles such as cars, vans, minibuses ect. Customers can book vehicles online via the CarHireOZ 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. 

Every month, the Headquarter (HQ) sends a central list with all available vehicles from the different suppliers, including price, availability information, the ratings and categories of vehicles to all stores. 

The company offers VIP Cards to those frequent vehicle hirers. VIP Cards can be used for the payment of hires.

• VIP card holders can be awarded for a certain amount discount when a vehicle is hires and paid by VIP card. The discount varies depending on the membership classes between 5% and 10% of the regular rental
• Customers can recharge money on their cards with top-ups to increase the card 

HQ management also analyses the usage of the VIP cards for all customer classifications by stores. 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 system to capture their day-to-day rental and sales. Those stores record the following data into their databases:

• A receipts file containing all the individual receipts with their rental vehicles.
• 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 the contact information).
  • The identification of the class they are currently assigned

- Customers are assigned to classes (Platinum, Gold, Silver, Regular). 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? 

• Each vehicle is identified by a registration number, the colour of the vehicle, the year of made, the model, mileage, category 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 category or model of the vehicles). 

• Each customer is described by a customer ID, a name, an address and birthday, telephone when booking a vehicle. The customer is initially assigned to a Regular class. 

• A booking record includes a booking number, type of the car and customer’s

• A rental contract record is made when the customer collecting the vehicle. The contract stipulates 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 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 overdue or earlier return etc. The final payment including damage or overdue penalty will be calculated. 

• The inventory of drinks & candies are also recorded in the database 

Management wishes to perform detailed analysis of their company's performance and has decided to build a data warehouse to assist their business analysis and decision making for new vehicle purchase and good marketing. The business analysis are processed as follows: 

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

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 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 the customers hiring cars mostly for private use or business travel?
• Are there certain times of the year we have more vehicles been hired?
• What is the main location of those people (customers) renting online? Which age group hires the most vehicles?
• How many percentage of customers make payment using cash?
• Have those promotions in March attracted more customers? Any of those promoted cars have not been hired at all?
• How much do we make on rentals in the last financial year? How does this compare to the past 2 years?
• How much do we make on overdue fees in last 5 years?
• Which categories of vehicles have made the highest profit?
• Which of those selling items are popular with highest sales?
• Whether the vehicles under promotion experienced an increase in rental during the promotional period?
• Whether the vehicles under promotion showed a drop in rental just prior to or after the promotion thereby cancelling any gain
• Whether the promotion was profitable
• Any of those promoted cars in the period of December holiday did not get hired at all? What are their categories?
• What sale items in store did not sell at all?

• How many 4WD have left in Bundoora store?
• Are those small cars short in City store?
• Do we often transfer family cars from city to airport store?
• Which stores have stocked more than they needed?

• What is the average amount of VIP recharge monthly?
• How many percentage of customers are using VIP card to pay their car hires?
• Which class contains the largest number of customers among Platinum, Gold, Silver and Regular? Therefore, managers can work out the discount 

In order to compute the effectiveness of the promotion, several factors have to be considered:

• The total cost of the promotion for media campaigns
• 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) 

Therefore, the need to analyze rental/sales revenue that includes the promotions were applied for the Vehicles that were rented. We can get the total Rental and Sales records about which promotion affected the actual price of the vehicles and sold items. The rental may increase the sales and revenue during the promotions. Each promotion is specified with a promotion_id, the applied period to what categories of vehicles and the percentage of original listed price. 

On the other hand, the promotions have costs associated to media campaigns. That is, the cost on each promotion with associated media campaigns should be measured. The managers need to look at what is the cost of each media campaign type (for example, TV Ad, Coupon, Local Newspaper Ad or internet etc) and whether they are effective to increase their car hires and produce higher business benefits. The executives want to compare the benefits of the different media advertisements. They also identify which media provider can offer good deals to CarHireOz company. 

The promotions can be decided centrally or locally, depending on the cases. It is then the responsibility of the store manager to apply promotions during the sales/rental activities. The subjects of the promotion describes its scope (which products are affected), the stores, the period, the applied customer group, and the involve media companies. For example:

• a list of specific Vehicles (for example, all 4WD Vehicles),
• a specific category of Vehicles (all van Vehicles during Xmas)
• the groups of customers may benefit from it, Eg., all customers or a specific class (Gold and Platinum)).
• Media companies involved for the promotions

• Need to get the total rental of each promotion involved vehicles –from sale transactions fact.
• Need to get the total cost of each promotion associated media companies
• Were any promotion campaigns successfully achieved great profit?
• Which media is most beneficial across different promotion media types?

Hint: the number of media types are not fixed to each promotion campaign. You need to use one of techniques you learned in the lecture for solving many to many relationship between the fact table and the dimension table. 

Your task is to design a data warehouse for CarHireOZ using Star Schema/Snowflake Modelling. Your design needs to encompass the following steps: 

• First construct a Data Warehouse Bus Matrix to identify the company's business processes and any likely Data Explain each business process with few sentences. 
• Design the star schema for all 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. List 3 rows of each fat table to demonstrate your correct measures to the grain of the business process you have modelled. Your design must be able to answer all questions and requirements in the aforementioned descriptions. 
• 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

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

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, bridge dimensions or any other design techniques discussed in the 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

6. Identify which fields from your facts/dimensions are required to answer the following selected some business questions. (10 marks) (Notice: your design should be able to answer all questions in the aforementioned case ) 

1. 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)
2. 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)
3. Any stores have rented out more vehicles during the promotion on Christmas holiday?
4. What vehicles were on promotion but did not rent?
5. Who are top 5 customers make a year out of all
6. Which categories of Vehicles have made the highest profit in past year?
7. What is the main age group of those customers renting online?
8. What is the preferred payment method of customers?

Business processes

Dimensions

Veichle_Type

VEHICLE

CUSTOMER_INFO

Member_Info_Class

BOOKINGS

DRIVER_INFO

CONTRACTS

PROCEDURE_FOR_RETURN

DATE_INFO_Table

Promotional_Products

ITEMS

ITEMS_SAle

STORE_INFOS

Promotion_Company

Rental and Sales

X

X

X

X

X

X

X

X

X

X

X

X

Advertisement_Commercials

X

X

X

X

X

X

X

X

Introduction to CarHireOZ

The two main business processes are the “Rental and Sales” and “Advertisement Commercials”. The first one deals with the sale of the products of the company and the rent services of the cars provided by the company. This business process is the very important as it is directly associated with the revenue generation of the company. In order to promote the business of the company they are using some promotional activities through different media companies. These operations are associated with the second business process “Advertisement Commercials”. The database dimensions tables are mentioned in the first row of the matrix given in the above section. In the second row and third row the two business processes have been mentioned and cross sign in the cells of the matrix is showing the association of the dimensions with the business process of the company.

                                                                                   

                                                                                     

The star schema diagram for the previous mentioned two business cases are given in the two diagrams in the above section. The tables in the diagrams are representing the entities that will be design in the database and the rows of the tables are indicating the attributes of the entities.

Fact table name	Fact granularity	Brief justification
RENTAL_SALES	Rent_Amount_In_Total	Total amount of the rents over a specific time period is stored in this fact.
	RentAmount_During_promotion	At the time of promotion, the amount of the rents is stored in this fact.
	Products_Sold	The products that are sold by the stores are stored in this dimension.
	Total_Revenue_Earned	The total amount of revenues generated from the business is stored in this fact.
	Rent_Date	This fact is used for storing the date on which a car is rented by a customer.
	Return_Date	This fact stores the date of returning a rented car by customer.
ADVERTISEMENT_COMMERCIAL	Promotional_Cost	The amount of money required for the advertisements is stored in this fact.
	Promotional_Profit	The amount of profit made by utilizing the promotional activities is stored in this fact.
	RentAmount_During_promotion	Number of cars during the time period of promotional activities is stored in this fact by car models or categories.
	Profit_From_Promotion_Company	The total profit made from promotions made through media company. This fact will assist in better investment
	Duration_Of_Promotion	This fact is for storing the time period of each of the promotional activities.

Dimension table name	Brief justification	Attribute hierarchies
VEICHLE_TYPE	There are different types of the vehicles in the company. The details of each types of cars will be stored in this table.	typeID (PK) description price
VEHICLE	This table is for string the details about each of ten cares present in the company.	registrationID (PK) colour Manufacturing_Year model mileage availability photo
CUSTOMER_INFO	Details of the customers is required for the company. The name, address and other required details are stored in this table, where each and every customers can be identified by the unique customer id.	Customer_ID (PK) Last_Name First_Name birthDate address city state zip phone
MEMBER_INFO_CLASS	The details about the members i.e., the customers who have the membership for renting the cars of the company are stored in this table.	ClassID (PK) Classdescription Recency Frequency Life_Time_Value
BOOKING_DETAILS	Online booking facility is available for renting the cars. The details of the customers and the renting cars will be stored in this table.	BookingNO (PK) Net_booking
DRIVER_INFO	The Driver_Info details are very essential for the organization. All the required details with the contact numbers of the drivers will be stored in this table.	DriverID (PK) Last_Name First_Name birthDate address city state zip phone licenceNO creditcard
CONTRACTS	This table is for storing the details regarding each of the contracts regarding the rent of the sale of the cars with the customers. The insurance details and the damages will also be stored in this table.	CONTRACTID (PK) insurance paymentmethod condition limitmileage
PROCEDURE_FOR_RETURN	The return details with any type of damages and usage details will be stored in this table.	PROCEDURE_FOR_RETURNID (PK) damage usage overduedays totalcost
DATE_INFO	The DATE_INFO dimension table allows the organization to search results based on timescale	Date_ID (PK) Date Day DayOfWeek Month Year Quarter
PROMOTION	This table is for string the details regarding the types and costs of the promotional activities.	PromotionID (PK) Type Cost
ITEMS	The details of the ITEMSs will stored for identifying the income of the company from each of the items.	ITEMSID (PK) Type Name Cost
ITEMS_SALE	Total sales of items is find through this dimension table	ItemID (PK)(FK) Customer_InfoID(PK)(FK) ITEMSName Customer_InfoName
STORE_INFO	This table is for storing the details regarding  the stores of the company like name, address and contact details.	STORE_INFOID (PK) Name Address ContactNo FaxNo
PROMOTION_COMPANY	This table is for storing the details regarding the advertising organizations.	CompanyID (PK) Type Charge

Design feature	Brief description	Brief justification
Bridge Table	In this case, many to many relationship is not enough for presenting the schema. Therefore bridge table connection is used.	Items_For_Saledimension table acts as the connector between Items and Sales dimensions. The primary key of each of the two tables are the foreign key in Items_For_Saletable.
Conformed dimension	This is used for showing the relationship among the tables from different perspectives.	Customer_Info, Store_Info and PROCEDURE_FOR_RETURNgenerate same types of outcomes but for different time line or propose.
Junk Dimension	Positive or negative answers from the queries cane be gathered through this dimension.	The Items_For_Salecan be considered as a junk dimension because when the items is sold out, it will still hold the data in the database. This will help to identify whether an item is in stock or sold out by the organization.
Mini Dimension	The little changes in the database will be reflected through this dimension.	Online_Bookingis this type of dimension where the database will always updated regardless the condition of the booking status.

1:

• Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion, Total_Profit_from_Promotion, registrationID, DATE_ID
• DATE_INFO: Day, Month
• VEHICLE: model

 2: 

• Advertisement_Commercials: PromotionID, Total_Cost_of_Promotion, Total_Profit_from_Promotion, registrationID, DATE_ID, CompanyID
• DATE_INFO: Day, Month
• PROMOTION_COMPANY: CompanyID

3: 

• RENTAL_SALES:Total_Rent_Amount, Total_Rent_of_Vehilce_During_Promotion
• DATE_INFO: DATE, Month
• BOOKINGS: BOOKINGSNO

4: 

• RENTAL_SALES: Total_Rent_of_Vehilce_During_Promotion, Rent_Date
• DATE_INFO: DATE, Month
• BOOKINGS: BOOKINGSNO

5: 

• RENTAL_SALES: Customer_InfoID, STORE_INFOID, RegistrationID, CONTRACTSID
• DATE: Year
• CUSTOMER_INFO: Last_Name, First_Name

6: 

• RENTAL_SALES: registrationID, returnprotocolid, Total_Rent_Amount
• DATE_INFO: Year
• VEHICLE: model
• BOOKINGS: BookingNO

7: 

• RENTAL_SALES: Customer_InfoID, BOOKINGNO
• BOOKINGS: Net_bookingS

8: 

• RENTAL_SALES: Customer_Info_Key, CONTRACTID
• CONTRACTS: paymentmethod

Abai, N.H.Z., Yahaya, J.H. and Deraman, A., 2013. User requirement analysis in data warehouse design: a review. Procedia Technology, 11, pp.801-806.

Ahmed, T., Pedersen, T.B. and Lu, H., 2013, June. A data warehouse solution for analyzing rfid-based baggage tracking data. In Mobile Data Management (MDM), 2013 IEEE 14th International Conference on (Vol. 1, pp. 283-292). IEEE.

Alhyasat, E.B. and Al-Dalahmeh, M., 2013. Data warehouse success and strategic oriented business intelligence: a theoretical framework. arXiv preprint arXiv:1307.7328.

Choi, H., Son, J., Yang, H., Ryu, H., Lim, B., Kim, S. and Chung, Y.D., 2013, April. Tajo: A distributed data warehouse system on large clusters. In Data Engineering (ICDE), 2013 IEEE 29th International Conference on (pp. 1320-1323). IEEE.

Cravero Leal, A., Mazón, J.N. and Trujillo, J., 2013. A business-oriented approach to data warehouse development. Ingeniería e Investigación, 33(1), pp.59-65.

Destercke, S., Buche, P. and Charnomordic, B., 2013. Evaluating data reliability: an evidential answer with application to a web-enabled data warehouse. IEEE Transactions on Knowledge and Data Engineering, 25(1), pp.92-105.

García-Moya, L., Kudama, S., Aramburu, M.J. and Berlanga, R., 2013. Storing and analysing voice of the market data in the corporate data warehouse. Information Systems Frontiers, 15(3), pp.331-349.

Golfarelli, M. and Rizzi, S., 2013. Data warehouse testing. Developments in Data Extraction, Management, and Analysis, pp.91-108.

Inmon, W.H. and Linstedt, D., 2014. Data Architecture: A Primer for the Data Scientist: Big Data, Data Warehouse and Data Vault. Morgan Kaufmann.

Khouri, S., Bellatreche, L., Jean, S. and Ait-Ameur, Y., 2014, October. Requirements driven data warehouse design: We can go further. In International Symposium On Leveraging Applications of Formal Methods, Verification and Validation(pp. 588-603). Springer, Berlin, Heidelberg.

Park, T. and Kim, H., 2013. A data warehouse-based decision support system for sewer infrastructure management. Automation in Construction, 30, pp.37-49.

Su, S.P.T. and Needamangala, A., 2017. Harvesting information from a library data warehouse. Information Technology and Libraries, 19(1), pp.17-28.

Tebourski, W., Karâa, W.B.A. and Ghezala, H.B., 2013. Semi-automatic Data Warehouse Design methodologies: a survey. International Journal of Computer Science Issues (IJCSI), 10(5), p.48.

Thenmozhi, M. and Vivekanandan, K., 2013. A tool for data warehouse multidimensional schema design using ontology. Int. J. Comput. Sci. Issues (IJCSI), 10(2), pp.161-168.

Vaisman, A. and Zimányi, E., 2014. Data warehouse systems. Springer, Heidelberg.