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:
- 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
- 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.
Dig Deeper on Oracle business intelligence and analytics
Related Q&A from Ian Abramson
We are working on the development of a datamart (in 9i) which takes data from two source systems. Since this is a transaction system, there is a lot ... Continue Reading
I am new to Oracle's ETL tool and I need information on data migration using Oracle ETL. Continue Reading
How can PL/SQL be best used for the ETL process? Continue Reading