EXPERT RESPONSE
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
Disadvantages:
- 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
Using an ETL tool is the other side of the loading equation. An ETL tool
usually provides a nice interface for users to create and manage
transformations, let's look at the advantages and disadvnatges of these
tools:
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
Disadvantages:
- High cost
- Limited user knowledge of most products
- Optimization sometimes is limited due to generic programming
methods
The bottom line is can you afford not to get an ETL tool at some point in
your data warehouse lifecycle? I think not.
For More Information
|
