American is high on IQ

Recent implementation of a specialized, compressed data warehouse helped address a major airline's growing concerns about accessing ticket information. That implementation was timely. For every person that went through the turnstile at American Airlines, another piece of paper landed in American's revenue accounting department. Eventually the number of documents processed by the department grew to 125 million annually. But with such a large data flow, and a database that did not run analytical queries and held only three months' worth of information, the airline was unable to access ticketing data without considerable manual labor.

In March 1997 American Airlines "flipped the switch" on its Revenue Accounting Data Access Resource (Radar), a decision-support data mart that utilizes the Sybase IQ data warehouse server product from Sybase Inc., Emeryville, Calif., to analyze ticket data and uncover the fraudulent use of discount tickets. The system runs on 12 Sun Enterprise 5000 processors and holds 13 months of data. IQ's compression technology holds 300Gb of raw data, which, according to John Hagen, senior systems analyst and Radar project manager, is eight to nine times more than American Airlines' previous database.

The system has helped the airline reduce costs, extend storage capabilities and empower users. Queries take an average of seven minutes, while effective implementation of a three-tier architecture helps prevent query lockups, said Hagen. Instead of utilizing several people to collect faxes and other paperwork to do one ticketing job, one person can now create a query to find the necessary information.

But as with most major projects, Radar faced challenges during its 11-month implementation phase, said Hagen. These included obtaining the needed funding, setting up the database schema and using borrowed hardware. The biggest challenge, however, was the system's own rapid growth.

The system is now used in 15 departments throughout the company and two cities, Tulsa, Okla., and Fort Worth, Texas, where Hagen is located. "[Radar] was designed for 30 to 40 people. Now, a lot more are using it," said Hagen. "We've had to beef up the hardware ... as different departments have different twists [on using it]."

Hagen said American Airlines "bit the bullet on the development cycle." The firm started the project with a consultant. Then, "We hunkered down and learned the system ourselves," he said. "That way, we understood every nook and cranny. That's very important."