Alteryx Use Cases

Read Alteryx customer stories to learn how they transform their organizations into becoming a data-driven business.
Analytic Automation That Adds Up | With Alteryx Analytic Process Automation™️, organizations can realize top-line growth, bottom-line savings, risk reduction, and efficiency gains. Calculate your ROI

AA Optimizes Schedule Processing Using an Iterative Batch Macro

Alteryx
Alteryx
american-airlines-logo.png
 
 
 
 
 
Overview of Use Case

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

  1. Receive a .csv turn file from Network Planning via AirVision
  2. Alteryx model recreates the lines of flight from the turn file in a data format
  3. Alteryx model then analyzes the turns and lines of flight for various MX qualifications/violations
  4. Alteryx outputs data into a Mosaic Database
  5. 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.

WorkflowWorkflowThe batch macroThe 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!

  
Related Resources
The entire PowerPoint presentation can be found here. Additionally, visit this link to watch the entire recorded session.