As the financial clearinghouse for the air travel industry, Airlines Reporting Corporation (ARC) serves airline carriers and the travel agency community by operating a settlement process for ticket sales between the two groups. But in an industry as multi-faceted as this one, ARC needs to provide its customers with more than the basics of transaction support.

"Our services extend beyond ticket processing and settlement to a full range of analytical and historical reports aimed at providing the entire industry with the most accurate and thorough picture of itself and the trends that affect it," says John Kyle, vice president of marketing and general manager of ARC's data and analytical products division. "We provide the information and reporting tools that our customers need to manage and grow their businesses."

Because of its obligation to 147 air and rail carriers and more than 21,000 accredited travel agencies and corporate travel departments, ARC knew it needed an advanced data management infrastructure to properly support its customers—and lead the way in time and cost efficiency.

Identifying the right system
Early in 2000, ARC launched a major modernization and cost-reduction initiative aimed at simplifying and streamlining some of the industry's most venerable and labor-intensive business processes. By developing an advanced data management infrastructure, the firm hoped to streamline operations, reduce costs and provide greater visibility into the settlement process for all participants.

Eventually, ARC realized that only an enterprise data warehouse could provide the technical infrastructure necessary to address its business objectives: a central repository for ticket sales and settlement data, enough history and detail to support trend analysis and data mining and enough processing power to support access and reporting by the entire carrier and agent community. Such breadth and depth were necessary because, unlike enterprise facilities that provide decision support for a single company, this data warehouse would support the operations of an entire industry.

As ARC began evaluating potential platform solutions for its new data warehouse, the issues of scale and capacity quickly narrowed the field. "We knew that we would be building a multi-terabyte warehouse right out of the door," recalls Randy Black, ARC's director of technical architecture. "We could not afford to get our owners and our industry to sign off on a multi-million-dollar investment, only to have the thing run out of gas at the next application."

ARC soon realized that Teradata offered exactly what it needed. By early 2001, ARC had installed a 10-node 5250 Teradata Warehouse in its Louisville, KY, data center, loaded 39 months of sales and settlement data (about 4.5 terabytes) and launched its new industry data warehouse—ARC COMPASS.

Saving time and money
In each of its principal business objectives, the Teradata-based ARC COMPASS solution has enabled an industry-wide transition from a slow and expensive paper-based ticketing process to the vastly more efficient electronic ticket.

In 2005, ARC processed 200 million paper ticket transactions, compared with 1.2 billion as recently as 1997. In fact, 92% of the tickets issued by ARC customers are now electronic, and the implementation of the Document Retrieval System has generated a $12.5 million reduction in operating costs directly related to the reduced use of paper tickets for agency support documents.

Agents who were once dependent on paper copies of document ticketing transactions now have instant and secure Web-based access to more than three years of sales and settlement history—nearly a billion ticket records at any given time, each appropriately encrypted.

With the phaseout of paper ticketing and the availability of electronic transaction records for daily analysis, fraud detection has also become more sophisticated, automated and effective. Potentially fraudulent activities can now be identified within 24 hours, instead of weeks or months. In fact, industry-wide ticket fraud has been reduced to a level of insignificance due to business intelligence (BI) enabled through the centralized data warehouse.

Business intelligence has helped ARC become more efficient in other areas as well. "We're constantly growing the data and analytical services we offer," Kyle says. "Our reports now range from daily sales and tax summaries to origin and destination analyses, agency performance summaries, carrier reports and more."

Agents and carriers also use the newly accessible data to extract granular insights, discovering, for instance, the top 500 agencies offering travel to specific Mexican destinations, or determining the impact of fare increases, promotions and other marketing initiatives.

The dual active decision
Since the initial implementation of ARC COMPASS in 2001, ARC's Teradata Warehouse has undergone two significant upgrades, one of those being a complete technology refresh in 2005 that gave the firm a fully redundant, dual active platform. The seeds of that transition were planted at the 2003 Teradata PARTNERS User Group Conference and Expo, where Black attended one of the first presentations on dual active architecture by Teradata CTO Todd Walter.

On the plane home, Black wrote a long memo describing how a dual active environment for ARC's data warehouse could significantly enhance the organization's disaster recovery capabilities, improve reporting performance and reduce overall operating costs.

Over the next 18 months, that memo evolved into a detailed business plan to dramatically enhance the data warehouse's technical and financial performance by redesigning its architecture. The decision to proceed was based on three specific objectives:

