Great data, less fussing

Carlson Wagonlit Travel has loads of expertise in routing corporate passengers between destinations efficiently and economically. But the $11 billion travel management company needed help when it came to directing data from myriad sources into a data warehouse. That’s why Carlson Wagonlit Travel took flight with Informatica Corp.’s PowerCenter, a hub system for integrating enterprise information that made the delivery of key analytical data to consultants a less arduous trip.

AT A GLANCE: Carlson Wagonlit Travel The company: Carlson Wagonlit Travel is an $11 billion travel management company headquartered in New York City. Each day, the company handles about 35,000 transactions for corporate customers. The problem: Carlson Wagonlit Travel needed a relatively easy and fast way to route data from online transaction processing systems and other sources into a data warehouse. This would give travel consultants access to the data as well as the ad hoc query and reporting capabilities they need to deliver travel pattern analysis to customers. The solution: With Informatica Corp.’s PowerCenter data extraction, transformation, and loading tool, Carlson reduced the deployment time on the data warehouse from years to months. The tool acts as a hub system for integrating data from disparate enterprise systems via an intuitive, point-and-click interface. The IT infrastructure: Carlson Wagonlit Travel’s source is a transaction-oriented Sybase Inc. SQL Server database; the target is an Oracle Corp. Oracle 8.0 RDBMS system optimized for reporting. The data warehouse architecture also integrates Cognos Corp.’s front-end tool suite, including PowerPlay for OLAP queries and Impromptu for query and reporting.

The PowerCenter data extraction, transformation, and loading (ETL) tool aggregates the appropriate operational and transactional data. The data is then optimized for online analytical processing (OLAP), allowing the New York City-based company’s travel analysts to perform ad hoc queries that uncover key trends for clients. “The types of contracts from suppliers are much more complex; the greater access to a greater level of detailed data, the easier we can help clients,” explains Leslie Schroeder, Minneapolis-based director of industry consulting and analysis for Carlson Wagonlit Travel. “The basic core of our service offering is to take a look at clients’ travel patterns and help them define what strategy to take to align with preferred carriers.”

Although it’s only one piece of Carlson Wagonlit Travel’s data warehousing architecture, Informatica’s PowerCenter was the bridge that connected the various information sources. The company’s source is a transaction-oriented Sybase Inc. SQL Server database; the target is an Oracle Corp. Oracle 8.0 RDBMS system optimized for reporting. Carlson Wagonlit Travel’s databases store information such as their clients’ passenger profiles, travel arrangements, and preferred carriers, as well as airlines’ routes, hubs, and flight information.

With its databases and PowerCenter, “all we have to do is point, click, update, and rerun” to get at the appropriate information, says Jay Vetsch, the company’s director of global information delivery, who’s also in Minneapolis.

Gather the information, and move it around

Carlson Wagonlit Travel isn’t the only company making data extraction, transformation, and loading tools a pivotal stop on their data warehousing itinerary. As data warehouse or datamart projects increase in complexity and span multitier architectures, many companies need to automate the routing of data between systems.

“There is a greater recognition in the marketplace that the most difficult job in implementing a data warehouse is getting the information in,” notes Henry Morris, vice president for data warehousing and analysis at International Data Corp., a market research firm in Framingham, Mass. Without ETL tools, companies have to manually move data between systems–which typically requires complex programming–and manage an information flow across multiple steps, Morris says.

This challenge is spawning a fairly healthy business for ETL products. According to IDC, the market for data warehouse generation tools reached $561 million in 1998 and will grow to $868 million by 2002. The market comprises tools for moving data from various sources into a data warehouse and includes ETL products as well as data cleansing tools.

Along with Informatica, of Palo Alto, Calif., other companies catering to this segment are Ardent Software Inc., of Westboro, Mass., with its DataStage product; Evolutionary Technologies International Inc., of Austin, Texas, with its ETI*EXTRACT package; and Informix Corp., of Menlo Park, Calif., with its Red Brick Systems Formation software.

