Continental airlines case study data warehouse

Case Study

After careful reading of the case material, consider and fully answer the following questions:
1. Describe "active" data warehousing as it is applied at Continental Airlines. Does Continental apply active or real-time warehousing differently than this concept is normally described?

2. In what ways does real-time data warehousing fit with the Continental strategy and plans?

3. Describe the benefits of real-time data warehousing at Continental.

4. What elements of the data warehousing environment at Continental are necessary to support the extensive end-user business intelligence application development that occurs?

5. What special issues about data warehouse management (e.g., data capture and loading for the data warehouse (ETL processes) and query workload balancing) does this case suggest occur for real-time data warehousing? How has Continental addressed these issues?

Continental Airlines was founded in 1934 with a single- engine Lockheed aircraft on dusty runways in the American Southwest. Over the years, Continental has grown and suc- cessfully weathered the storms associated with the highly volatile, competitive airline industry. With headquarters in Houston, Texas, Continental is currently the United States’ fifth largest airline and the seventh largest in the world. It carries approximately 50 million passengers a year to five continents (North and South America, Europe, Asia, and Australia), with over 2,300 daily departures, to more than 227 destinations. Continental, along with Continental Express and Continental Connection, now serves more des- tinations than any other airline in the world.

In 1994, Continental was in trouble. There were ten major U.S. airlines, and Continental ranked tenth in on-time performance, mishandled baggage, customer com- plaints, and denied boardings because of overbooking. Not surprisingly, with this kind of service, Continental was in financial trouble. It had filed for Chapter 11 bankruptcy protection twice in the previous ten years and was heading for a third, and likely final, bankruptcy. It had also gone through ten CEOs in ten years. People joked that Continental was a “Perfect 10.”

Continental’s position in the industry changed dramat- ically over the period 1994–2004. In 1994, Gordon Bethune became Continental’s CEO, and by 1998 he took the com- pany from its “worst to first” position in the airline industry. A key to this turnaround was the Go Forward Plan, which continues to be Continental’s blueprint for success and is increasingly supported by real-time business intelligence (BI) and data warehousing. Currently, the use of real-time technologies has been critical for Continental in moving

from “first to favorite” among its customers, especially among its best customers. Continental’s president and COO, Larry Kellner, describes the impact of real-time BI in the fol- lowing way: “Real-time BI is critical to the accomplishment of our business strategy and has created significant business benefits.” In fact, Continental has realized more than $500 million in cost savings and revenue generation between 1998 and 2004 from its business intelligence initiatives, producing an ROI of more than 1,000 percent.

The Role of Data Warehousing and Business Intelligence

Real-time business intelligence is taking Continental Airlines to new heights. Powered by a real-time data warehouse and strong management leadership around data, the company has dramatically changed all aspects of its business.

Information Wasn’t Available The movement from “worst to first” was only partially supported by information technology. Historically, Continental had outsourced its operational systems (e.g., reservations, payroll, billing) to EDS, and employees had very limited access to data from these systems. Data was locked away in systems that could support operations, but not decision making. Each department had its own approach to data management and reporting. There was no support for ad hoc queries. Employees had to make deci- sions based on intuition rather than on information.

The airline lacked the corporate data infrastructure for employees to quickly access the information they needed to gain key insights about the business. Data was not considered an asset, and was not governed for the good of the enterprise. However, senior management’s vision was to merge data into a single source, with access by employees in all departments.

Enter Data Warehousing Senior management decided to invest in an enterprise data warehouse that all employees could use for quick access to key information about the business and its customers.

This case is adapted from Anderson-Lehman, Ron, Hugh J. Watson, Barbara H. Wixom, and Jeffrey A. Hoffer. 2004. “Continental Airlines Flies High with Real-Time Business Intelligence.” MIS Quarterly Executive 3 (December): 163–176. Revised case study copyright © 2007 by Jeffrey A. Hoffer.

On October 1, 2010, Continental Airlines and United Airlines merged into United Continental Holdings, Inc. This case deals with activi- ties within Continental Airlines before this merger.

CASE STUDY II-2

Real-Time Business Intelligence at Continental Airlines

284

Case Study II-2 • Real-Time Business Intelligence at Continental Airlines 285

