CSE5DWD Data Warehouse Concepts and Design

Published : 14-Sep,2021 | Views : 10

Question:

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.

What products are short of supply for any flights at end of trip Has this always been the case in last three months
What are top 3 flights have the highest sales across the country in last 12 months
Identifying the most frequent repairs on components of aircraft through the history of services.
Identifying the hot time period of flights for full seats flies in last 12 months, find out what flights are fully booked three monthsin advance.
Which age group of customers is most likely using our flight at weekends Does this vary across different location or times of the year
Finding the main occupation of those people who purchase the first class ticket in last 12 months.
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
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.
Does the promotion period in Christmas month have increased the sales comparing with the same period in last year.
Do customers prefer to go online purchases or buy tickets from promotions agent Do they prefer to pay cash or credit card.
Any flights have not been sold out during the promotion period
How many percentagesof customers are Qantas frequent flyers among those travellers in last 3 months
Which promotion agent earns the highest commission.

Answer:

A data warehouse is also known as the enterprise data warehouse and is used in the business industry for da analysis and reporting. It is considered as the core component of the business intelligence and different data are gathered regarding the business areas and integrated for the preparation of the analytical report. The enterprise bus matrix is designed for creating a proper planning and model the data warehouse. For the preparation of the bus matrix the problems are divided into manageable parts and the fact table for the data warehouse is created for to show the relation between the databases created for the company. The graphical schema of the data warehouse is designed by creating the star schema.

The report consists of the bus matrix, star schema, fact table and its justification such as the dimension and its granularity. The creation of the data warehouse helps the Qantas Airline to take better decision regarding the business and thus help in growing the current business process and gain competitive advantage over other companies in the market.

Fact Table, Granularity and Justification for the selection of Granularity:

Fact table name	Fact granularity	Fact table type	Brief justification
MaintenanceFactTable	TotalProductCost	Accumulating	For improving the onboard and the airport service for the customer decision is required to be taken by the airport management authority for the products sold by the company and calculating the cost of the product for increasing the efficiency of the system.
ServicesFactTable	Total_Point	Transaction	The transaction at the sales counter is needed to be recorded for calculation of the point distributed to the customer during the selling of the tickets.
SalesFactTable	TotalSalesofTicket	Periodic Snapshot	Report is required to be created for the sales of the ticket including the date and time when the tickets are sold.
InventoryFact table	Items in inventory	Periodic snapshot	Report is created regarding the items that are stored in the inventory and the date and time of the product entering or leaving the inventory is recorded in the database.
IssueTicket Fact Table	TotalTicketIssued	Transaction	The number of ticket issues is recorded to calculate the transaction and it is necessary for keeping track on the sales of the ticket.

Justification and Attribute Hierarchy of the Dimension Tables:

Dimension table name	Brief justification	Attribute hierarchies
Date_Dimension	It is used for recording the details when the ticket is sold to the customer.	Date_id (PK) Full_date Date Month Year Time
Dimension_PaymentMethod	It is used for recording the transaction details that is successful or failed payment	Payment_method_id (PK) Payment_type Total_Payment
Customer_Dimension	The record of the customer is required to be stored in the database for handling the emergency conditions.	Customer_id (PK) customer_name customer_address customer_gender customer_birth_day customer_contact_number customer_email customer_age customer_occupation
Employee_Dimension	The details of the employees are stored in the database for improving the payroll system.	Employee_id (PK) Employee_Name Employee_Address Employee_Experience Employee_Contact Employee_Designation Employee_Salary
Flights_Dimension	The details of the flight is required to be stored for handling emergency situations.	Flight_id (PK) Flight_name Takeoff_Airport Destination_Airport Flight_Capacity
Points_Customer_Dimension	It records the points earned and spend by the customer	Ponits_Key (FK) Customer_Key (FK) Last_Earned_Point Last_Used_Point
Agent_Booking_Dimension	It records the details of the tickets that are booked by the agents	Agents_Key (FK) Customer_Key (FK) Agent_Name Customer_Name
Membership_Dimension	The details of the members are stored in the database	Membership_id (PK) Membership_type Membership_duration
Dignosis_Dimension	The result of the analysis or diagnosis id stored in the database	Diagnosis_id (PK) Diagnosis_name Associated_Airline_Section
Ingredient_Product_Dimension	It records the details of the ingredients that are used for preparation of the products	Ingredient_Key (FK) Product_Key (FK) Ingredient_Name Product_Name
Tool_Dimension	The details of the tools are stored in the database for management of the data warehouse	Tool_id (PK) Tool_Name Tool_Type
Junk_Dimension_Diagnosis_Tool_Date	Data is stored in the database as a Boolean format for easy diagnosis and easy collection of the data from the database	Tool_id (PK) Diagnosis_id (PK) Date_id (PK) Tool_Ok Issue Diagnosis_successful
Products_Dimension	The details of the products are stored in the database tables	Product_id (PK) Product_Name Brand_describtion Product_brand Product_features
Dimension_Ingredient	The ingredient data are stored in this dimension	Ingredient_id (PK) Ingredient_Name
Dimension_Promotion	The promotion data are stored in this dimension	Promotion_ID (PK) Promotion_Name Promotion_Start_Date Promotion_End_Date
Agent_Dimension	The details of the agents are stored in the database of the organization	Agents_id (PK) Agents_name Agents_gender Agents_birth_day Agents_contact_number Agents_email Agents_Address
Comission_Dimension	The data regarding the commission provided to the agents are stored in the database	Commission_id (PK) Commission_amount Total_commission Commission_Type

