Migrating legacy data: how to get there from here - data warehousing - includes related articles on selecting a vendor and worldwide revenue - includes table of products - Technology Information

Software Magazine, Jan, 1996 by Richard Adhikari

Creating reports for management has been a hellish proposition at Valero Energy Corp., a $2.8 oil and gas company based in San Antonio. "Every day, hundreds of people do their own file transfers out of DB2 on the mainframe and Quattro Pro or Paradox systems, interpret the data and pass it back up as reports to management," said David Palmer, lead database administrator. Because users often take different approaches to the same data, management was getting "10 different sets of reports" on one issue, said Palmer, adding that the multiple queries slowed down the corporation's IBM ES 9000 mainframe.

Such problems are common with legacy systems; databases are so large that users may query outdated data, or run queries from different points of view on the same data and come up with different results.

In an attempt to resolve these problems, many corporations are building data warehouses -- which offer targeted, up-to-date information -- that run on another box to take the load off the mainframe. In a survey of senior IS managers at Fortune 1000 firms, Forrester Research Inc., Cambridge, Mass., found that 96% plan to implement a data warehouse. Of these, 60% expect data warehouses to improve overall access to corporate data, while 31% see them as part of a broader corporate strategy to improve business processes, offer better customer support and identify new opportunities.

However, building a data warehouse is not simply a matter of shifting data from legacy systems into database systems running on smaller platforms. IS managers face challenges ranging from the tactical -- what tools to use to extract, scrub and migrate the data -- to the strategic -- should they draw up an enterprise model and a data warehouse model first, or should they just plunge in? Many MIS executives select commercially available tools, while others write their own routines. The decision depends largely on environment, business and technology strategies, and existing infrastructure.

Today, there's a multitude of tools for copy management, replication and metadata modeling, and deciding among them is no small task. Jim Salvi, project manager at The Clorox Company, Oakland, Calif., shortened the process by hiring Meta Group Inc., a Stamford, Conn., market research firm, to advise him.

"They narrowed the extract tool vendors to three candidates -- Carleton [Corp., Burlington, Mass. , Prism [Inc., Sunnyvale, Calif.] and ETI [Evolutionary Technologies International, Austin, Texas]," Salvi said. After the vendors demonstrated their products, Salvi chose ETI's Extract tool suite because he felt it "had a few more strengths" and could be useful in merging data from any companies Clorox might acquire.

Clorox has 20 legacy databases for marketing, order management, sales management, inventory and other corporate uses. These run on an Amdahl mainframe in Vsam and DB2. The mainframe is linked to about 1,000 PCs running Microsoft Windows and 3270 terminal emulation. Clorox is moving data off the mainframe into a client/server environment consisting of Oracle7 running under HP-UX.

As with other corporate legacy databases, the files are. what database administrators call "dirty." "Sometimes there are records in our files with missing fields or with product description fields that are misspelled or spelled differently, for example," said Salvi. At Clorox, data is either cleaned up manually by the department that owns it, or by using ETI's Extract, Salvi said. The Extract suite generates data collection, conversion and migration programs in the appropriate language to run in spurce and target systems.

Salvi uses CA-XCom from Computer Associates International Inc., Islandia, N.Y., to move legacy data into the data warehouse. End users access the Oracle data warehouse using the Analyzer access tool and perform analysis with the Express multidimensional database, both from IRI Software, Waltham, Mass. Salvi expected to roll out access to the warehouse to all Clorox sales and marketing departments by the end of 1995.

For his part, Valero Energy's Palmer selected Carleton's Passport to generate code on the mainframe for cleansing legacy data, and InfoPump from Platinum Technology Inc., Oakbrook Terrace, Ill., for transferring data from DB2 to the Oracle7 data warehouse. Before deciding on Passport, Palmer looked at SQL gateways, but decided against them.

"Dynamic SQL will kill you," he said. "It spends all its time compiling and linking statements in SQL and that will bring the mainframe to its knees." Passhort creates static SQL that does not require compilation and linking, he said.

Palmer said he selected InfoPump for data transfer because it lets users move data to and from relational databases, host systems, Ascii files and Lotus Notes.

"You tell InfoPump what the source and target are, compile a basic script and use the scheduler that comes with InfoPump to run things," Palmer said.

For Mark Poole, manufacturing systems manager at the Harris Semiconductor Division of Harris Corp., Melbourne, Fla., business issues posed the biggest challenge in creating a data warehouse. The $600 million division has factories in Melbourne, Mountain Top, Pa., Findlay, Ohio, and Kuala Lumpur, Malaysia, each with its own method of calculating yields and its own database running on its own platform. Systems consist of Amdahl mainframes running CA-IDMS, VAXes running RDB, and HP 3000s running the IC-10 database under MPE/ix. Harris Semiconductor is in the process of migrating data from these systems to a central Ingres data warehouse running under Ultrix, so all facilities will have a common means of calculating yields.