The CIO at the time, Janet Wejman, recognized that the warehouse was a strategic project and brought the devel- opment and the subsequent maintenance and support in-house. She believed that the warehouse was core to Continental’s business strategy and should not be out- sourced. Work on the warehouse began, and after six months of development, the warehouse was rolled out in June 1998.

The initial focus of the warehouse was to provide accurate, integrated data for revenue management. Before the warehouse, only leg-based (a direct flight from one airport to another) data was available. Continental could not track a customer’s itinerary from origin to destination when it involved more than one stop because itinerary data were held in multiple data- bases. This limited Continental’s ability to understand a market and customer behavior, and optimize its entire network. The warehouse integrated multiple data sources—flight schedule data, customer data, inventory data, and more—to support pricing and revenue man- agement decision making based on origin-to-destination information.

The data warehouse provided a variety of early, big “wins” for the business. The initial applications for pricing and revenue management were followed by the integration of customer information, finance, flight information, and security. They created significant financial lift in all areas of the Go Forward Plan. Exhibit 1 provides two examples of how integrated enterprise data was initially used at Continental.

Taking Things a Step Farther, with “First to Favorite” Once Continental achieved its goals of ranking first in the airline industry in many performance metrics and of return- ing the company to profitability, Gordon Bethune and his management team raised the bar with a new vision. Instead of merely performing best, they wanted Continental to be their customers’ favorite airline. The First to Favorite strat- egy builds on Continental’s operational success and focuses on treating customers extremely well, especially the high- value customers.

The Go Forward Plan identified the actionable ways in which the company could move from first to favorite. Increasingly, information technology was critical for support- ing the plan’s initiatives. At first, having access to historical, integrated information was sufficient to support the Go Forward Plan and to generate considerable value for the com- pany. However, as Continental moved ahead with the First to Favorite strategy, it became increasingly important for the warehouse to provide real-time, actionable information to support tactical decision-making and business processes.

Real-Time BI Applications

Continental moves real-time data (ranging from to-the- minute to hourly) about customers, reservations, check-ins, operations, and flights from its main operational systems to the enterprise data warehouse. The following applications, ranging from revenue management to flight operations to fraud detection, illustrate the variety of key applications that rely on real-time data.

Demand-driven Dispatch

Prior to the warehouse, flight schedules and plane assignments were seldom changed, regardless of changes in markets and passenger levels. Continental flew flights without fully understanding each flight’s profitability. After the data warehouse, Continental created Demand-driven Dispatch, an application that identifies opportunities for maximizing aircraft usage. The application identifies opportunities to make short-term adjustments that do not disrupt operations. For example, it may be possible to swap one routing of an aircraft without disrupting the crews or the maintenance operations. The swap may assign a larger plane to a flight with unusually high demand. This application is very useful when large events, such as the Super Bowl or Mardi Gras, occur. Continental uses this application to “cherry pick” schedule changes that increase revenue. Demand-driven Dispatch has led to an estimated $5 million dollars a year in incremental revenue.

Good Will Letters

An eight-month good will test showed that even small gestures are very important to building loyalty. The warehouse first determined Continental’s high-value customers by marrying profitability data and algorithms with customer records. The marketing department pulled this data from the warehouse and divided a sample of these high-value Continental customers into three groups. When individuals were delayed more than 90 minutes, one group received a form letter apologizing, a second group received the letter and a trial membership to the President’s Club (or some other form of compensation), and a third group received no letter. Customers who received regular written communication spent 8 percent more in the next 12 months. Another unexpected benefit was that nearly 30 percent of those receiving the President’s Club trial membership joined the club. This translated into $6 million. The concept was expanded across the company to include the top 10 percent of Continental’s customers.

EXHIBIT 1 Some of the Initial Data Warehouse Applications

286 Part II • Applying Information Technology

Revenue Management and Revenue Accounting The purpose of revenue management is to maximize rev- enue given a set of resources. An airline seat is a perishable good, and an unfilled seat has no value once a plane takes off. The revenue accounting area seeks to quickly and accurately record the revenues that Continental generates.

Fare Design Continental understands how important it is to offer com- petitive prices for flights to desired places at convenient times. Continental uses real-time data to optimize airfares (using mathematical programming models). Once a change is made in price, revenue management immedi- ately begins tracking the impact of that price on bookings. And, knowing immediately how a fare is selling allows the group to adjust how many seats should be sold at a given price. Last minute, customized discounts can be offered to the most profitable customers, to bring in new revenue, as well as increase customer satisfaction. Continental has earned an estimated $10 million annually through fare design activities. Prior to the availability of real-time data, Continental’s pricing was a less effective balance of filling seats and optimizing fares.

