An end-to-end business intelligence case study using Python, PostgreSQL, SQL, and Power BI to assess aviation disruption, passenger impact, regional exposure, and modeled airline losses.
The completed project turns seven source datasets into a reproducible analytical workflow and a four-page interactive report. It connects business requirements to data preparation, source-specific analysis, and practical planning recommendations.
This is a simulated ICAO client assignment, not an official ICAO commission or endorsement. Results describe the supplied scenario rather than verified real-world aviation activity.
- Executive Overview: cancellation and reroute records, affected passengers, fuel cost, and airport exposure.
- Modeled Financial Scenarios: airline-loss rankings, daily estimates, and ratios within the appropriate financial population.
- Geopolitical & Operational Disruption: operational timelines, cancellation reasons, airport impact, and airspace closures.
- Regional Risk & Aviation Corridor Vulnerability: directional route burdens and separate regional airport, airspace, and financial comparisons.
- Passenger impact: 2,200 cancellation records contain 575,710 affected passengers.
- Reroute burden: 1,500 reroute records add 2.20 million kilometres, USD 11.66 million in fuel cost, and 4,706.30 delay hours.
- Regional concentration: the Middle East accounts for 56.12% of airport-affected flights and, separately, 77.85% of modeled summary airline loss.
- Corridor concentration: Europe to Middle East leads reroute activity with 196 records. The reverse direction leads regional-corridor cancellations with 251 records.
These findings support targeted contingency reviews, passenger-support planning, and separate financial scenario discussions. The case study explains the evidence behind each recommendation.
Raw CSV files → Python validation and cleaning → Clean UTF-8 files
→ PostgreSQL tables → SQL analysis and views → Power BI Import model
The implementation contains seven ETL scripts, shared validation helpers, seven analytical tables, six SQL analysis scripts, and nine views. The Power BI model uses explicit measures and separate date, airline, geography, and severity dimensions.
A key modeling decision is to preserve different source populations. Detailed cancellations and reroutes are not forced to match financial summary counts. Airport and airspace exposures remain separate, and undated financial values are not presented as dated operational trends.
See the architecture and database and semantic-model diagram.
- Business Requirements: client brief, 24 questions, scope, and acceptance criteria.
- Preliminary Data Assessment: source coverage, quality checks, and preparation decisions.
- Technical Documentation: transformations, SQL objects, final Power BI model, and reproduction instructions.
- Executive Case Study: completed findings, dashboard pages, and recommendations.
Clone the full repository. Use Python with requirements.txt, PostgreSQL with psql, and Power BI Desktop with PBIP support. Follow the technical document for the setup order, then open the PBIP project and connect to your local aviation_bi database. Database setup includes destructive rebuild steps; use a dedicated local project database. Never store credentials in the repository.
Data: Global Civil Aviation Disruption 2026 Iran–US War by zkskhurram, licensed under CC BY-SA 4.0. Raw files in data/raw/ are unchanged. Cleaned files in data/clean/ remain under CC BY-SA 4.0; preparation includes text trimming, date formatting, three airline-name aliases, closure timestamp formatting and duration validation, and conflict-location parsing. See the Technical Documentation for the complete transformation details. Code: MIT. The MIT code license does not cover the source or cleaned datasets.
Authors: Dhaerya Nauni and Honey Aggarwal