NATS, which manages all flights in UK controlled airspace, has achieved unprecedented results using Microsoft's SQL Server and Azure
By 2017, the number of global airline passengers is expected to hit 3.91 billion, a 31 percent increase over 2012. And unless action is taken, global CO2 emissions are expected to be a staggering 70 percent higher in 2020 than they were in 2005. The numbers add up: more people in the air, more pollution, and more risk. Changes need to be made, but how? The answers are buried somewhere in the terabytes of complex data generated by flight traffic. NATS, which manages all flights in UK controlled airspace including traffic for the iconic and busy Heathrow Airport, was uniquely poised to find answers— but only if it had the right tools.
Harnessing flight data to transform UK airspace with SQL Server and Azure
Managing air traffic in UK airspace produces huge volumes of complex data covering all aspects of flight operations, from a flight’s planned trajectory, to its radar tracked movements. Using a variety of technologies, NATS had been performing sophisticated analytics on flight data across UK airspace for more than 20 years, but had hit a wall in terms of the data volume and complexity, which was only increasing. The data had become increasingly difficult to analyze in a timely manner to deliver maximum benefit to the business—at last count more than 15 terabytes of highresolution airspace operations data had been amassed in four years alone, and this was only a subset of the available data which NATS’ operational systems generate 24 hours a day, 7 days a week.
“For NATS, it’s about the ability to look for hotspots, identify the underlying causes, make changes, and then monitor the benefits. That’s how we’re using Azure to add to the continuous improvement of the overall air traffic network across the UK.” - Gavin Walker, Chief Information Officer, NATS
The company performs BI on business issues ranging from analyzing and monitoring safety to service and value performance. It’s most critical focus is analysis and optimization of traffic management and flight efficiency. “We have a number of different systems that we use to control traffic across the whole of the UK and out across the Northern Atlantic Ocean, and all of those systems generate massive volumes of data,” says Raymond Lim, BI Manager at NATS.
NATS processed the accumulated information, which included flight plans and national radar data, from more than 6,000 flights each day for valuable business insights. The process had become time- consuming, and it was challenging to get actionable insight to the business in time to inform and support decision making. The challenge would only grow exponentially as air traffic inevitably grew in the years ahead and the volume and resolution of data increased. Analysts were already struggling with the huge data sets and multiple tools. “We had the analogy that the analytics teams were like a box of straws on its side,” adds Gavin Walker, Chief Information Officer at NATS. “Each process that they did was very similar but different, so it was hugely inefficient. They spent 80 percent of their time manipulating the data, and 20 percent of the time actually analyzing it. We wanted to turn that ratio around.”
Overcoming reporting bottlenecks and a potential two-year delay
NATS had collected two years’ worth of information in a data warehouse through natural growth, but realized that it would need to look at a minimum of four years of information—about 15 terabytes worth of data —to get the insights it needed. The problem was that it would take an estimated full years worth of 24/7 processing to create the data warehouse data store the business needed.
Reducing fuel consumption and pollution is an ever-present goal too. In 2008, NATS was the world’s first air navigation service provider to publically commit to reducing air traffic carbon emissions. With a goal of reducing CO2 emissions by an average of 10 percent per flight by 2020 measured against a 2006 baseline, NATS estimated that even a 1 percent reduction would save airlines about GB£36 million (US$51 million) annually. It had already made good progress toward meeting its goals, but looked forward to more gains with a step change in data capability.
Taking flight data to the cloud
To solve these challenges, NATS put together a small team of specialists tasked with executing the company’s BI strategy. The team concluded that it was time to reconsider its on-premises strategy and look to the cloud instead to meet its time-sensitive challenges. It realized that SQL Server on Microsoft Azure would provide the on-demand scalability it needed, without the time and cost involved in building out a data center.
“With our SQL Server solution, we can analyze a flightplan versus a flight’s actual trajectory and accurately model fuel usage and CO2 output to understand the effect of network influences and operational decisions. This enables us to strategically manage the air traffic operation with more information to hand, and have more confidence in our decisions.” - Ray Lim, Business Intelligence Manager, NATS
NATS began running its data warehouse schema in the cloud, with an architecture that included 250 Azure Virtual Machines running SQL Server in parallel. The data warehouse cloud solution integrated local and national radar data, weather readings taken at 20-minute intervals, and more than 6,000 daily flight plans— or 2.2 million per year. In addition to loading data, the data warehouse also runs multiple algorithms that are used to calculate CO2 emissions, analyse flight path conformance, and analyse the management of air traffic for environmental, capacity, and safety efficiency gains.
On the front end, NATS uses a variety of interactive reporting interfaces, including SQL Server Reporting Services and SharePoint, to take advantage of technologies like Power View and Bing Maps for enhanced visualization of air traffic spatial data.
Gaining unprecedented results
Powering through four years of data in less than a month, the turbo-charged data warehouse handled more than 15 million rows of radar data per day in addition to simultaneous, complex ETL processes. Each Azure Virtual Machine independently processed seven days of data at a time, which was then merged to create a unified data warehouse environment.
Within a matter of weeks, not years, NATS had created a data warehouse store of four years of processed and enriched data. This unlocked huge potential for analyzing the performance of our airspace network operation and reducing CO2 and increasing flight safety now and in the future.
“With our data warehouse in SQL Server on Azure, we have been able to massively process multiple years worth of high resolution radar and flight plan data to better understand fuel burn and CO2 emissions from UK flight operations. From this enhanced understanding we can focus fuel and CO2 efficiency programmes to improve operational performance for the airlines that traverse our airspace." - Ray Lim, Business Intelligence Manager, NATS
NATS is also fighting CO2 emissions more successfully. “We promised to cut CO2 emissions by 10 percent per flight by 2020, and as a secondary target, we said that we would reduce emissions four percent per flight by 2015,” says Lim. “With our SQL Server solution, we can analyze a flightplan versus a flight’s actual trajectory and accurately model fuel usage and CO2 output to understand the effect of network influences and operational decisions. This enables us to strategically manage the air traffic operation with more information to hand, and have more confidence in our decisions. Broadly speaking, the higher the aircraft fly, the less fuel they burn. And instead of holding a flight in a circling pattern before landing, we can sequence it so that it descends continuously to the airport and burns less fuel then as well. We now have more data on how and why this happens and have gained the insights and learning from these to positively influence operations in future”. NATS recently achieved their interim miles tone, enabling a 4.3% reduction of ATM related CO2 emissions which corresponds to an enabled CO2 emissions reduction of over 1 million tonnes.
In addition, the company looks forward to enhancing its capability for predictive analytics, using tools such as Azure Machine Learning to improve air traffic operations. “We can start looking at data both backward and forward,” says Walker. “So we can combine historic information like radar and incident data with information about weather and other events to identify trends and better manage air traffic and avoid ever having another incident.”
And the interactive, self-service BI tools are already improving efficiency of NATS analysts and managers. “The analysis that used to take us 20 days now takes about five minutes,” says Walker. “So instead of getting a monthly view of performance, we can get one every 15 minutes.”
This more timely, detailed, and accurate insight can be used by air traffic control centres and airports to improve performance across multiple areas, from infrastructure management and fuel conservation to traffic and security. “And for NATS, it’s about the ability to look for hotspots, identify the underlying causes, make changes, and then monitor the benefits,” says Walker. “That’s how we’re using Azure to add to the continuous improvement of the overall air traffic network across the UK.”
Click here to download Your guide to unlocking organisational growth with data