Flight data analytics on US DOT data
Avatics
Analytics on the public airline data the US Department of Transportation publishes: ten years of Bureau of Transportation Statistics fare and on-time records loaded into Azure SQL, Power BI dashboards on top, and an AI assistant that answers questions about the data in plain language. Built as a project. Contact us to scope yours.
Features
- BTS Airline Origin and Destination Survey (DB1B): ticket, market and coupon fare records from 2015 to the latest published quarter, more than 800 million rows
- BTS On-Time Performance: scheduled and actual times, delay causes and cancellations by carrier, route and airport
- Power BI dashboards by carrier, route, airport and quarter, refreshed as BTS publishes new data
- An AI assistant that turns a plain-language question into a query and a chart
- Loaded, checked and kept current by ForIT on Azure SQL
What the analysis covers
The US Department of Transportation publishes, through its Bureau of Transportation Statistics (BTS), the most complete public record of how US airlines operate and what passengers paid. Avatics loads those datasets into one Azure SQL database, keeps them current as BTS publishes new quarters, and puts Power BI and an AI assistant on top, so a question that used to take weeks of spreadsheet work gets answered in minutes.
Fares and markets: the DB1B survey
The Airline Origin and Destination Survey (DB1B) is a 10% sample of the airline tickets sold in the US, published every quarter since 1993. Avatics carries the ticket, market and coupon tables from 2015 to the latest quarter, more than 800 million rows, and answers questions like: what did passengers pay on a route, how did a carrier's share of a market change after a merger or a new entrant, which connecting itineraries carry the traffic, and how fares moved by distance and by quarter.
Delays and cancellations: On-Time Performance
The Reporting Carrier On-Time Performance dataset records the scheduled and actual times of every domestic flight the reporting carriers fly, with the cause of each delay and the code for each cancellation. Avatics uses it to show on-time rates, delay minutes and cancellations by carrier, airport and route, and how one day or one week compares with the same period in other years.
Dashboards and an assistant that writes the query
Power BI Embedded dashboards give the standing views: carrier and route comparisons, quarter over quarter trends, airport rankings. For everything else there is the assistant: ask in plain language, and it writes the query against the data, runs it inside the guardrails we set, and returns the table and a chart.
How a project works
Avatics was built as a project for one client with a specific set of questions. We scope your questions, load the DOT datasets they need and any of your own data that belongs beside them, build the dashboards, and hand your team a workspace it can use. Contact us and tell us what you need to know.