A Qantas Frequent Flyer member’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 to Platinum class. HQ managements analyse the 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 members and 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. Qantas flights 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 sales activities. 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. The field management strategy is used for data 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 central management is looking for determining whether the promotion was effective: The need to analyze sales revenue that includes the promotions were applied for the ticket on sales. The sale records contain information about which promotion affected the actual price of the tickets of flights. In order to compute the effectiveness of 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 business to see the efficient operations and effective achievement. It has observed that a simple reporting feature built on top of their operational database is not adequate. Therefore, an enterprise data warehouse is required to assist mangers in addressing the aforementioned requirements and queries. 1. What products are short of supply for any flights at 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 frequent repairs on components of aircraft through the history of services. 4. Identifying the hot time period of flights for full seats flies in last 12 months, find out what flights are fully booked three months in advance. 5. Which age group of customers is most likely using our flight at weekends? Does this vary 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 particular food 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 been sold 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:(100 marks total) 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. (10 marks) (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. Provide a sample row of your fact table for each schema. (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 Fact table type Brief justification (iiii) 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, 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 (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 above. (10 marks) Lecturer’s Notes: ➢ 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,.