Dimensional Modelling Business Case
Objectives
- To create a Data Warehouse conceptual design using Star Schema Modelling
- To document allsteps during the design process
Business description
Qantas Airways is the flag carrier airline of Australia.Qantas' headquarter is located at the Qantas Centre in the Mascot suburb of Sydney, New South Wales.The Qantas Group's main business is the transportation of customers.As of March 2017, Qantas and its subsidiaries operate 285 aircraft which includes 72 aircraft by Jetstar Airways, 82 by the various QantasLink-branded airlines, 8 by Jetconnect and 5 by Express Freighters Australia on behalf of Qantas Freight.
Qantas flies to 20 domestic destinations and 21 international destinations in 14 countries across Africa, the Americas, Asia, Europe and Oceania excluding the destinations served by its various subsidiary airlines.
Qantas management is to ensure the successful completion of around 300,000 flights every year to 57 airports around Australia and to 86 destinations in 36 countries that is a massive task. It requiresmaintaining high levels of customer service and operational efficiency covering very broad spectrum of business areas such as Engineering, Freight, Airports, flights and other Qantas various corporate business units.Given the diversity of the Group's operations, the management must frequent analyse operational data and making decisions for better marketing prediction and effective and efficient business operations.
In this assignment, we are only working on a subset of Qantas's business operationsfor multidimensional design. Only the following departments will be considered.
1. Flight management
There are hundreds Qantas flights transporting passengers from different airports to over 50 domestic and international destinations every day. Qantas flight operational database records every flight, the flight log, flight number, aircraft name, fight date and time of departure and landing, the airports of departure and destination, the airports, the capacity, the number of passengers, the number of crew members, the captain and First Officer of flight, the distance, the actually flying time, the fuel consumed. The total of meal, drinks, consumable cleaning liquid and tissues,etc in each trip
Each flight maintains an inventory table for consumable products. The inventory will be refilled at end of each trip. The management wants to analyze the high costs of productsconsumed inflight for budget planning and improvement.
All Qantas Line Maintenance Stations can communicate with inbound aircraft provides 24 hours per dayfor advice on incoming defects,facilitating the preparation of appropriate resources to preclude unscheduled extended ground time. For operators with extended stopovers, Qantas Line Maintenance can perform Supplementary or Minor Maintenance inspections - optimising the aircraft's down time.
Sophisticated ground support equipment and Technical support are used for fault diagnosis and troubleshooting.
Each service and the in charge engineer, the parts used, the problem fixed and service logfor an aircraft will be recorded in the database.The management must make sure the safety by analysing the services data to identify the potential dangerous aircraft components and cost effective of the repairs and services.
2. Awarding programs and services
Qantas Group's broad portfolio of subsidiary businesses ranges from Qantas Clubs to Qantas Frequent Flyer.
Qantas frequent flyer program is aimed at rewarding Qantas customer loyalty. Points can also be earned on other Oneworld airlines as well as through other non-airline partners. Points can be redeemed for flights or upgrades on flights operated by Qantas, Oneworld airlines, and other partners.
A Qantas Frequent Flyermember's points can be collected if one of the following actives occurs:
- A new ticket of Qantas flight is purchased
- Any purchases are paid by a credit card from Qantas partners
The types of Qantas frequent flyer memberships are classified as follows:
- Bronze card holder
- Silver status (Oneworld Ruby),
- Gold status (Oneworld Sapphire),
- Platinum and Platinum One status (Oneworld Emerald).
The class of Qantas frequent flyer membership is based on flying more for higher-level class. The types of memberships are based on distance flown,with bonuses that vary by travel classes with Qantas airline and Qantas subsidiary airlines. The distance of trips reaches certain credits Qantas Frequent Flyer membership will be upgraded to a higher level of class, eg, Gold class toPlatinum class.
HQ managements analysethe usage of Qantas memberships for all classifications.
They want the following updated information about each member on monthly figures:
- Starting point balance
- current point balance
- Points earned by Qantas traveling
- Points earned by Qantas partners
- Traveling mileage
- Points used for upgrading tickets
- Points used for buying Qantas products
- Points used for purchasing tickets
- Recent traveling activities
- Number of longue usage
- Name of Qantas partner for the point earned
To promote the better services to VIP membersand award customers' loyalty, Qantas provide the airline lounge with airport locations around Australia and the world. Those Gold Frequent Flyer holders, Platinum Frequent Flyers holders, Oneworld first-class passengers and Qantas Club Members (who paid membership fees) are provided the benefits including lounge access for meals, drinks and rest rooms, priority check-in, priority luggage handling and increased luggage allowances.
Qantasflights offer different classes of seats based on the space of the cabins:
- Qantas first-class is offered exclusively on Airbus A380s and some Boeing 747-400s.
- Business class is offered on all Qantas mainline passenger aircraft.
- Premium economy class is only available on Airbus A380 and all Boeing 747-400 aircraft. Qantas premium economy is presented as a lighter business class product rather than most other Qantas long-haul economy cabin
- Economy class is available on all Qantas mainline passenger aircraft.
The first class and business class travelers all have complimentary access to the Qantas business class lounge (or affiliated lounges) as their part of benefits.
HQ management needs the data analytics on the Qantas business class lounges' usage, services provider, number of clerks in one lounge room, the costs of foods and drinks for their better operations.
3. Ticket sales:
Qantas has a centralised operational database to capture their day-to-day ticket sales. Qantas provides online ticket sales and promotion agents for ticket sales. Promotion agents must be supplied with batches of tickets. Those tickets are allocated to each agent and are not available to be sold online. To avoid unsold tickets, agents are initially allocated minimal size batches of tickets and periodically request more batches on demand. If a ticket is sold, the customer's details are recorded with the booking date, payment date, agent or online sale indicator, the class of the ticket, payment methods, Qantas frequent flyer members, Qantas club card, price, so on.
Customers can pay their purchased tickets by cash, cheque, bank card, visa card, master cards and frequent flyer point. The payment by American express card will be charged 2% extra. Online purchases must use credit cards.
At the end of each month, Qantas distributes commissions to the promotion agents based on the number of tickets they have sold. The percentage commission paid to each agent for each flight they have promoted over the past 5 years is stored in a database.
Promotion is one important activity to improve the business. Heavy price reductions are the most effective way to create substantial increases in the number of tickets sold.
Each promotion is specified with a promotion_id and the description. It is then the responsibility of the manager to apply for promotions during the salesactivities. Promotion files contain information about the different promotions.
Every week, HQ distributes a central list to all agents with available tickets from the different flights, including price, availability information, the ratings and level of discounts if any.
The revenue of the business is generated by flight tickets sales. Retail price may vary between sessions, promotion periods or special events. The central management collects all local agents weekly sales. Thefield management strategy is used fordata analysis, demand forecasting, and decision making. The management needs to analyse the performance from different agents for commissions. Marketing managers need to analysis customer behaviours and location performance for better planning and promotion decisions.
- the promotion describes its scope (which flights are affected) such as:
- a list of specific flights and their destinations
- a specific event
- the types of card holders may benefit from it (eg., all customers or a specific class (Gold and Platinum)).
- Start and end dates (that is, for seasonal promotions)
- Discount level
The centralmanagement is looking for determining whether the promotion was effective:
The need to analyze sales revenue that includes the promotions were applied for the ticketon sales. The sale records contain information about which promotion affected the actual price of the tickets of flights.
In order to compute the effectivenessof the promotion, several factors have to be considered:
- The sale revenues have generated for the targeted flights.
- Have the ticket sales under promotion increased during the promotional period?
- Which agents have more sales during the promotions? Does this vary across different months or event types?
- What tickets were on promotion but did not sell in full capacity?
Managements want to analyse the agent performance and the distributions among agents.
Qantas central managements wish to analyze their business performance from many different business units to maximize their revenue. They need to perform detailed analysis of their businessto see the efficient operations and effective achievement. It hasobserved that a simple reporting feature built on top of their operational database is not adequate. Therefore, an enterprise data warehouseis required to assist mangers in addressing the aforementioned requirements and queries.
1. What products are short of supply for any flightsat end of trip? Has this always been the case in last three months?
2. What are top 3 flights have the highest sales across the country in last 12 months?
3. Identifying the most frequentrepairs on components of aircraftthrough the history of services.
4. Identifying the hot time period of flights for full seats flies in last 12 months, find out what flightsare fully booked three monthsin advance.
5. Whichage group of customers ismost likely using our flightat weekends? Does thisvary across different location or times of the year?
6. Finding the main occupation of those people who purchase the first class ticket in last 12 months.
7. What particularfood in Qantas lounge are the most popular? Any type of drinks in the lounge is less than 2 bottles every day in last months? Do they share the common feature?
8. Finding the percentage of first class or business class traveller and percentage of plenum Qantas frequent flyer to see who are using Qantas lounge more in last 3 years.
9. Does the promotion period in Christmas month have increased the sales comparing with the same period in last year?
10. Do customers prefer to go online purchases or buy tickets from promotions agent? Do they prefer to pay cash or credit card?
11. Any flights have not beensold out during the promotion period?
12. How many percentages of customers are Qantas frequent flyers among those travellers in last 3 months?
13. Which promotion agent earns the highest commission?
Your Task:
Your task is to design a data warehouse for Qantas Warehouse using multidimensional 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) Designthe star schema for any Data Marts you have identified, ensuring your Dimensions are conformed, primary and foreign keys are clearly labelled, and that yourattributes are named using verbose textual descriptions. Provide a sample row of your fact table for each schema.
(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 Fact table type 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 featureyou 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 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.