Data Warehouse Design Features:

Design feature	Brief description	Brief justification
Degenerate dimension	This information are excluded from the fact tables	Error_Ingredient, Result_Diagnosis, Total_Product_cost, Diagnosis_Time, Flight_Duration, Ingredient_Amount, TotalSaleOfticket, TotalSaleOfTicket,
Junk Dimension	It helps to respond to the querry of the customer and the sales represented using the information system of the Qantas airlines	Junk_Dimension_Diagnosis_Tool_Date is the junk dimension as this dimension provide answer to the questions such as tool is re-usable or is diagnosis successful and many more.
Mini Dimension	It helps in storing the records and update the records consuming less time	Agent_Booking_Dimension table recodes the data and is updated frequently for storing all the records regarding the transaction and sales of the ticket or change in the booking of the flight or schedule of the flight.
Bridge Table	The bridge table is used to interconnect the tables and respond to different querries of the customer and the sales representatives	Dimension_Points_Customer is the table used as a joining table and provide solution using many to many relation between the Customer_Dimension and Points_Dimension
Conformed dimension	This dimension carries the same purpose for every fact that it relates to.	The operator measurement is the adjusted measurement as it conveys a similar reason for both the reality tables it is associated.
Role playing dimension	This measurements are utilized as a part of every reality table	The date measurement can be considered as the pretending measurement as it is utilized as a part of every reality table

7. Identification of Fields:

FactTableMaintenance: Product_ID, Date_ID, Flight_ID
Date_Dimension: Full_date, Time, Date, Month
Product_Dimension: Product_Name
Flight_Dimension: Flight_Name
FactTableSales: Date_ID, Flight_ID, TotalSaleOfTicket
Date_Dimension: Month
Flight_Dimension: Flight_Name
FactTableMaintenance: Diagnosis_ID
Dignosis_Dimension: Diagnosis_Name, Associated_Airline_Section
FactTableSales: Date_ID, Flight_ID
Date: Month, Year
Flight: Flight_Name
FactTableSales: Flight_ID, Date_ID
Date_Dimension: Month, Year, Date
Customer_Dimension: customer_Age, customer_location
FactTableSales: Flight_ID, Date_ID, Customer_ID
Date_Dimension: Month, Year
Flight_Dimension: Flight_Name
Customer_Dimension: customer_occupation
MaintenanceFactTable: Date_ID, Product_ID
Date_Dimension: Month, Year
Product_Dimension: Product_Name
FactTableSales: Customer_ID, Date_ID
FactTableServices: Membership_ID, Customer_ID, Date_ID
Customer_Dimension: Customer_Name
Membership_Dimension: Membership_type
Date_Dimension: Month, Year
FactTableSales: Date_ID, TotalSaleOfTicket
Date_Dimension: Month, Year
FactTableSales: Flight_ID, PaymentMethod_ID
Flight_Dimension: Flight_Name
PaymentMethod_Dimension: TypeofPayment
FactTableSales: Date_ID, Flight_ID, TotalSaleOfTicket
Date_Dimension: Month, Year
Flight _Dimension: Flight_Name
FactTableSales: Date_ID, Customer_ID
Date_Dimension: Month, Year
Customer _Dimension: Customer_Name
FactTableSales: Date_ID, Agnet_ID, TotalSaleOfTicket, Commission
Date_Dimension: Month, Year
Agent_Dimension: Agent_Name

Conclusion

For taking an effective decision and manage the current business process of the airline company the development of the data warehouse plays an effective role. The star schema is developed after analyzing the current business process of the organization and it helps the business executives to fetch all the details about the company. Using the data warehouse design the company Qantas Airways can increase their security of their business and enhance their querry processing. The major problem found during the preparation of the report is that the design process is time consuming and in order to create an effective data warehouse design the business process of the organization is required to be analyzed. The connection between the fact tables is made such that it can respond to each of the business querry and increase the efficiency of the data warehouse system.

Bibliography

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.

Baboo, L.D.S. and Kumar, P.R., 2013. Next generation data warehouse design with big data for big analytics and better insights. Global Journal of Computer Science and Technology, 13(7).

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

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.

Kimball, R. and Ross, M., 2013. The data warehouse toolkit: The definitive guide to dimensional modeling. John Wiley & Sons.

Kraus, C. and Valverde, R., 2014. A data warehouse design for the detection of fraud in the supply chain by using the Benford’s law. American Journal of Applied Sciences, 11(9), pp.1507-1518.

Mireku Kwakye, M., 2017. Modelling and Design of Generic Semantic Trajectory Data Warehouse. Science.

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.