Q:

I am currently managing the feasibility study phase of a data warehousing project. However I am currently having a tough time persuading the IT head of the benefits of using an ETL tool (such as Ascential or Informatica) over hand coded programs to perform the data extract, transformation, and load process. How should I go about this, particularly in terms of demonstrating real cost savings or other benefits?

A:

Sid Adelman’s Answer: The vendors have this information but be careful; they are, what you might say, on the optimistic side so do not let your management believe you will be getting all the productivity and quality benefits the vendors present. Talk to organizations using the ETL tools and get their impressions of the ETL tool benefits. These will be much closer to what you will actually get.

Les Barbusinski’s Answer: I feel your pain. It’s often difficult to sell the need for an ETL tool at the beginning of a data warehouse project. All management sees is the steep purchase price. Savings are tough to quantify, and they don’t usually accrue until the second or third release of the data warehouse. Nevertheless, the benefits are real. Here’s a partial list:

Productivity

  • Coding ETL scripts using a meta data-driven graphical tool with built-in data cleansing and transformation functions is generally faster than hand coding.
  • Mappings, extract rules, cleansing rules, transformation rules, aggregation logic and loading rules are generally handled as separate objects in an ETL tool. This means that you can change one object in an ETL "string" without affecting the other objects. For example, you can change the loading logic for a particular target table (say, from direct insert to generating a flat loader file) without affecting the cleansing and/or transformation logic for that table. This compartmentalization eases maintenance and reduces the need for retesting.
  • Objects in an ETL tool (e.g., transformation rules) can be reused.
  • ETL tools facilitate impact analysis when modifying or enhancing a data warehouse.
  • Many ETL tools provide "adapters" for extracting data from packaged ERP applications (such as SAP or PeopleSoft), as well as software packages for verticals (e.g., banking, manufacturing, distribution, etc.).
  • Some ETL tools have meta data repositories which can integrate with the meta data in your BI tool.

Methodology

  • ETL tools impose a certain level of structure, rigor, and consistency in your development approach. Unless you have strictly enforced standards and/or well-designed script templates and sub-routine library in your shop, hand coding your ETL scripts can result in a lot of hard- to-maintain spaghetti code.

Documentation

  • The meta data trapped by an ETL tool graphically documents source and target database structures, mappings (a.k.a. data genealogy), cleansing rules (a.k.a. business rules) and transformation rules. Such documentation is invaluable during impact analysis, or when bringing new team members up to speed on an ongoing DW project.

Hope this helps.

Michael Jennings’ Answer: Please refer to the June Enterprise Architecture View column, "What's in Your ETL Method?"

Chuck Kelley’s Answer: I think a big challenge that you will have is exactly this issue. How do you convince someone to purchase a product for $75,000+ when they are not sure exactly what they will get from it? It may be that for the first round of your data warehouse, you choose hand coded programs over an ETL tool. They will help the head of IT to get over the sticker shock of using a product. During the next round, you will be able to easily document where the gains in using a tool over hand coding can be found – namely, in documentation, understandability of the code and meta data.

Clay Rehm’s Answer: The head of IT is probably getting sticker shock. Do your homework and identify all of the vendors who can provide this tool. There are other vendors out there such as Data Junction, who can provide these capabilities at less of a cost.

Also, don’t limit your search to ETL tools. Look at EAI tools and e-business data integration tools as well.

To manage expectations, do you really think you can convince the head of IT that an ETL tool will provide cost savings? An ETL tool still requires a developer to understand data sources, data targets and transformations, just as you would using any other programming language. Find out the true benefits of an ETL tool such as the graphical interface, performance and meta data and sell those points.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access