Evaluating ETL Tools

When using an ETL tool, the transformation specifications get translated into instructions for the ETL tool. These instructions can then be stored as technical meta data in the meta data repository. Expanding the ETL process and running regression tests are made easier with the tool because there is less human intervention and therefore fewer chances to introduce errors.

When evaluating ETL products, follow these steps.

1. Perform a cost-benefit analysis to compare licensing (buying) an ETL product with building the ETL process in-house. Although both options can be expensive for different reasons (licensing a very sophisticated ETL tool is expensive, but so is maintaining custom-built software), your first choice should be licensing an ETL tool. If the ETL tool cannot handle all of the required transformations, supplement the licensed product with your own specialized code for those transformations. If your transformation requirements are simple and you have a small budget, you may want to buy a less sophisticated ETL tool or consider building your own ETL process.

2. Compile a list of ETL products and vendors that are likely to meet your requirements. Attend trade shows to learn more about the products and vendors. Caveat emptor be a cautious and skeptical buyer.

   

   Let your transformation and cleansing requirements not vendor hypedrive your product evaluation and selection process.

3. Compare the ETL products and vendors to your weighted data transformation requirements. Include the business rules for data cleansing as part of your ETL tool selection criteria. For example, some ETL tools cannot read flat files and cannot perform some of the very complicated transformations. If you need those capabilities, you must be aware of ETL tool limitations because you may need to license an additional data-cleansing tool to perform those processes, or augment the ETL tool functionality with custom-written code.

4. Evaluate each ETL product objectively and prepare a scorecard that compares the product features and their effectiveness. The reputation and responsiveness of a vendor are equally as important as the features of the products. Therefore, prepare another scorecard comparing the vendors.

5. Check the vendors ' client references by talking with people at organizations that already use the tools you are considering. This is the most cost-effective and informative way to evaluate the tools.

6. Narrow the list of ETL products and vendors to a short list of two or three candidates. Otherwise, you will waste too much time comparing all the products, and it may take "forever" to make the final decision. You might then select an inferior tool just to end the frustration and delay.

7. Arrange for product demos since "seeing is believing." Spend some time preparing the test cases so that all vendors on the short list get to demonstrate the performance and effectiveness of their products using the same test cases.

8. Test the vendor products even though it takes away time from your project schedule. Testing is the best way to discover any glitches that may happen before using a vendor product in production. Try to negotiate a 30-day trial period.

(The process of evaluating and selecting products and vendors is described in more detail in Step 10, Meta Data Repository Design.)