What we Built with Alteryx is Awesome. How Can we Make it Even More Awesome?
In May 2021, a targeted cyber-attack on the Colonial Pipeline forced a shutdown of critical gasoline and distillates pipelines across 5,500 miles of the United Sates — from Texas to New Jersey. While the disruption at the gas stations across the southeastern United States received most of the news coverage, another large-scale impact was brewing.
Also affected during the pipeline shutdown was the ability to move and supply many of the nation’s busiest airports with jet fuel. Airports from Atlanta, Washington, DC, and New York were on alert.
Senator Angus King (I – Maine) stated that, “This interruption of the distribution of refined gasoline and jet fuel underscore the vulnerability of our nations critical infrastructure.”
Standing by to help the operators of the nation’s airports manage this crisis was an analytics automation capacity built by the Federal Aviation Authority (FAA) and powered by Alteryx. More to come on that, but first, let’s understand the backstory.
In the recent past, the communication and sharing of data regarding the operational status of an airport — including the supply of jet fuel from individual airport operators, FAA regional organizations, and FAA headquarters — was done by email.
A typical scenario: An email was sent out to the airport, “Hi, we would like to get some information from you”, and then copied and pasted into an Excel spreadsheet that was then emailed again, sending it back up the chain to headquarters.
Across the FAA this created a lot of duplication of effort and increased the opportunity for the corruption of data or mistakes to be made. To alleviate this issue, the FAA developed a SharePoint-based Knowledge Services Network (KSN). In this environment, information on airport status could be updated directly by the airport operators and shared amongst regional and HQ-based FAA administrators. With this SharePoint system, data was sent directly from the airports through workflow management which, for the most part, eliminated the email traffic. Once an airport became activated in SharePoint, they were notified to update their information on a regular basis through a workflow management process. Problem solved, right?
Report Once, Share Widely
With thousands of airports under the National Plan of Integrated Airport Systems (NPIAS), the flow, volume, and structure of data coming into the KSN system or preexisting regional-based systems varied and was not always timely. With the numerous reporting processes, the challenge was discovering an easy way to consolidate this information into operational views by region.
Working with a team of airport partners in Florida who had a preexisting reporting database, the FAA was able to create an API to find, connect, and compile information related to 29 airports. The key to making this work was running the API with Alteryx. This API data was then pushed by the reporting Florida airports to the Florida aviation database. This data is grabbed, and within Alteryx is transformed into the same exact format that the FAA was utilizing in the KSN.
What Alteryx enabled was the capability to extract, transform, and load data from the airports regardless of how the airport was set up to report. What this meant for the airports is that they only had to report the information regarding status once, either into a regional system or directly into the KSN SharePoint-based process. What this enabled the FAA to do was get a consolidated view of airport status across regionals and/or across the country.
The innovation at the FAA didn’t stop with just the ability to automate the collection and compiling of data from across multiple airports. All the information related to 522 different airport status indicators, was multiplied by hundreds of airports results hundreds of thousands of pieces of information.
In the words of the FAA team, the ability to grab all this data made them “Happy and everybody was smiling because it was awesome”, but in the quest for even more breakthroughs, the team asked, “How can we make this even more awesome?”
Using the agility of Alteryx, the FAA team was able to create an app to tell the workflow to only pull the information that was activated from the airports to indicate a change in status (i.e., runway condition, weather, fuel supply, etc.)
Sharing is Caring, Reporting Agility is Breakthrough
The true value in analytics comes from the ability to share insights. With Alteryx, the FAA team was able to automate the reporting and sharing on airport status every time the workflow was run. In this case, the workflow published the results directly to a Tableau visualization that enables a curated view of an airport’s status across the national, regional, or down to the individual airport.
As is the case with most shared dashboards or reports, they don’t fit everyone’s needs. As the FAA team describes it, they had some folks who were like, “Well, that’s great, Tableau is nice, but it doesn’t really lend itself to creating reports” that we need. Not shrinking away from the challenge, the FAA team went back to Alteryx and developed another app utilizing the same workflow to enable the creation of specific or ad-hoc reports based on different filters and parameters needed by users.
The FAA can now use the same workflow to automate the collection, preparation, analysis, and reporting of data across the nation’s airports. In addition, the built-in agility of Alteryx allows for the building of standard visualizations and the ability to meet the demand for customized reports with drag and drop, pick list functionally. As one FAA user described, with Alteryx we get into “really cool application design” where users can come in here and grab information, transform the information and filter it to create the report they need.
This elevated ability to gather data, transform it, and share was invaluable when the FAA Airport Operations team needed to quickly respond to the Colonial Pipeline cyber-attack. Each airport in the affected region was required to provide constant updates on fuel status, and the automated analytic capability of Alteryx empowered the FAA team with the information needed in timely, clear, and concise manner. As one member of the FAA team explained, one region was at first hesitant to participate in the automated reporting process saying “Ah, you know, we may not do this, it’s, it’s good, but it’s not that good”, but when the Pipeline crisis hit and they had to report on fuel supplies across their entire region, a call came in the next day with that region saying “We have decided we are going to do this because it takes so much time and energy to pull these reports and send them up to Excel.”
Analytics Automation in Action
Analytics automation allows the FAA team to get a quick update on the status of each airport which is fed directly into a customizable Tableau dashboard, that can be filtered according to the needs of the users. The automation goes even further by providing automated alerts to preselected individuals or email lists every time a change in status is made at an airport. This enables inspectors to proactively validate the status change and engage the proper notification trees if necessary.
During the Colonial Pipeline situation, the information processed through Alteryx was color coded to indicate when an airport was getting within three to five days of fuel supply on hand. Additionally, with Alteryx’s ability to support geospatial analytics and visualizations, users could hover over a particular airport and get information about the airport, including runway status, air carrier status, and any remarks related to airport status.
As one user at FAA explains, “For high visibility folks who just want to know what’s going on across the nation for activated airports, this is just a very fast way for them to be able to see what’s going on without having to read through an Excel spreadsheet or do anything like that. Very visual and very helpful, all possible because of Alteryx.”
What this FAA and Alteryx story highlights is that data and the flow of insights can be a messy business, but with innovative and agile approaches that leverage analytics automation, insights can be made accessible, informative, actionable, and awesome. And, with data leaders who are creative and empowered what is awesome can be made even more awesome.