What are your views on the advantages and/or disadvantages ETL tools and data modeling versus code?

Question: My question is twofold, but related to the use of tools as opposed to code. We have a small business intelligence (BI) team, and the members are very technical and very good analysts and developers. They do not believe that an extract, transform and load (ETL) or a data modeling tool makes sense in our environment. Could you please provide your views on the advantages and/or disadvantages of the use of a tool for ETL and data modeling versus code to provide the same functional capabilities?

Tom Haughey’s Answer: This is really three entirely different questions, so I will address it as such.

1. Do you need an ETL tool or can you write your own ETL?

If an organization already has a large body of transformation programs written in COBOL or some other language, then I would stick with that strategy for now. If you have such a body of code, you probably have either procedures in place or a code generator that simplifies code creation. Many organizations successfully use what I call “roll your own” (RYO) code to do ETL. This can be a very effective solution in many situations. The advantages of ETL tools are:

Retention of metadata. This is a major point because analytical applications are highly dependent on proper understanding of metadata.

Ease of use. Because most ETL tools are GUI based and have repositories, they have increased ease of use and ease of modification.

Built-ins. They have built in objects to handle recurring tasks such as aggregation. So, these do not need to be coded and recoded.

Skill. Because of the above factors, the skill level requirements for ETL tools are less than with SQL.

Support. There is a large experience base to fall back on. This includes customer experiences and vendor support.

Auditing. ETL tools assist with auditing because of their repositories and their ability to preserve versions.

If you do not have an existing body of ETL code and other templates in place, I would recommend going to the ETL tool. However, in my opinion, as long as you handle metadata efficiently in your current environment, I would consider sticking with your current RYO solution. This point needs emphasis. Good metadata (in whatever form it takes) is essential to the usability of a data warehouse. As long as this is provided, I see no immediate benefit in going to an ETL tool. However, consider also your future plans and the growth plans for the DW. If you anticipate a large growth in BI applications and in data sources, then conversion to an ETL too would be a sound investment and should be added to your DW strategy.

2. Do you need a data modeling tool in general?

There is no question about it. If you intend to be a serious data management organization, or especially a world-class data management organization, there is no other way. I’m not saying which one, or even which type of one, or which features you need, but you absolutely need one or you cannot maturely deal with management of data and with database design. A smaller organization may be able to survive well with a simpler tool like Visio; a larger more mature organization needs a tool like PowerDesigner, ERwin, ERStudio, Visible or others.