Ticket Facsimile Prior to the warehouse, paper tickets were scanned and archived on microfiche. To access a ticket for research pur- poses, required finding the ticket number, accessing micro- fiche, locating the particular ticket, and printing it. In 2001, the warehouse team built a report in Hyperion Intelligence (the software was called Brio at the time) to “look like” a facsimile of the ticket, and other airlines and agencies agreed to use this as the standard ticket copy for inter- airline transactions. The report is used to interactively search for one or more tickets in a variety of ways and query the real-time booking, customer, and flight informa- tion in the warehouse. The ability to find and print tickets from the warehouse reduced headcount by eight and saves hundreds of thousands of dollars for Continental.

Airline Reservations The ability of customers to make reservations and airlines to accurately process those reservations is critical. While a data warehouse is not typically thought of as supporting airline reservations, Continental’s warehouse sometimes serves as an emergency backup system because it includes real-time reservation data.

Recovering Lost Reservations In 2002, an error in Continental’s reservation system resulted in a loss of 60,000 reservations. Within a matter of hours, the warehouse team developed an application

whereby agents could obtain a customer’s itinerary and con- firm whether the passenger was booked on flights based on warehouse data.

Another similar situation happened recently when the reservation system had problems communicating with other airlines. In certain circumstances, the system was not sending reservation information to other airlines, and, consequently, other airlines weren’t reserving seats for Continental’s passengers. As a result, Continental cus- tomers would arrive for a flight and not have a seat. The data warehouse team was able to run a query to get the information on passengers who were affected and who had not yet flown. This information was fed back into the reservation system so that seats could be assigned, thus avoiding a serious customer relations problem.

Customer Relationship Management The purpose of customer relationship management (CRM) is to increase revenues, profits, and customer service by knowing customers exceptionally well and giving them great service. Continental’s marketing department uses the warehouse for customer segmentation and target market- ing, loyalty/retention management, customer acquisition, channel optimization, and campaign management. In addi- tional to these traditional CRM applications, marketing has created other innovative CRM applications that leverage the warehouse’s real-time capabilities.

Customer Value Analysis A customer value model using frequency, recency, and monetary value gives Continental an understanding of its most profitable customers. Every month, the cus- tomer value analysis is performed using data in the warehouse, and the value is fed back to Continental’s customer database. Although the value is not adjusted real-time (because some source systems needed for the value analysis can provide the data only once per month), the value is provided to Continental’s customer- facing systems so that employees know who the best customers are.

This understanding helps Continental react effectively in tough situations. For example, post-9/11, Continental used the results of its customer value model to understand who and where their best customers were stranded around the world. Continental applied this information to its flight rescheduling priorities. And, while the schedules were being revised, the company worked with its lodging and rental car partners to make arrangements for its stranded customers. The highest value customer was in Zurich, and he used Continental’s offices to conduct business until he was able to get home.

Case Study II-2 • Real-Time Business Intelligence at Continental Airlines 287

Marketing Insight Marketing Insight was developed to provide sales personnel, marketing managers, and flight personnel (e.g., ticket agents, flight attendants) with customer profiles. They can see how much someone has traveled with Continental and what the person’s value is to the airline. Flight attendants receive the information by reading their “final report,” which lists the passengers on their flights, expanded to include value infor- mation. Gate agents are able to pull customer information up on their screens and drill into flight history to see which high- value customers have had flight disruptions. A commonly told story is about a flight attendant who heard about a high- value customer’s recent flight disruption and apologized on behalf of Continental. The passenger was floored that she would know about the incident and then care enough to apologize. President and COO, Larry Kellner, loves the Marketing Insight application because if someone calls him on the phone, he can input their frequent flier number to identify the customer. He knows immediately if he is speak- ing with a customer who flies every week or once a year and responds accordingly.

