We are using Ascential DataStage version 4.2 and I am having difficulty justifying the cost of the tool for Oracle source to Oracle warehouse loading of information. The elapsed time is not that long when you extract the data out of Oracle just to load it back in. Am I wrong?


Sid Adelman’s Answer: If you already have the tool, why do you need to justify the cost? There’s a lot more than vanilla extract and loading that any ETL tool should be giving you such as more productive ETL coding, meta data, cleansing (lite), loading validation, and better documentation.

Doug Hackney’s Answer: The real ROI of ETL tools is not in productivity. The two biggest upsides of buying an ETL tool are: 1) it gives you a fighting chance to maintain what you have built, and 2) you will pick up some free meta data (mostly technical meta data; almost all useful user meta data needs to be manually populated and maintained). #1 relates to the fact that if you build it by hand with the best and brightest resources, it is unlikely that it will be documented well and even less likely that they will sick around to maintain it. You can turn an average resource into an OK tool jockey, but they will never be a best and brightest developer.

Mike Jennings’ Answer: You are not necessarily wrong, depending on your current business requirements. From a maintenance standpoint, the ETL tool comes with a GUI interface for the developer that provides editing, prompting, syntax checking, DBMS access, security, support for multiple source/target types and project organization (directory/file/component) typically from a single access point. If your project is not to large (two to four developers), ETL transformation rules have limited complexity, ETL window processing times are being met (with recovery slack time), all your sources are Oracle and you are able to effectively perform change management of the processes, then this method is adequate for your current needs. Future business requirements, performance and management needs may compel you to revisit this issue resulting in rework.

Be careful how you code your ETL transformation processes. Constructing the processes to depend on specific DBMS functionality will limit your options to change databases in the future as your warehouse grows.

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