American Airlines

Flight of Revenue 
Along with its regional carriers American Eagle and American Connection, American Airlines serves more than 250 cities in 41 countries with 1,100 airplanes and about 4,400 daily flights. The airline processes about 125 million transportation documents on an annual basis.

With the amount of data created by all these ticket documents, American Airlines needed a more efficient system that would help ensure proper amounts of earned revenue on ticket sales. But because of the complexity of the airline industry and the amount of data involved in ticket transactions, accomplishing this would be no small task.

“Our business analysts have the expertise to spot fraud and other incidents of incorrect revenue flow, but they also need a system that makes it easy for them to quickly query and sort through large amounts of data,” said John Hagen, senior systems analyst and project manager for American Airlines.

Hagen was also concerned with the amount of storage such a system would require. “Some data warehouse technologies expand the amount of required storage by five to ten times after indexing,” Hagen said. “This would force us to purchase as much as two additional terabytes of storage—an expensive proposition.”

Search for a Solution
American Airlines first considered hiring outside consultants to custom-build a data warehouse, but the cost would have likely outweighed the expected return. Alternatively, Hagen and his staff turned to Sybase after its technology significantly outperformed other leading off-the-shelf data warehouses.

“Sybase IQ returned one ad hoc inquiry in minutes compared to one of the other vendor’s software that literally took hours,” Hagen said. “Sybase IQ was consistently 10 times faster than the other two competitors’ software.”

American Airlines integrated Sybase IQ with other data warehouse technologies that combine to give the airline an efficient solution that scales easily. Sun Microsystems provided server and storage hardware while Computer Associates helped deploy its front-end, report-generation software. American named this data warehouse RADAR (Revenue Account Data Access Resource).

“The technology provided by Sybase, Sun and Computer Associates has made it easy for us to keep RADAR within the same configuration over time, and this makes things a lot simpler,” Hagen said. “All we have had to do is add more storage and load system upgrades as our business grows.”

Sybase IQ also integrated seamlessly with American’s legacy systems.

Soaring Results
With the RADAR system, American Airlines now has the data warehouse it needs to detect fraudulent ticket-processing, track ticket sales properly and ensure proper revenue is flowing into the company. “Besides benefiting from faster inquiries, we can also conduct more in-depth analysis,” Hagen said. “The previous system could hold only three months of data, but Sybase IQ allows us to store about 13 months of data, so we can run historical reports over longer time periods.”

Hagen estimates that American saved about $250,000 up-front compared to the cost of developing a similar data warehouse using outside consultants. But the ongoing savings have been even more dramatic. “We originally projected that Sybase IQ would save us about $150K per year,” Hagen said. “But over the past five years, it has saved us about $5 million by helping us identify forms of fraud we never knew existed and by giving us the ability to implement appropriate measures to eliminate the causes.”

The astounding success of the program has caused the number of American Airlines end-users to increase significantly. “The system has actually become a best-practice standard within the company, and many more users are now tapping into IQ Multiplex for information,” Hagen said. “Besides the business analysts, we also have security analysts, auditors and vice presidents taking advantage of the data Sybase IQ provides.”

The system has performed so well that American Airlines has turned down proposals from Big 5 consulting firms to tune their business analytic tools.

Sybase IQ has also reduced the amount of storage American Airlines needs for the data warehouse. “The total storage required by Sybase IQ is about 600GB,” Hagen said. “Without IQ’s compression and column-indexing technologies, we would probably need about two terabytes.”

American Airlines also realizes the impact that the IQ Multiplex solution has had on its customers. “By detecting ticketing errors and improving cash flow, we can keep ticket prices lower,” Hagen said. “What a technology can do for the company’s customer is the ultimate bottom line. IQ Multiplex has clearly had a significant, positive impact. It has turned out to be a win-win for us and our customers.”