Q:  

Any time I get to a data related project I have a choice of ETL tool vs. homegrown solution. Over the years I have created my own methodology for homegrown ETL solutions. This solution "actively" uses meta data to derive transformation logic and apply data quality routines. I have some production processes which I have not modified for three years. All data changes have been maintained by business analysts through modifying meta data. This approach cuts the entire ETL development stage while maintaining DW. Only BA,QA and operations are involved and this leads to cost savings. To me this approach has huge advantages over commercial ETL tools especially given they steep purchase price tag. Your opinions please!

I am also trying to determine a feasibility of the use of an ETL tool (DataStage) and don't see any benefits. Our environment:

  1. Oracle 9i
  2. All data feeds are delivered from subsidiaries in ASCII files.

Am I the only one not seeing any "value add" from ETL tools in such environments?

A:  

Larissa Moss' Answer: It sounds like you have, in effect, created your own homegrown "ETL tool with an embedded business rules engine," which appears to be flexible enough to work for your current data warehousing ETL needs. I am assuming that you could, and would, expand both the ETL code and the meta data capabilities to keep up with any new and different ETL requirements that your customized utility/tool could not handle on future DW releases. Very few companies have invested the time and effort to build a homegrown "ETL tool/business rules engine" sophisticated enough to handle all of their ever-growing ETL requirements for an integrated and expanding DW environment. Most companies buy a commercial ETL tool to avoid the development and ongoing maintenance time and costs of a customized solution (same reason they buy other packaged solutions). In an actively growing DW environment, new databases are added periodically and existing databases are modified to store additional data elements. Even with a flexible business rules engine, the DW rate of change would still require continuous modifications and enhancements to the code of a homegrown utility/tool. Another potential "value added" factor for buying a commercial ETL tool is that a company can relatively quickly replace ETL developers that have experience on a certain ETL tool, without losing time for knowledge transfer from the original utility/tool developer to his/her replacement, or without a long learning curve if the original developer left without notice. And finally, there is the topic of meta data management. Rather than building another customized solution, companies are hoping that the vendors will soon agree to one set of meta data standards, so that even more tools can be developed to extract, merge, cleanse, integrate, display and generally manage and maintain meta data.

Adrienne Tannebaum's Answer: "Active" meta data always reflects what it is describing and/or representing. This is fine if no one ever wants to see "what used to be" or if the data that was described by the previous version of the meta data is always replaced. Your use of meta data seems to apply specifically to one data warehouse? When the meta data changes does everything get recalculated?

One major advantage of ETL tools is their ability to make the "meta data" available to the world outside of development. When your business users see the data, do they know how it was created, where it came from, what was done to modify it? Would they be able to get that info for data warehouse data that has been stored since last year?

If you are evaluating these products purely from the point of view of development time and the fact that you already know most of the "meta data" then you may not see a benefit. But think about all of the mistakes and poor judgments that are made when your users don't have the knowledge that you do. That may be where a benefit will surface.

Les Barbusinski's Answer: Hand-coded ETL can be every bit as cost-effective as using an ETL tool, but only if the overall ETL architecture is sound and well documented, standards are published and rigorously enforced, and the ETL processes can easily be modified, extended and tuned. Most of the time this can only be achieved if your project team is blessed with a good architect and great developers.

Many shops opt to purchase an ETL tool because they trust the vendor to have created a mature environment for developing ETL scripts in an intuitive and modular fashion. They also like the technical support provided by the vendor and appreciate the R&D that the vendor puts into the tool to keep up with the latest technology and best practices.

It sounds like you've developed a solid ETL architecture. However, is it well documented? Intuitive? Extensible? Can it easily be adapted to a real-time environment with hooks into a variety of MOM tools, EAI tools and ERP adapters? Are you the only one who can support it, or are there others in your shop that can readily administer the environment? If your architecture meets these criteria, then you really don't need an ETL tool. Otherwise, you may want to take a good look at the available tools on the market. Hope this helps.

Mike Jennings' Answer: Please refer to my June 2003 DM Review Enterprise Architecture View column, "What's in Your ETL Method?" http://www.dmreview.com/master.cfm?NavID=55&EdID=6799.

Clay Rehm's Answer: Your methodology sounds truly like a well-thought-out design. If it works, then as the old adage says "don't fix it!" If this technology works for you, provides accurate data on a timely basis at a low cost, then I think you are correct in assuming that an ETL tool will not provide you any added value.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access