In-Depth

ETL Increasingly Used Outside Data Warehouses

ETL isn’t a data warehousing-only play—companies are increasingly tapping it to support database consolidations and migrations, among other tasks

• By Stephen Swoyer
• 03/08/2005

Although Extraction, Transformation, and Loading (ETL) software has been an enabling technology for data warehousing for many years, its use for other applications has traditionally been somewhat limited.

That's changing. According to Phil Russom, an analyst with consultancy Forrester Research, ETL is now being tapped to support non-data-warehousing activities, such as database consolidations and migrations. Right now, he says, one-fifth of ETL use-case scenarios involve non-data-warehousing activities.

“The mix [of data warehousing and non-data warehousing ETL] has shifted slowly over the years, accelerating this decade with the minority percentage [of non-data-warehouse usage] increasing to almost 20 percent,” wrote Russom in a recent research report.

Forrester’s survey sampled several organizations more than once. Taking only the number of discrete organizations into account, Russom says, non-data-warehousing ETL usage rises sharply. “If we instead count organizations, 15 out of 28 had some kind of non-DW usage—roughly half,” he writes. “Two organizations reported 100 percent non-DW usage, showing that ETL needn’t be associated with data warehousing at all. And two other organizations reported non-DW usage as 50 and 65 percent respectively.”

The most common reason organizations begin to deploy ETL outside of the bread-and-butter data warehousing space is to move data between disparate applications. “At 9.48 percent of all ETL use, this is the largest category of non-DW ETL use. ETL may support data propagation, where data moves one way from a system of record to others,” Russom indicates, noting that ETL is also used to support data synchronization efforts, particularly for the purposes of business continuity planning. “This is common in financial services companies where customer data may be entered or changed in multiple systems.”

Elsewhere, companies tap non-data-warehouse ETL to support customer data integration (1.07 percent) and database migration (3.04 percent) efforts.

Finally, Russom notes, miscellaneous cases of non-data-warehouse ETL usage abound. “[S]ome companies use ETL just to transform data and documents from an industry standard [such as HIPAA or EDI] to an internal format and vice-versa. Others apply ETL in creating reference data, managing master data, generating test data from production data, caching data to improve application performance, persisting data for customer self-service, integrating data as part of a fulfillment process, collaborating through data with partners, importing third-party data into corporation systems, and so on.”