Airline-System-DWH-Modeling:

A leading airline company engaged our services to support the executive management in their analysis of current business processes and identification of new opportunities for company growth.

Project Description:

• Executives decided that the first deliverable should focus on the flight activity in order to ensure a good ongoing business process.
• The marketing department wants to analyze what flights the company’s frequent flyers take, what fare basis they pay, how often they upgrade, how they earn and redeem their frequent flyer miles, whether they respond to special fare promotions, how long their overnight stays are, and what proportion of these frequent flyers have gold, platinum or titanium status.
• Analysis also include the reservation process where the finance team will be interested in analyzing the company's profit. Note that reservation processes can take place through multiple channels. The airline company also provides customer care interaction before, within, and after your trip in order to handle customer inquiries, and complaints and keep their feedback for business enhancements. Analysis include interaction type and problem severity if an issue exists .

Analysis of Business Processes:

In order to execute this and distribute those reports to other business sectors inside the organization we first considered how many business processes we would require. We came to the conclusion that there would be 3 processes involved in asking all the questions after far too many talks.

• 1. Customer Care is crucial for gathering feedback from customers and learning about their preferences so we can improve our services.
• 2. The Behavior of the passengers includes information about the passenger's behavior as well as details about the ticket, the passenger, the flight, its route, and any promotions that may have been included.
• 3. The trip's goal is to give the executive a full report on the state of the company's operations.

🖥 Project steps:

• Define the Business Processes.
• State the modeling process for each business process.
• Construct bus matrix.
• Construct a logical data model using diagramming tool.
• Translate the logical data model to a physical data model .
• Create a table/s in oracle DBMS and populate sample data for your queries.
• Construct a sample of SQL queries using your physical model design which can be used to answer Business questions.