A Personal Touch At Continental, like at most companies, a relatively small percentage of customers are responsible for a dispropor- tionate amount of the company’s profits. Using data from the warehouse, each quarter Continental’s top 3,000 cus- tomers are sent handwritten notes from senior management. A note may say, “I see that you live in Houston. Hope that you have tried the Presidents Club, it has wireless Internet access,” or “You checked in on Continental.com. I hope that you liked the service.” Sometimes these customers are sent a personalized report card (based on up-to-the minute ware- house data) that shows the on-time performance of the flights that they were on. The response to this personal touch has been outstanding. Many fliers select carriers on the basis of price, but many of the most profitable customers do it on the basis of the relationships that are created and the attention and service that go along with the relationships.

Elite Access Elite Access is one of the perks that Continental extends to its high-volume, high-profit customers. Qualifying travelers receive priority check-in, priority security screening, priority boarding, priority baggage handling, seat upgrades when available, and additional mileage credit when they happen to be assigned to a middle seat. Prior to the warehouse, marketing assumed that nearly all of the people who qualified for Elite Access were One Pass (Continental’s frequent-flyer program) mem- bers. This proved to be incorrect. Through an analysis of

warehouse data, Continental discovered that 60 percent of the high-value customers were not One Pass mem- bers, and as a result, were not receiving Elite Access service. These customers were going to the airport, standing in (long) lines, not benefiting from high-prior- ity service, and not receiving any seat upgrades. In other words, they were receiving baseline service. With the real-time warehouse in place, these customers are identi- fied as soon as they check in. They receive Elite Access treatment because Continental’s systems can identify them at all of the customer touch points. They do not have to be One Pass members to be recognized.

Flight Operations Operations is concerned with all aspects of getting people to their destinations safely, on-time, efficiently, and with their luggage. This is where customers have either a good or bad flying experience, and Continental works hard to provide excellent service. Good operations also can reduce costs by ensuring that ground personnel are in the right place at the right time. Special real-time applications have been developed to support this capability.

Flight Management Dashboard The Flight Management Dashboard is an innovative set of interactive graphical displays developed by the data ware- house group. These displays are intended to help the oper- ations staff quickly identify issues in the Continental flight network and then manage flights in ways to improve cus- tomer satisfaction and airline profitability.

Some of the dashboard’s displays help Operations to better serve Continental’s high-value customers. For example, one of the displays is a graphical depiction of a concourse, which is used to assess where Continental’s high-value customers are or will be in a particular airport hub (see Exhibit 2). The display shows gates where these customers have potential gate connection problems so that gate agents, baggage supervisors, and other operations managers can assess where ground transportation assis- tance and other services are needed so these customers and their luggage avoid missing flights. In Exhibit 2 it can be seen that Flight 678 is arriving 21 minutes late to Gate C37 and two high-value customers need assistance in making their connections at Gates C24 and C29.

On-time arrival is an important operational measure- ment at Continental. Therefore, another critical set of dashboard displays helps Operations keep the arrivals and departures of flights on time. One display shows the traffic volume between the three Continental hub stations and the rest of their network (see Exhibit 3). The line thickness between nodes is used to indicate relative flight volumes

288 Part II • Applying Information Technology

C37 # 678 SAN 21 min late

C24 #1558 DCA 3*, 5 12 min C29 # 253 OKC 5*, 6 20 min

March 7, 2003 17:00 - 17:30

Terminal B Continental

Continental Express Northwest

B83 B83A B81

B81A B80

B79A B79

B77A

B76A B77

B76 Station

TerminalLink

B87 B88

B70 B71 B71A

B73 B74 B75 B60

B62 B61

B62A B63

B63A B64 B65 B66

C3 0

C3 1

C3 2

C3 3

C34

C35 C36

C37

C40

C39 C38

C42

C4 5

C4 6 C47

C48

C4 3

C4 4

C41

C29

B72 B72A

B68 B69

B86

Station

South Concourse

Customer Service Center Ticketing/Check-in

Presidents Club

Baggage Claim

Continental Express

Immigration

B85A NorthConcourse

Terminal C Continental

Terminal D Continental

KLM

B84A, B84B B84C, B84D

B84E

C27 C2

6 C2

5 C2

4

C22

C21

C20

C19 C18

C17

C16

C15

C14

D- 1 D-

2 D-

3 D -5 D-

6 D-

6A D-

7 D-

10 D-

11 D-

12 D-

8 D-

9 D-

4A D-

4

USO

C23

B85

?

?

?

International Arrivals

EXHIBIT 2 Concourse Display of High-Value Customer Activity

