A major airline company decided to hire you in order to assist the executive management to analyze their current business processes and expand the company by discovering new opportunities. Executives decided that the first deliverable should focus on the flight activity in order to ensure 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, aluminum, or titanium status. Your analysis shall also include the reservation process where finance team will be interested in analyzing the company profit. Note that reservation processes can take place through multiple channels. Airline company also provides customer care interaction before, within and after your trip in order to handle customer inquiries, complaints and keep their feedback for business enhancements. Analysis should include interaction type and problem severity (if issue exists) State the processes bus matrix for your company then apply dimensional modeling process to design the logical and physical design to support such kind of analysis for decision support.
- Identify Bus matrix for your business processes against common dimensions(state which facts will use which dimensions)
- State the dimensional modeling process for each business process
- Using any diagramming tool you like, construct a logical data model for this case study. Output should be an image or PDF. State why did you choose this particular data model design? What does the data represent? (Details about each model component is necessary).
- Translate the logical data model to a physical data model which includes the following: tables and columns (name, data type) Output should be a Word or Excel file.
- Create the table in oracle DBMS and populate sample data to be used in your queries.
- Construct a sample of SQL queries (5 – 8 queries) using your physical model design which can be used to answer possible questions by the decision maker as described in the case-study above. List the business question with each query. Output should be a Word file
- A report of maximum 2 pages is required to elaborate different types of indexes used in Data warehousing and their usage.