An R&D statistical study at a Fortune 100 airline suggested that millions of dollars each month were being lost due to lost revenue. The cause of the lost revenue ranged from simple ticketing mistakes and training issues to intentional fraud. But the approach used in the R&D study had the following limitations:
- Statistical sampling does not identify specific cases. To take action specific tickets must be identified. Also, some tests require identifying patterns for individual employees or customers, which requires very large samples, preferably the entire database.
- Sources other than the actual ticketing database were used because of its complexity and critical production system availability. But working against anything less than the production ticketing database introduces the possibility that the results can be disputed.
- Not all tickets could be inspected because of the volume of ticketing data. Over 500 million records, from approximately 40 entities comprising 40 million tickets needed to be scanned. This had to be done for over 10 different specific lost revenue detection tests.
- Certain types of detection tests could not even be attempted in the study because of the complexity of the logic involved.
The airline agreed to put Geneva ERS to the test. In a 14-week effort, a nine-member team performed the following:
- The detection test business logic was defined, and data mapping from the business logic to the database performed,
- Custom code was developed to scan the CA IDMS ticket database, and execute other complex logic
- An architecture was developed, Geneva ERS installed, and the database structures defined within the tool,
- Geneva ERS “views” or queries were created to produce four files (virtual and physical) and over 10 violation reports,
- The queries were executed and refined dozens of times against test databases about 1/6th the size of the production database.
Executions against the production database required scanning the 500 million records in approximately 1 ½ to 3 hours wall clock time and from 3 to 6 hours CPU time. The ticket database was scanned using 30 parallel processes, ultimately reading 170 different files. All detection tests were resolved in one scan of the production database.
The results validated the dollar values estimated in the R&D study showing that over $6 million annually were being lost in one area alone, and millions more might be reported incorrectly. It also provided insight into some areas that had never been investigated before. But more importantly, Geneva ERS identified specific cases which could be investigated and collected. The evidence was so solid that certain employees were dismissed as a result of the investigation.
The following deck proposed similar projects to other airlines.
