QuickStudy: Extract, Transform and Load (ETL)

After reconciliation, the data is transported and loaded into the data warehouse for analysis of things such as cycle times and total outstanding receivables.

Fryman says customers are using ETL not only for data warehousing and business intelligence activities; they're also moving data from one operational system to another, such as from an ERP system to a CRM application.

One Truth

"ETL allows teams of business users to operate from one version of the truth," says Chet Phillips, IT director for business intelligence at Motorola Inc. in Schaumburg, Ill. The company uses ETL to feed its Informatica data warehouses.

ETL allowed Motorola to collect information from 30 different procurement systems and send it to its global supply chain management data warehouse to analyze what the company was spending in aggregate, says Phillips.

In the past, companies that were doing data warehousing projects often used homegrown code to support ETL processes, says Schiff. However, even those that had done successful implementations found that the source data file formats and the validation rules applying to the data evolved, requiring the ETL code to be modified and maintained. And companies encountered problems as they added systems and the amount of data in them grew. Lack of scalability has been a serious issue with homegrown ETL software.

Providers of packaged ETL systems include Microsoft, which offers data transformation services bundled with its SQL Server database. Oracle has embedded some ETL capabilities in its database, and IBM offers a DB2 Information Integrator component for its warehouse offerings.

There are also third-party vendors that offer bolt-on tools. They include Informatica, data integration vendor Ascential Software Corp. in Westboro, Mass., and Hummingbird Ltd. in Toronto. The software from third-party vendors can offer integration among a wider variety of heterogeneous applications and data structures, says Schiff.

See additional Computerworld QuickStudies

Read more about data warehousing in Computerworld's Data Warehousing Knowledge Center.