flight information. Another line graph summarizes flight lateness. Users can drill down to more detailed pie charts that show degrees of lateness, and then, within each pie, to the individual flights in that category. Another chart con- centrates on flights between the United States and Europe and the Caribbean, and can show similar critical flight sta- tistics. In all of these elements of the dashboard, high-level views can be broken down to show the details on customers or flights that compose different statistics or categories.

Real-Time Flight Statistics Continental management believes, “You can’t manage what you can’t measure.” Therefore, management has an opera- tions meeting every morning to review airline performance in terms of on-time arrival, on-time departures, baggage handling, and other key performance indicators. Prior to the real-time warehouse, information was refreshed at the end of each day; therefore, managers had to use historical infor- mation to try to improve the day’s operations.

Operational data are now available in real-time (updated each minute), and users can submit queries and understand how the airline operation is performing at any

* Number of flights due to arrive late/ Current traffic arriving into station

IAH

Data Warehouse Group

*

CLE EWR

IAH

Others

1/25 33/55

22/89

0/0 0/0

18/77

0/1

23/74

1/20/13/29

0/1

EXHIBIT 3 Display of Flight Lateness from/to Hubs

and the number of late flights so that the operations staff can anticipate where services need to be expedited. The ratio of the number of late flights to the total number of flights between the hubs is also shown. The operations staff can click on the lines and drill down to see individual

Case Study II-2 • Real-Time Business Intelligence at Continental Airlines 289

moment. Upper management can see in real-time the revenue projections for any flight, where the most valuable customers are while in flight, which ones are affected by delays and cancellations, and bookings as they are made. This helps management make decisions in the event flights need to be delayed or cancelled for weather and other disruptions. They can also run “what-if” scenarios to determine the impact of cancellations, delays, or changes to specific flights.

Fraud Detection and Airline Security Continental uses its warehouse to identify reservations that are not in fare and contract compliance and to profile suspicious booking and ticketing transactions. Fraud also includes the blocking of seat inventory, the selling of tickets at prices lower than allowed (an estimated $60 to $70 million annual risk), fictitious booking records, fraudulent lost baggage claims, and One Pass account redemption abuse. Continental also uses its real-time data warehouse to support airline security efforts.

Fraud Profiles Some interesting applications have emerged as different kinds of fraud have been identified. More than 100 “pro- files” of fraud are run regularly against the data. As poten- tial fraud is detected, it is handed off to a case worker who conducts a formal investigation. For example, one profile looks for reservations agents who make an extraordinary number of first-class bookings. Last year, Continental was able to convict an agent who was manufacturing fake tickets and then exchanging them to purchase new first- class tickets that she would then sell to friends. Continental received over $200,000 in restitution from that one case. In total, Continental was able to identify and prevent more than $15 million in fraud last year alone.

Too Much Travel to Be True A daily report lists Continental’s most profitable customers. A man appeared out of nowhere one day as number one on the list. An alert user did not recognize the name and inves- tigated. She discovered that he had made all of his deposits for frequent flyer points on the same day. She then looked at all of the deposited flights and discovered that he had not flown on any of them. He had counterfeited boarding passes and tickets and bundled them up and sent them in to the One Pass service center. The “revenue” from the dummy tickets shot him to the top of the customer profitability report. A timely report, an alert employee, and the ability to drill into One Pass flight data caught this attempted fraud.

Is It Safe to Fly Immediately after 9/11, planes were ordered to land at the nearest airport. Continental had 95 planes that did not reach their planned destination. Sometimes there were

three or four planes at a little airport in a town with no hotels, and passengers had to move in with the local peo- ple. At Continental’s headquarters, FBI agents moved into a conference room with a list of people they wanted to check. Queries were run against flight manifest data to see if potential terrorists were on flights, and it was only after a flight was deemed safe that it was allowed to fly. Continental Airlines was recognized by the FBI for its assistance in the investigations in connection with 9/11.

Fraud Investigations In the wake of 9/11, Continental realized that they had the technology and data in place to monitor passenger reservation and flight manifests in real-time. A “prowler application” was built so that corporate security can search for names or pat- terns of activities that have been identified as being fraudu- lent. When matches are found, an e-mail and a page message are sent immediately to a contact at corporate security. This capability helps corporate security identify fraudulent activity as it occurs. Not only does this feature enable corporate secu- rity to prevent fraud that is occurring, but it enhances their ability to gather critical intelligence through more timely interviews with suspects, victims, and witnesses.

