Airline Data warehouse

Project description

During this project, I was able to create a comprehensive data warehouse architecture that met all of the business requirements.

▶ Here is a breakdown of the steps we followed:

Defined business processes and their level of granularity based on airline company requirements and industry understanding.

By identifying common interactions between different business processes and their properties I established fact and dimension tables, also a bus matrix that illustrates relationships between these tables.

Designed a logical model that represents the structure of the data warehouse using dimensional modeling principles and a diagram tool.

Created a physical data model using Toad, an Oracle-based tool for the RDBMS housing the dimension and fact tables.

Inserted Dummy data to be used in the queries that answer some of the business questions

▶The data warehouse schema we devised adopted a galaxy schema approach and included the following fact tables:

Flight Activity Fact:

This fact table, operating at the leg level of granularity,

describes each point the aircraft stops at.

It facilitated analysis for multiple departments, including

executives, finance, and marketing.

Frequent Flyers Fact

With a focus on frequent flyers, this fact table provided

valuable insights to the marketing team.

It enabled analysis of frequent flyers' actions, earned and

redeemed miles, and responses to promotional offers.

It is to be noted that those are different from regular passengers that were mentioned also in the DWH

Customer Care Fact:

This fact table monitored the performance of staff members in

handling passenger interactions.

It helped track inquiries, complaints, severity levels, and

feedback rates, contributing to enhanced customer service.