Organizations have long relied on in-house developers to hand-code scripts for data integration projects. Yet, for almost 20 years, they've had alternatives in the form of extract, transform and load (ETL) tools and data integration platforms. But many companies still use hand coding for data integration projects -- including, until recently, Canadian retailer Home Hardware Stores Ltd.
Two years ago, when Home Hardware was upgrading its merchandising system, a consultant encouraged the company to consider data integration software, according to Will Buddell, senior systems analyst with the St. Jacobs, Ont.-based retailer. The hardware, building materials and furniture retailer had to merge its product database with an acquired company's product database. With about 100,000 different products, combining two Oracle databases was no small integration task.
"We had an internal development team that produced a test XML file. Time on the books for them to produce it was about 20 hours," Buddell said. "With [Data Integrator], we produced the same XML file in about four hours. We used that as the basis to go forward, because it showed that we could reduce our development time drastically."
Hand coding wouldn't have been difficult, Buddell said -- just time-consuming. In contrast, it took only a day or so to set up the Data Integrator software. The pre-built database connectors and a drag-and-drop interface for source-to-target mapping made it especially user-friendly, he said. When it was actually time to merge the data for phase one of the project, it only took about two hours. Integration for other phases of the project sometimes took longer, but not as long as hand coding would have taken, Buddell said. Plus, time is money. Home Hardware estimates that the software helped it save anywhere from $200,000 to $500,000 (Canadian dollars) during the project, he said.
Why use data integration software?
Productivity gains are a big reason that many companies use data integration software, according to Rick Sherman, founder of Stow, Mass.-based consulting firm Athena IT Solutions. In addition to saving time, many common, repeatable functions are coded into the software, so that companies don't have to "reinvent the wheel" every time they do an integration project. That's not all.
"An ETL tool or data integration platform means that you have all of the code in one place, documented and managed for version control," Sherman said. "With hand coding, you have code all over the place, little management and little documentation."
That's a problem when companies need to replicate integration procedures, troubleshoot or change code, he said. And then there are issues like auditability and transparency, since regulations like SOX or HIPAA demand that companies be able to trace data back to their original source systems. Data integration software helps by automatically documenting information about data sources and transformations, making for an easier audit trail.
Many still don't see the benefits
However, many companies still don't use data integration software, Sherman said. His experiences as a consultant indicate that while Fortune 500 companies often use data integration software for a large data warehouse, it's common for them to revert to hand coding for data marts, operational data stores or other projects.
The top inhibitor is likely cost, Sherman said. While available ETL tools run the functionality and pricing gamut, a robust integration platform can be a six-figure proposition for a large company, he explained. That's because the scale of the project affects the price. Data integration software is often either priced by concurrent CPU usage or by the number of source and target systems. Beyond licensing costs, training is often required for platforms, he said. Add this all up and many companies see in-house development as the path of least resistance.
"There's seemingly no cost to coding, just the person you have doing it," Sherman said. "But, when that programmer leaves -- who's going to maintain that person's code? If you worry about what you would do, you probably need an ETL tool."
Justifying the cost of data integration software
Certain systems may justify the use of data integration software more than others, Sherman added. While a one-time data aggregation project may not require software, any system updated or accessed frequently is a good candidate. For example, a business intelligence system, with data loaded on a regular basis, could benefit from the productivity gains of software. Systems accessed by many business users, or applications that will be expanded over time to encompass more data sources, may also benefit. Additionally, any sort of financial reporting system, especially those supporting SOX compliance, has good reasons to rely on data integration software.
The cost justification for data integration software is often more qualitative than quantitative, Sherman said. There's not a straightforward ROI equation. But the soft benefits -- especially around auditability and transparency -- are often compelling enough on their own to convince CFOs and CIOs to sign off on purchases, Sherman said.