Supporting First to Favorite with Information Technology

Real-time BI requires the use of appropriate technologies, which build upon and extend those that are used with tradi- tional BI and data warehousing. At Continental, real-time technologies and the associated processes are critical for supporting the First to Favorite strategy.

The Data Warehouse Real-time BI is built on a real-time data warehousing foun- dation. At the core of Continental’s real-time efforts is an 8-terabyte enterprise data warehouse running on a 3 GHz, 10-node Teradata 5380 machine. The warehouse supports 1,292 users who access 42 subject areas, 35 data marts, and 29 applications. Exhibit 4 shows the growth of the warehouse over time.

EXHIBIT 4 Warehouse Growth over Time

1998 2001 2004

Users 45 968 1,292

Tables 754 5,851 16,226

Subject Areas 11 33 42

Data Marts 2 23 35

Applications 0 12 29

DW Personnel 9 15 15

290 Part II • Applying Information Technology

The basic architecture of the warehouse is shown in Exhibit 5. Data from 25 internal operational systems (e.g., the reservations system) and two external data sources (e.g., standard airport codes) are loaded into the warehouse. Some of these sources are loaded in real-time and others in batch, based on the capabilities of the source and business need. Some results of analysis (e.g., customer value analysis) are fed from the warehouse back into the operational systems.

Data Access The users access warehouse data in various ways (see Exhibit 6). Some use standard query interfaces and analy- sis tools, such as Teradata’s QueryMan, Microsoft Excel, and Microsoft Access. Others access data using custom- built applications. Still others use either the desktop or Web versions of Hyperion Intelligence to access data. An estimated 500 reports have been created in Hyperion Intelligence, and many of these reports are pushed to users at scheduled intervals (e.g., at the first of the month, after

External and Internal Operational Data Sources

Complex Queries (predefined and ad-hoc)

Data Warehouse

Business Users and Applications

Continuous and Batch Updates from the Data Sources

Short, Tactical Queries

CRS Billings, Flight Statistics, Human Resources, Issued Tickets, Line-of-flight, APEX Proration, Customer Retention, Interim Revenue, Market Value, Bookings, Revenue, Customer Demographics, Customer Profile, Customer Value, Dining Services, Inventory , Issued Tickets, Market Share, OnePass Deposits, Schedule, Codeshare, Overbookings, System Tables, Agency Profiles, Aircraft Maintenance, CO Flight Attendant Payroll, CO Pilot Payroll, Compensation Events, Gulfstream Revenue, Interline Billing, Payroll, PROS Forecasting, Service Events, Block Space, CO Express Flight Attendant Payroll, CO Express Pilot Payroll, Copa Revenue, eCare, Real-time Flight Statistics, ACI, DOT Revenue, Baggage

Enterprise and Restricted Views

Warehouse Data Back to the Internal Operational Data Sources

EXHIBIT 5 The Data Warehouse Architecture

EXHIBIT 6 Data Warehouse Access

Application or Tool Types of Users Number of Users

Hyperion Enterprise 300

Intelligence—

Quickview (Web)

Hyperion Enterprise 114

Intelligence—

Explorer (Desktop)

Access Enterprise 200

Custom Applications Enterprise 700

Teradata Campaign Marketing 20

Manager

Clementine Data Revenue 10

Mining Management

Teradata QueryMan Enterprise 150

Excel Enterprise many

Case Study II-2 • Real-Time Business Intelligence at Continental Airlines 291

the general ledger is closed). Other products include SAS’s Clementine for data mining and Teradata’s Campaign Manager for campaign management.

Real-Time Data Sources The warehouse’s real-time data sources range from the mainframe reservation system, to satellite feeds transmit- ted from airplanes, to a central customer database. For example, files of reservation data are sent from a main- frame application on an hourly basis. Within the reserva- tion system, the records are not structured in a useful way for analysis. The records are leg-based instead of trip- based (i.e., a trip recognizes a passenger’s true origin and destination), and they are stored in a hierarchical format that cannot be easily queried. Therefore, a passenger name record (PNR) server application reads each file into mem- ory and changes the format of the records from a leg-based perspective to one that includes origin and destination information. The PNR server application then sends the updated records to the warehouse. Passenger data are ref- erenced by many applications, so it is important to control this critical master data.

