Pricing & route analytics

Flight Tracking DB

A project by Mohammed Ashraf Hassan — Data & BI Analyst

Pricing & route analytics

300.2K

Flights analysed

20.89K

Avg ticket price

12.22

Avg duration (hrs)

6bn

Total ticket value

The question

With 300K flight records, which airlines, cabin classes, and booking windows actually drive ticket price — and where does price stop being about distance?

What I built

A three-page Power BI report on a star-schema model with airline, city, class, departure-time, and arrival-time dimensions. Pages cover an executive summary, a pricing and booking deep dive with correlation analysis, and a route-level view.

What it revealed

Business class is 11.12% of tickets sold but a far higher share of value, with average business fares several times economy.

Duration barely predicts price: the correlation plot flattens after the first few hours, so scheduling — not distance — sets the fare.

Fares stay flat until roughly 10 days before departure, then spike sharply — a clear last-minute pricing window.

Vistara and Air India dominate both flight volume and premium pricing; the six busiest city pairs cluster around Chennai, Bangalore, and Kolkata.

Power BI Power Query DAX Star Schema

Report pages

Flight Tracking DB landing page with navigation to executive overview, pricing and booking, and route analysis pages — Report landing page with guided navigation

Flight executive overview dashboard showing average ticket price, flight volume, trips per airline and ticket share by class — Executive Overview — volume, price and class share

Flight pricing dashboard showing duration versus cost correlation, cost variance by stops and class, booking window price trend and time-of-day price matrix — Pricing & Booking — correlation, booking window and time-of-day pricing

Flight route analysis dashboard showing average travel duration by route, top five city trip pairs and flight counts by source city and stops — Route Analysis — durations, top city pairs and stop mix

Data model