Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

Dimension table names

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 price.

? Customers can recharge money on their cards with top-ups to increase the card balance. 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: 3 ? 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 to.

o 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?

Design features

? 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 information. ? 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 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 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 table.

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: Rental and Sales 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. 4 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?

Fact less fact tables

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?

The HQ management is also checking their inventory daily to check the shortage and surplus of vehicles for scheduling among their stores. ? 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? The management also analyse the different revenue stream on VIP cards: ? 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 level. Advertisement/commercials Cost analysis: In order to compute the effectiveness of the promotion, several factors have to be considered: ? The total cost of the promotion for media campaigns 5 ? 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.

Degenerate dimensions

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 The management is looking for determining whether the promotion was effective: ? 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. 6. Your Task: (100 marks total) Your task is to design a data warehouse for CarHireOZ 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. Explain each business process with few sentences. (10 marks) (ii) 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 6 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. (40 marks) (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. (12 marks).

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. (12 marks). 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, minidimensions, bridge 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 the following selected some business questions. (10 marks) (Notice: your design should be able to answer all questions in the aforementioned case study.) 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 stores. 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? Lecturer’s Notes: 7 ? It is not enough to just draw the shapes of the facts and dimensions in the data warehouse/ data marts. Each fact/dimension should be completely defined, with keys and facts/attributes clearly described; ? All information from the given business problem should be recognisable in your final data warehouse; ? Your final data warehouse should be able to answer all business questions listed. If one question cannot be answered using your proposed solution, it means the solution is not completely correct and you have to rethink it;

Dimension table names

Fact Table, Granularity and Justification against Granularity:

Fact table name

Fact granularity

Brief justification

Rental and Sales Fact Tale

TotalRevenue

The organization is running the business to make profit and grow business. The growth of the business is reflected through the revenue. For this reason, it is very crucial to collect the information of revenue.

CarRentAtPromotion

This fact shows the business growth at processing level

ItemSellQuantity

The organization also make profit from selling the items

RevenueInNormalPeriod

Most of the time business runs on normal period

RevenueAtPromotionPeriod

Business can find the quantity of rented car so that proper business decision can be made

ADVERTISEMENT_COMMERCIAL

PromotionCost

It is essential for the organization to reduce cost of promotion so that profit can be increased

PromotionSuccess

The total amount of capital collected form a promotion

RentAmount

The amount of car rented because of a promotion

MediaCompanyProfit

The amount of additional capital CarhireOZ makes from a media promotion

ProtionDuration

The duration of the promotion. This assists in understanding the impact of promotion and reduce business by adjusting promotion duration next time

4. Justification and Attribute Hierarchy of the Dimension Tables:

Dimension table name

Brief justification

Attribute hierarchies

BOOKING_DIMENSION

The booking allows the organization to determine whether full online business is ready to be accepted or not

 

CONTRACTS_DIMENSION

Contacts act as the proof of settled deals. This dimension allows the organization to collect additional processes when customer violates a rule. Based on these data, the organization can collect payment legally

 

CUSTOMER_DIMENSION

Customers are the base of business. Having required data of the customer allows to expand business and better promotion option

 

Date_Dimension

The date dimension allows the system to have full information of the values in timeline aspect

 

DRIVER_DIMENSION

Having the details of the drivers allows the organization to manage the business legally and manage processes

 

Item_DIMENSION

The dimension table allows the organization to have item related data

 

Media_Company_DIMENSION

This dimension allows the organization to find the details of the media companies

 

Member_Class_DIMENSION

CarhireOZ will be using these data to identify the customer expense capacity. The promotions will be made as per the customer capabilities

 

Product_Promotion_DIMENSION

The promotion of the product ensures that whether the product sale will increase or not.

 

RETURN_PROTOCOL_DIMENSION

The return details are very crucial so that business operations can be operated smoothly.

 

STORES_DIMENSION

The sales from each store is significant data. CarhireOZ will make various decisions based on sales made from stores.

 

VEHICLE_DIMENSION

CarhireOZ must keep all the information of the cars they own. This allows the organization to have better insight of business allow further growth.

 

Vehicle_type_DIMENSION

The organization needs to have the information on the types of vehicle they own

 
5. Data Warehouse Design Features:

Design feature

Brief description

Brief justification

Fact less fact tables

The fact less fact table is the type of fact table that do not have any fact or measure

In the selected data warehouse consent, the fact tables have proper and significant facts or measures

Degenerate dimensions

The degenerated dimension in the fact table is referred to the key which has no dimension associated with it

The sales order number can be referred to as the Degenerate dimension in the proposed Rental and Sales Fact Tale

Role playing dimensions

Role playing dimension is often referred to as the dimension used in the same database repeatedly for various reasons

The DATE_DIMENSION is the ideal role playing dimension in the proposed data warehouse design

Bridge Table

The bridge table is used for diving the many to many implementing the database physically

There is no bridge table in the data warehouse  

Junk Dimension

The junk dimension stores the answer of a query in yes no only. No other query can be resolved through this dimension

The bookings_dimension is the junk dimension. It only refers to answer, whether the booking has been done online or not.

6. Identification of Fields:

Question 1:

Advertisement_Commercials: PromotionKey, registrationKey, DateKey, PromotionCost, PromotionSuccess

Date_Dimension: Day_name, Month_Name

VEHICLE_DIMENSION: VehicleModel

Question 2:

Advertisement_Commercials: PromotionKey, CompanyKey, registrationKey, DateKey, PromotionCost, PromotionSuccess

Date_Dimension: Day_name, Month_Name

Media_Company_DIMENSION: CompanyName, CompanyType

Question 3:

RENTAL_SALES: registrationKey, DateKey, customerKey, bookingNO, CarRentAtPromotion, RevenueAtPromotionPeriod

DATE_DIMENSION: DAY_NAME, MONTH_NAME

BOOKING_DIMENSION: BookingType

CUSTOMER_DIMENSION: CustomerLastName, CustomerFirstName

Question 4:

RENTAL_SALES: registrationKey, DateKey, bookingNO, CarRentAtPromotion, RevenueAtPromotionPeriod,  

Date_Dimension: DATE, Month

BOOKING_DIMENSION: BOOKINGSNO

Question 5:

RENTAL_SALES: registrationKey, DateKey, customerKey, bookingNO, contractKey, StoreKey

STORES_DIMENSION: StoreName, StoreAddress

Date_Dimension: Year

CONTRACTS_DIMENSION: Rent_Start_Date

CUSTOMER_DIMENSION CustomerLastName, CustomerFirstName

Question 6:

RENTAL_SALES: registrationKey, BookingNO, returnprotocolKey, CarRentAtPromotion, RevenueInNormalPeriod, RevenueAtPromotionPeriod

Date_Dimension: Year

VEHICLE_DIMENSION: VehicleModel, VehiclePicture

BOOKING_DIMENSION: BookingType

Question 7:

RENTAL_SALES: customerKey, bookingNO, contractKey

BOOKING_DIMENSION: BookingType

CONTRACTS_DIMENSION: Rent_Start_Date

CUSTOMER_DIMENSION: CustomerLastName, CustomerFirstName

Question 8:

RENTAL_SALES: customerKey, contractKey

CONTRACTS_DIMENSION: Contact_paymentmethod

CUSTOMER_DIMENSION: CustomerLastName, CustomerFirstName

Azez, H. S. A., Khafagy, M. H., & Omara, F. A. (2015). Joum: An indexing methodology for improving join in hive star schema. International Journal of Scientific & Engineering Research, 6, 111-119.

Chowdhury, R., Chatterjee, P., Datta, S., & De, M. (2016, April). Pseudo mesh schema based data warehouse architecture employing encryption request algorithm and intelligent sensor algorithm for secured transmission and performance enhancement. In Foundations and Frontiers in Computer, Communication and Electrical Engineering: Proceedings of the 3rd International Conference C2E2, Mankundu, West Bengal, India, 15th-16th January, 2016. (p. 467). CRC Press.

Chowdhury, R., Chatterjee, P., Mitra, P., & Roy, O. (2014). Design and implementation of security mechanism for data warehouse performance enhancement using two tier user authentication techniques. International Journal of Innovative Research in Science, Engineering and Technology, 3(6), 165-172.

Chowdhury, R., Roy, O., Datta, S., & Dasgupta, S. (2018). Virtual Data Warehouse Model Employing Crypto–Math Modus Operandi and Intelligent Sensor Algorithm for Cosseted Transference and Output Augmentation. In Knowledge Computing and Its Applications (pp. 111-129). Springer, Singapore.

Dehdouh, K., Boussaid, O., & Bentayeb, F. (2014, September). Columnar nosql star schema benchmark. In International Conference on Model and Data Engineering (pp. 281-288). Springer, Cham.

Di Tria, F., Lefons, E., & Tangorra, F. (2014). Big data warehouse automatic design methodology. Big Data Management, Technologies, and Applications, 115-149.

George, J., Kumar, V., & Kumar, S. (2015). Data Warehouse Design Considerations for a Healthcare Business Intelligence System. In World Congress on Engineering.

Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and data warehouses. Prospect Press.

Larbi, A., Malki, M., & Boukhalfa, K. (2018). Fuzzy Ontology-based Approach for the Requirements Query Imprecision Assessment in Data Warehouse Design Process Near Negative Fuzzy Operator. International Journal of Information Technology and Computer Science (IJITCS), 10(2), 18-32.

Raj, S. V. (2015). Applications of Business Intelligence in e-Commerce Websites-A Theoretical Overview of Data Warehouse Schema Design & Usage. The International Journal of Business & Management, 3(12), 8.

Solihin, W., Eastman, C., Lee, Y. C., & Yang, D. H. (2017). A simplified relational database schema for transformation of BIM data into a query-efficient and spatially enabled database. Automation in Construction, 84, 367-383.

Zhu, J., Potti, N., Saurabh, S., & Patel, J. M. (2017). Looking ahead makes query plans robust: Making the initial case with in-memory star schema data warehouse workloads. Proceedings of the VLDB Endowment, 10(8), 889-900.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Dimension Table Name And Design Features For CarHireOZ Data Warehouse. Retrieved from https://myassignmenthelp.com/free-samples/cse5dwd-data-warehouse-concepts-and-design/profit-and-grow-business.html.

"Dimension Table Name And Design Features For CarHireOZ Data Warehouse." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/cse5dwd-data-warehouse-concepts-and-design/profit-and-grow-business.html.

My Assignment Help (2020) Dimension Table Name And Design Features For CarHireOZ Data Warehouse [Online]. Available from: https://myassignmenthelp.com/free-samples/cse5dwd-data-warehouse-concepts-and-design/profit-and-grow-business.html
[Accessed 19 March 2024].

My Assignment Help. 'Dimension Table Name And Design Features For CarHireOZ Data Warehouse' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/cse5dwd-data-warehouse-concepts-and-design/profit-and-grow-business.html> accessed 19 March 2024.

My Assignment Help. Dimension Table Name And Design Features For CarHireOZ Data Warehouse [Internet]. My Assignment Help. 2020 [cited 19 March 2024]. Available from: https://myassignmenthelp.com/free-samples/cse5dwd-data-warehouse-concepts-and-design/profit-and-grow-business.html.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
Whatsapp
callback
sales
sales chat
Whatsapp
callback
sales chat
close