Other data feeds are streams of real-time data. The flight data (called FSIR, or flight system information record) is sent real-time from the airplanes via satellite to an operations control center system, which supports the command center for Continental where the actual flights are coordinated. FSIR data may include time estimates for arrival, the exact time of lift-off, aircraft speed, etc. This

data is captured by a special computer and sent within seconds to the warehouse.

Other data sources are pushed real-time by the sources themselves. For example, Continental’s reservations system, One Pass frequent-flier program, Continental.com, and customer service applications all directly update a cen- tral customer database. Then, every change that is made to a customer record is sent to the warehouse.

The Data Warehouse Team Continental has 15 people on its data warehouse team. They are responsible for managing the warehouse, devel- oping and maintaining the infrastructure, data modeling, developing and maintaining ETL processes, and working with the business units. The organization chart for the data warehouse staff is shown in Exhibit 7.

Data Warehouse Governance The Data Warehouse Steering Committee provides direc- tion and guidance for the data warehouse. It is a large, senior-level committee with 30 members, most at the director level and above. The members come from the business areas supported by the warehouse and are the spokespersons for their areas. The warehouse staff meets with the committee to inform and educate the members about warehouse-related issues. In turn, the members identify opportunities for the warehouse staff to become involved with the business areas. They also help the warehouse team justify and write requests for additional

Data Warehouse Director

Infrastructure and Application

Development (2 employees)

Infrastructure and Application

Development (5 employees)

User Support (4 employees)

Data Warehouse Manager—Miami

Master Data Modeler

Data Warehouse Manager—Houston

EXHIBIT 7 The Data Warehouse Organization Chart

292 Part II • Applying Information Technology

funding. Another responsibility is to help set priorities for future directions for the warehouse.

Securing Funding The business areas drive the funding for the warehouse. There has always been one area that has helped either justify the initial development of the warehouse or encourage its later expansion. Revenue Management sup- ported the original development. The second and third expansions were justified by Marketing to support the First to Favorite strategy. Corporate Security championed the fourth, and most recent, expansion. This approach helps ensure that the warehouse supports the needs of the business.

The funding does not come directly from the busi- ness areas (i.e., their budgets). Rather, the funding process treats proposals as a separate capital expense. However, the business areas must supply the anticipated benefits for the proposals. Therefore, any proposal must have a business partner who identifies and stands behind the benefits.

The Benefits of Business Intelligence

Continental invested approximately $30 million into real- time data warehousing from 1998 to 2004. Of this amount, $20 million was for hardware and software expenses, and $10 million for personnel costs. Although this investment is significant, the quantifiable benefits from real-time warehousing are magnitudes larger. Specifically, over this same period, Continental realized over $500 million in increased revenues and cost savings, resulting in an ROI of over 1,000 percent.

The benefits from real-time BI at Continental range from better pricing of tickets to increased travel to fraud detection. Exhibit 8 identifies some of the benefits that have been realized. Because there are 1,300 users with ware- house access, it is impossible to keep track of all the ways in which the warehouse has impacted Continental’s bottom line. The data warehouse team knows that many other ben- efits exist that have not been identified. However, when big “wins” are achieved, the benefits are recorded and commu- nicated throughout the company. This helps to preserve the excitement around warehouse use, and it encourages busi- ness users to support warehouse expansion efforts.

Lessons Learned

The experiences at Continental confirm the common keys to success for any enterprise-wide IT initiative— the need for senior management sponsorship and support, close alignment between business and IT strategies, a careful selection of technologies, and so on.

EXHIBIT 8 Sample Benefits from Real-Time BI and Data Warehousing

Marketing • Continental performs customer segmentation, target marketing, loyalty/ retention management, customer acquisition, channel optimization, and campaign management using the data warehouse. Targeted promotions have produced cost savings and incremental revenue of $15 million to $18 million per year.

• A targeted CRM program resulted in $150 million in additional revenues in one year, while the rest of the airline industry declined 5 percent.

• Over the past year, a goal was to increase the amount of travel by Continental’s most valuable customers travel. There has been an average increase in travel of $800 for each of the top 35,000 customers.

Corporate Security

• Continental was able to identify and prevent over $30 million in fraud over the last three years. This includes more than $7 million in cash collected.

