Custom development versus ETL tool

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Data Warehousing, ETL, and business intelligence Analyzing an index after rebuild Speed of low-cost query Data warehouse based on operational data model Method other than DB link for large data transfer Data migration with ETL Basic steps for implementing a DBMS Importing 8i dump file to 9i Using export/import process to clone to data warehouse Areas of impact while migrating database from 8i to 9i/10g External tables explained Oracle data and application integration Users caution to look before you leap with Oracle Fusion Applications Oracle CRM On Demand data integration raises big issues Q&A: Oracle's Lenley Hensarling on integrating Value Chain apps with EnterpriseOne Oracle brings Fusion Middleware into the modern age Risk analysis software now part of Oracle Agile PLM software portfolio SearchOracle.com podcasts Special Report: Collaborate '09 Oracle and Java: Some think advisory board would comfort users Oracle New Year's resolutions, part 1: Advice for navigating 2009 Oracle to acquire application service management firm

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

The question of custom code versus the use an ETL tool is one that we are faced with each time we need to create a data warehouse. Each has its advantages and disadvantages, but for me it comes down to how serious is an organization about the care and feeding of the warehouse.

Creating custom code is a nice solution, it provides organizations with the ability to code exactly what and how they would like their transformation programs to be structured. Let's summarize both side of the custom-code option:

Advantages:

• Low cost (usually have language/compiler in-house e.g.: C++, PL/SQL, Java)
• Code is built just for your needs
• Optimization of programs
• Build what you want, when you want.
• Large knowledgebase of programmers

• Difficult to manage and maintain programs
• Changes to warehouse could have large impact to many programs
• No centralized repository of code
• Limited metadata capabilities
• Longer development cycle
• Debugging is more difficult
• Limited auditing capabilities

Advantages:

• Nice visual interface to create and maintain programs
• Centralized storage of programs
• Version control of programs
• Customization of transforms is relatively simple
• Usually provides good metadata support
• Quick deployment of transformations
• Debugging built into most products
• Transform scheduling
• Transform auditing

• High cost
• Limited user knowledge of most products
• Optimization sometimes is limited due to generic programming methods

For More Information

• Dozens more answers to tough data warehousing questions from Ian Abramson are available here.
• The Best Data Warehousing and Business Intelligence Web Links: tips, tutorials, scripts, and more.
• Have an DW tip to offer your fellow administrators and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical data warehousing questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.