Reduce operating costs. "Our paramount objective was to reduce the operating costs charged to our Data and Analytical Service product line, which bears all expenses related to the data warehouse," Black says. "We aimed to reduce their expense levels while improving the quality of service we provided to them."

Improve the return on ARC's data warehouse investment. The primary target here was a substantial monthly expenditure for disaster recovery services that provided insurance against a major warehouse outage, but offered no usable processing capacity under normal operating conditions. ARC hoped to reinvest that expense in additional capacity that would improve performance and support new applications.

Enhance disaster recovery capabilities. "At the end of the day, I still had to protect the company in the event of a disaster," Black says. "Whatever we did, I had to have a viable disaster recovery strategy."

A careful analysis revealed that ARC could achieve all of these objectives by deploying a second data warehouse platform and transitioning to a dual active operation in conjunction with a technology refresh scheduled to coincide with the end of its existing lease agreement. So in March 2005, the company performed a complete data warehouse platform upgrade, replacing the existing Teradata system with two new ones, each with six NCR 5400 Server nodes and 22TB of spinning disk. One system was deployed at ARC's primary Louisville data center, the other co-located with the data and analytical products division in Arlington, VA.

Dividing responsibilities
A key decision in the implementation design was to split internal and external workloads, using the Louisville system to support all customer-facing applications and dedicating the new Arlington system to the data and analytical product division's ad hoc analysis. Daily data loads are executed sequentially, loading first at Louisville to meet an 8 a.m. update agreement, and then at Arlington to meet a later 11 a.m. agreement.

The two data warehouse platforms are sized so that either can handle the combined workload. Should the Louisville system experience an extended outage, its workload can be redirected and resumed on the Arlington system, which is kept in synchronization. In the event that all workloads shift to one system, new processing priorities will be implemented to maintain performance and ensure continuous service level agreement (SLA) compliance.

In all respects, the dual active implementation has allowed the ARC team to meet its financial, performance and strategic objectives:

Operating costs have been reduced. By eliminating the monthly disaster recovery outsourcing expense, leveraging new technology performance gains and taking timely advantage of lower finance rates, ARC was able to reduce overall data warehouse-related costs by $580,000 in the first year of its new three-year lease contract, and by $830,000 in each of the following years.

Execution performance has been significantly improved. ARC COMPASS users at the company's data and analytical products division in Arlington had long suffered sub-standard query response times caused primarily by network congestion on the WAN link to the data warehouse in Louisville. With a high-speed local link to what is now essentially a dedicated data warehouse system, those delays no longer exist.

Data warehouse return on investment (ROI) has been increased. The expense that ARC previously incurred for the security of its off-site disaster recovery solution has been replaced with additional processing capacity that is available for day-to-day workloads. This goal was achievable since both systems in a dual active environment actively process work.

Test environment has been enhanced. "We now have the ability to use the Arlington system to test new applications in a full-scale production environment, using a complete copy of the warehouse database, with no risk to our customer-facing applications," Black explains. "If we break something, it will affect our internal users but not our customers."

Availability and disaster recovery capabilities have been strengthened. "Our final objective for the dual active transition was to improve our disaster recovery capability, and we've certainly done that," Black says. "Our previous restore process involved pulling tapes from our off-site storage, sending those to the recovery facility and putting someone on a plane to supervise the recovery. All those steps would inevitably involve some downtime. Now we have two live copies of the warehouse available at all times."

Transforming the travel industry
As a core element of shared infrastructure for data management and BI, the ARC COMPASS data warehouse has provided the air travel industry with a potent infusion of transformational insight and operational efficiency. It has helped ARC and its customers reform their business processes to improve performance, reduce costs and eliminate fraud. It has delivered badly needed visibility for an industry experiencing rapid structural and environmental change. It has proven its ability to continually deliver new business value over time, and it has cemented ARC's position as an indispensable service provider to its industry partners.

"ARC provides the ticketing industry with the world's best settlement engine," Kyle says. "Then we maintain the information from those transactions accurately and thoroughly, letting us serve as the industry's data store. By applying analytics and data services and making reports from those services easily accessible, we become nothing less than the premier provider of knowledge and insight to the travel industry." T

Bill Tobey is a senior technology writer with Ford Sherman, Inc., a communication services agency.

Illustrations by Greg Mably

� Teradata Magazine-September 2006