Data warehouse vendors themselves are expanding their product lines with ETL tool sets. And other companies, including Influence Software Inc., of Sunnyvale, Calif., and Acta Technology Inc., of Palo Alto, Calif., are focusing on data extraction, loading, and transformation tools that bridge SAP AG R/3 enterprise resource planning (ERP) data with data warehouses.

The latter is one area where IDC’s Morris expects a growth surge, since ETL vendors can shoulder the maintenance burden that comes with keeping a tool set current with frequent upgrades to complex enterprise software. “ETL vendors are recognizing that packaged [enterprise] applications are frequently a source [for data warehouses] so they’re taking the responsibility of keeping current as those applications change,” Morris says. “Once you start thinking about that problem, why would a company want to do that kind of maintenance themselves? ETL vendors might not know your legacy applications, but enterprise applications are a known quantity.”

Bridging systems

For Carlson Wagonlit Travel, the decision to make an ETL tool part of its data warehouse infrastructure was driven by a time crunch. Competition in the travel industry, particularly the consulting segment, meant the company’s travel analysts needed faster and more efficient access to aggregated data to perform analysis for clients. Prior to the ETL system, analysts struggled to pull key information out of transactional systems–the methods were slow and complex. “We essentially had one database to do transaction processing and analytical processing and because of that, we were running into contention issues,” says Vetsch. “People running ad hoc queries were crashing into each other and availability was poor.”

With PowerCenter at the controls, Carlson Wagonlit Travel’s team of five technical people and three business liaisons got the data warehouse up and running in about eight months. “The ETL tools reduced the implementation cycle,” Schroeder says. “Without it, it would have taken several years to accomplish the same thing.”

Lessons learned about ETL tools Evaluate your team’s skill set to determine if they can handle hard coding to migrate data between systems. If not, an ETL tool is probably a good bet. Let the winner emerge by itself during the selection process. Present the toughest transformation that delivers the highest business value to all contenders. Be diligent about evaluating the ETL tool’s ongoing ability to address maintenance and subsequent iterations of applications. Are the tools resilient to changing business requirements with aggressive iteration timelines?

In addition to the Sybase source database for transactions and the target Oracle 8.0 RDBMS for the reporting piece, Carlson Wagonlit’s data warehouse architecture integrates Cognos Corp.’s front-end tool suite, including PowerPlay for OLAP queries and Impromptu for query and reporting. Before settling on Informatica’s PowerCenter ETL tool, the firm evaluated five or six other tool sets, including those from Evolutionary Technologies and Platinum Technology, a Computer Associates International Inc. company. PowerCenter was selected primarily for its ease of use. “We have Microsoft [Corp.] Access development experience on the team,” Vetsch says. “PowerCenter is so point-and-click and graphical you don’t have to be a wizard on UNIX or C++ to be up and running. It played to our skill set.”

Efficiency was also behind Hunt Corp.’s decision to make an ETL tool part of its data warehouse strategy, according to Willard Thrash, manager of data warehousing. A year ago, the Philadelphia-based maker of office supplies and graphics decided to redesign its sales analysis data warehouse on a Microsoft SQL Server platform. Hunt picked Ardent Software’s DataStage to move information from the source MFG/PRO for ERP package from QAD Inc. to the target SQL Server platform and related datamarts.

Previously, Hunt used Progress Software Corp.’s 4GL tools to populate and load the data between the two systems. “We chose DataStage rather than use the Progress programming language, which took time and expensive consulting,” explains Thrash. Hunt’s decision to go with an ETL tool also allows the company to change the technology underlying its data warehouse fairly easily. So, for example, if the company decided to switch from a SQL Server database to an Oracle back end, little or no work would be required to redesign the data flow.

Says Thrash: “DataStage is independent of any database, so as long as there is a direct connection or ODBC drivers, it can read and write to any database.”

Given the success and the relative ease with which this data warehouse got off the ground, Thrash and the Hunt Corp. crew see ETL tools as a key stop on any trip to data warehousing. “The ETL tool will be a part of each data warehouse project [going forward] because data warehousing is mostly about moving data from source systems into a central database,” Thrash says. “You need a tool to automate that process. It’s basic plumbing.” //

Beth Stackpole is a freelance writer living in Newbury, Mass. She can be reached at bstack@stackpolepartners.com