Alteryx Success Stories
Learn how Alteryx customers transform their organizations using data and analytics.STORIES WANTED
Showcase your achievements in the Maveryx Community by submitting a Success Story now!
SUBMISSION INSTRUCTIONSAA Optimizes Schedule Processing Using an Iterative Batch Macro
American Airlines’ Line Maintenance Strategic Planning department leveraged Alteryx to reduce schedule processing time by 99%. Alteryx was the key to providing new schedule insights and allowing analysis of data that just wasn't possible before. Using an iterative batch macro, they went from extremely manual process, literally red-penning printed schedule, that took over 30 man hours to a 4 minute process.
We analyze future schedules to make sure an aircraft gets enough time on the ground to do all the regular maintenance. We have over 288 destinations, but only 39 are qualified to do maintanance. We have 27 different aircraft types and only certain destinations are rated for certain aircraft types, so it's a big puzzle piece that we have to make sure it's put together when the schedules are made.
We get a turn file, which is a CSV file, and it's a schedule report. For example, it shows that an aircraft is going from JFK to LAX, sits for an hour in LA and then it's going back to JFK. What does it do after JFK? We don't know. This turn file has no way of linking those two together, which is what we need to be able to analyze it properly. So, what we want is the line of flight which is a linear view of flights that an aircraft is scheduled to make. We need to create that line of flight into a data format.
Schedule Analysis Overview
- Receive a .csv turn file from Network Planning via AirVision
- Alteryx model recreates the lines of flight from the turn file in a data format
- Alteryx model then analyzes the turns and lines of flight for various MX qualifications/violations
- Alteryx outputs data into a Mosaic Database
- The output sits into a Teradata database where it's stored for further analysis and visualization using Tableau
Iterative Batch macro
- The key to rebuilding the lines of flight
- Full data is input into the batch macro
- This data is fed into the iterative macro
- The iterative macro outputs two datasets to the batch macro
- Set 1 records that DID meet the criteria
- Set 2 records that DID NOT meet the criteria
- Both record sets are then pushed back into the iterative macro
- This type of “compound” macro is needed when the iteration requires both record sets to loop through
- One set continuously builds on itself
- Second set diminishes and ends the macro when all records are gone.
The batch macro will run multiple times in one workflow. It runs one time for each record that's input into the batch macro and it creates an output after each run. It can improve your performance by grouping inputs into the workflow. For example, if I'm noticing that my workflow is taking a long time because I'm running all the data for all 27 aircraft at one time, I can use a batch macro and just put in one aircraft at a time so it's only running a little bit of data through the workflow. If you're from the programming world, this would be like your for loop.
The iterative macro runs until a condition is met. In the programming world, this would be your while loop. For each record that meets that condition, flows back through your workflow, and if it doesn't meet the condition, it's going to repeat back through that iteration until it finally does meet the condition. So what I'm doing is taking that iterative macro, placing it inside a shell of the batch macro so that we can have multiple outputs circulate back through that iterative macro. It allows me to do what is essentially a recursive join and not only did I have the outputs that were decreasing in size, but I was able to build that line of flight out by building on each record.
We've increased the efficiency by 99 and a half percent from the old process all the way through Alteryx. That's a huge amount of gains. That whole process took about 30 man hours for one 30 day schedule, and the Excel workbooks about three hours. We used before a VBA model that took 45 minutes. Now with the Alteryx model, we can get that done in eight minutes for one 30 day schedule. Half of that time is actually just the process of connecting to the databases. The actual processing of the work takes about four minutes and that's just the 30 day schedule. With Alteryx, I can run look backward through the same model and I go back two years. So, where we could barely do 30 days in Excel, I can do two years worth of data. It's pretty powerful stuff!