IT • The warehouse technology has significantly improved data center management, leading to cost savings of $20 million in capital and $15 million in recurring data center costs.

Revenue Management

• Tracking and forecasting demand has resulted in $5 million in incremental revenue.

• Fare design and analysis improves the ability to gauge the impact of fare sales, and these activities have been estimated to earn $10 million annually.

• Full reservation analysis has realized $20 million in savings through alliances, overbooking systems, and demand-based scheduling.

In terms of data management, Continental learned two very important lessons:

1. Recognize that some data cannot and should not be real-time. Although the initial movement to real- time was relatively easy for Continental, the decision

Case Study II-2 • Real-Time Business Intelligence at Continental Airlines 293

to move additional data to real-time is made with care for several reasons. First, real-time data feeds are more difficult to manage. The real-time processes, such as the flow of transaction data into queues, must be constantly monitored, and problems with these processes can occur throughout the day (rather than just when a batch update is run). And, when problems with data occur, they must be addressed immediately. This puts pressures on staffing requirements. Second, there is a need for additional hardware. Additional ca- pacity is needed to store the data, and each real-time feed requires two servers, one to run the load and a second to back it up. Third, obtaining a real-time data feed from some source systems can be prohibitively expensive (or even impossible) to implement. Because of these time, cost, and difficulty-related factors, data should only be as fresh as its cost and intended use justify. Some daily, weekly, or monthly updates may be adequate for the business.

2. Have the right people in the right positions. Developing and operating a real-time warehouse requires a team with excellent technical and business skills. At Continental, data warehouse staff members in the more technical positions (e.g., design of ETL processes) have degrees in computer science. Some of them previously built and maintained reservation systems before they joined the warehouse team. Consequently, they have experience with transaction- oriented, real-time systems, which serves them well for real-time BI and data warehousing. The ware- house team members who work closely with the business units have previous work experience in the business areas they now support.

In some companies, the warehousing staff has strong technical skills but limited business knowledge, and the business side has limited technical skills but good business knowledge. At the intersection of the warehousing and business organizations, there is a dramatic change in the technical/business skills and knowledge mix. Continental ensures that the ware- house is used to support the business.

The right people are also on the data governance council. The council has authority from senior man- agement and includes a balanced membership from IT, business, and data stewardship roles.

Conclusion

The leadership of Gordon Bethune, the Go Forward Plan, and Continental’s employees moved the airline from “worst

to first.” They helped Continental do what an airline should do—get people to their destinations, safely, on-time, and with their luggage.

Continental’s initial improvements were made in spite of the company’s limited information systems, but management recognized that better information was critical if the company was to improve, grow, become more prof- itable, and provide even better customer service. The com- pany developed better performance reporting systems, shared this information with everyone in the company, and rewarded outstanding performance when the airline as a whole improved.

Even after Continental had moved from “worst to first,” management wanted more. It wanted Continental to move from “first to favorite.” With the First to Favorite business strategy, Continental would strive to become the preferred airline of Continental’s most prof- itable customers. Meeting this objective, however, required much better information than was currently available. Continental had to learn who its most valuable customers were and what kinds of programs and offers were most appealing to them, and then the airline had to use information to provide exemplary service.

To meet these requirements, Continental developed and rolled out its data warehouse in 1998. At the time, man- agement recognized that real-time BI was needed in order to fully support the First to Favorite strategy. Consequently, Continental moved systems to real-time as much as was possible given the source systems and the current technol- ogy and made plans for real-time data warehousing. In 2001, ahead of other airlines and most other companies, Continental implemented real-time BI and data warehous- ing. The use of real-time BI has fundamentally changed how the company operates and its ability to compete in the marketplace.

As noted, data warehousing is commonly described as “a journey rather than a destination.” This is certainly true at Continental. For example, although the Continental data warehouse currently contains 90 percent of the opera- tional systems’ subject areas, the warehouse team is currently working to enhance existing subject areas and to convert more subject areas to real-time.

Continental’s journey is likely to be relatively easy because of the approaches they have taken with real-time BI and data warehousing. The business strategy and real-time BI are in sync. The business units feel that they own the warehouse; the warehouse team maintains the warehouse, and the business areas develop their own appli- cations, with assistance from the warehouse staff. With its approach, Continental is able to use real-time business intelligence to move from first to favorite.