|
Business processes |
Dimensions |
|||||||||||
|
Date |
Payment method |
Customer |
Employee |
Flights |
Membership |
Points |
Diagnosis |
Tool |
Products |
Agents |
Commission |
|
|
Flight management |
X |
X |
X |
X |
X |
X |
||||||
|
Awarding programs and services |
X |
X |
X |
X |
X |
X |
||||||
|
Sales |
X |
X |
X |
X |
X |
X |
Flight Management Fact Table:
Awarding Programs and Services Fact Table:
Sales:
|
Fact table name |
Fact granularity |
Fact table type |
Brief justification |
|
Flight_Management_fact_table |
Flight_Duration |
Accumulating |
The management of the Airline will be needed to see the flight durations against each of the flight to gather better flight management related data and take accurate and improved decision. |
|
Awarding_Promotion_and_Services_Fact_Table |
Commission_Amount |
Transaction |
The management in order to get the idea about the commission about to be provided to the agents will be gathering from this data unit. |
|
Sales_fact_table |
Total_Sales_of_Ticket |
Periodic Snapshot |
The total sales of ticket is the most crucial data in terms of the sales business process. The sales fact table can be measured against the shortest business standard time or seconds. |
|
Dimension table name |
Brief justification |
Attribute hierarchies |
|
Date_Dimension |
In terms of taking the business decisions, the information are required on the basis of date and time. The dim_date dimension is one of the crucial dimension table of the warehouse. |
Date_id (PK) Full_date Date Month Year Time Quarter |
|
Payment_Method_Dimension |
This dimension will inform the payment methods used for every transaction from the consumers’ end. |
Payment_method_id (PK) Payment_type Total_Payment |
|
Customer_Dimension |
The dimension will provide all the customer data, required for decision making, to the management |
Customer_id (PK) customer_name customer_address customer_gender customer_birth_day customer_contact_number customer_email customer_age customer_occupation |
|
Employee_Dimension |
The information regarding the employee will be recorded within this dimension |
Employee_id (PK) Employee_Name Employee_Address Employee_Experience Employee_Contact Employee_Designation Employee_Salary |
|
Flights_Dimension |
The flights are the core of the Qantas Airways’ business process. In order to gather flight related answers, this dimension will be the basic participant |
Flight_id (PK) Flight_name Source_airport Destination Flight_Capacity |
|
Agent_Customer_Flight_Dimension |
It is temporary table that hold the name of the agent name that booked ticket for a particular flight for a customer |
ACF_ID (PK) Agent_name Customer_name Flight_name |
|
Membership_Dimension |
The Qantas Airways provides various kinds of membership. Tracking the membership is essential for various decision making needs. |
Membership_id (PK) Membership_type Membership_duration |
|
Points_Dimension |
The Qantas Airways provides points to the consumer. These points are essential to keep track of. |
Points_id (PK) Earned_Point Used_point |
|
Dignosis_Dimension |
The management need the diagnosis reports for enhancing the flight management. This diagnosis can be trouble shooting |
Diagnosis_id (PK) Diagnosis_name Associated_Airline_Section |
|
Tool_Dimension |
The tools used in diagnosis are essential for making the decisions against diagnosis. |
Tool_id (PK) name type |
|
Diagnosis_Tool_Date_Junk_Dimension |
This will provide an short term data on the diagnosis result, tool status after diagnosis and temporary issue in the process |
Tool_id (PK) Diagnosis_id (PK) Date_id (PK) Tool_Ok Issue Diagnosis_successful |
|
Customer_type_Dimension |
The customers can be of different types. The characteristics of customer is a crucial factor for the management to take customer related decisions. |
Customer_type_id (PK) Type_name Type_priority |
|
Customer_and_Type_Dimension |
There are some poinst when the need of the bridge tables can be felt. This bridge table act as the junction between Customer_Type_Deimnsion and Customer_Dimension. |
Customer_id (PK) Customer_type_id (PK) Type_name Customer_name |
|
Products_Dimension |
The management also wants to make certain changes in the products provided to the consumers during flights. |
Product_id (PK) Brand_describtion Package_type_describtion Product_color Product_backage_type Product_brand Product_features |
|
Ingredient_Dimension |
The ingredients will allow the flight passengers to track the product material that is crucial for health conscious passengers |
Ingredient_id (PK) Ingredient_Name |
|
Agents_Dimension |
The agents are inevitable parts of the flight bookings. In terms taking decisions associated with the agents, this dimension will serve as the base dimension. |
Agents_id (PK) Agents_name Agents_gender Agents_birth_day Agents_contact_number Agents_email Agents_Address |
|
Commission_Dimension |
The agents get commission from the tickets they sell. |
Commission_id (PK) Commission_amount Total_commission Commission_Type |
|
Design feature |
Brief description |
Brief justification |
|
Degenerate dimension |
This is always exiting the fact tables. |
Total_Sale_Of_Ticket, Total_Earned_Points, Fulty_Ingridient, Product_Charge, Diagnosis_Duration, Flight_Duration, Total_Award, Commission_Amount are the degenerated dimension as these key are the DD as these do not have its own dimension table |
|
Role playing dimension |
This dimension are recycled for fulfilling the various purposes |
The date dimension is the role playing dimension as it is recycled in every star schema for satisfying various decision making the needs. |
|
Fact-less fact table |
This dimension does not have any purpose. |
The Awarding Programs and Services Fact Table is a fact-less fact table. I does not have a meaning. This provides the connection between the dimension tables so that all the programs and service related decisions can be made easily. |
|
Conformed dimension |
This dimension carries the same purpose for every fact that it relates to. |
The agent dimension is the conformed dimension as it carries the same purpose for both the fact tables it is connected. |
|
Junk Dimension |
A junk dimension is grouping of low cardinality flags and indicators. This junk dimension helps in avoiding cluttered design of data warehouse. |
Diagnosis_Tool_Date_Junk_Dimension is the junk dimension in the star schema. This dimension holds the attributes that do not belong to any dimension or fact table. This dimension provide answer in the form of simple yes or no. |
|
Mini Dimension |
The mini dimension is for addressing the rapid changing attributes. |
Agent_Customer_Flight_Dimension is the Min Dimension as the data changes in the dimension. An agent may book ticket to the same customer for different flight and many other reasons. |
|
Bridge Table |
The bridge table acts as a solution to the many-to-many issue in the data warehouse star schema model. |
Customer_and_Type_Dimension is the bridge table in the data warehouse star schema model. |
Question 1: What products are short of supply for any flightsat end of trip? Has this always been the case in last three months?
- Flight_Management_Fact_Table: Product_Key, Date_Key
- Date_Dimension: Full_date, Time, Month, Year
- Product_Dimension: Product_Id
Question 2: What are top 3 flights have the highest sales across the country in last 12 months?
- Sales_Fact_Table: Date_Key, Flight_Key
- Date_Dimension: Month, Year
- Flight_Dimension: name
Question 3: Identifying the most frequentrepairs on components of aircraftthrough the history of services.
- Flight_Management_Fact_Table: Diagnosis_Key
- Dignosis_Dimension: Diagnosis_Name, Associated_Airline_Section
Question 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.
- Sales_Fact_Table: Date_Key, Flight_Key
- Date_Dimension: Month, Year
- Flight_Dimension: Flight_Name
Question 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?
- Sales_Fact_Table: Flight_Key, Date_Key
- Date_Dimension: Month, Year, Date
- Customer_Dimension: customer_age, customer_location
Question 6: Finding the main occupation of those people who purchase the first class ticket in last 12 months.
- Sales_Fact_Table: Flight_Key, Date_key, Customer_Key
- Date_Dimension: Month, Year
- Flight_Dimension: Flight_Name
- Customer_Dimension: customer_occupation
Question 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?
- Flight_Management_Fact_Table: Date_Key, Product_Key
- Date_Dimension: Month, Year
- Product_Dimension: Product_ID
Question 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.
Sales_Fact_table: Customer_Key, Customer_Type_Key, Date_Key
Customer_Dimension: Customer_Name
Customer_Type_Dimension: Type_Name
Dim_Date: Month, Year
Question 9: Does the promotion period in Christmas month have increased the sales comparing with the same period in last year?
- Sales_Fact_Table: Date_Key, Total_Sale_Of_Ticekt
- Dim_Date: Month, Year
Question 10: Do customers prefer to go online purchases or buy tickets from promotions agent? Do they prefer to pay cash or credit card?
Sales_Fact_table: Flight_Key, Payment_Method_Key
Flight_Dimension: Flight_Name
Payment_Method_Dimension: Payment_Type
Question 11: Any flights have not been sold out during the promotion period?
- Sales_Fact_Table: Date_Key, Flight_Key, Total_Sale_Of_Ticket
- Date_Dimension: Month, Year
- Flight_Dimension: name
Question 12: How many percentages of customers are Qantas frequent flyers among those travellers in last 3 months?
- Sales_Fact_Table: Date_Key, Customer_Key
- Date_Dimension: Month, Year
- Customer_Dimension: Customer_Name
Question 13: Which promotion agent earns the highest commission?
- Sales_Fact_Table: Date_Key, Total_Sale_Of_Ticket, Commission_ID
- Dim_Date: Month, Year
- Dim_Commission: Commission_Amount
George, J., Kumar, V. and Kumar, S., 2015. Data Warehouse Design Considerations for a Healthcare Business Intelligence System. In World Congress on Engineering.
Nagar, A. and Chandwani, G., 2014. Designing of the CASE Tool to develop Star Schema using the XML Schema consisting of Clinical Data. International Journal of Computer Applications, 104(2).
Zhu, J., Potti, N., Saurabh, S. and 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), pp.889-900.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
