Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
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.
Describe the business challenge or problem you needed to solve
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.
Describe your working solution
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.
WorkflowThe batch macro
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.
Describe the benefits you have achieved
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!
The entire PowerPoint presentation can be found here. Additionally, visit this link to watch the entire recorded session.