Data Doghouse - performance management, business intelligence, and data warehousing

ETL tools: Don’t Forget About the Little Dogs

Rick Sherman — Tue, 17 Nov 2009 11:41:09 -0500

I’m not only concerned about hand-coding versus ETL tools; I’m also concerned that potential buyers of ETL tools and the market in general are only looking at a small number of players in the ETL marketplace.

For many years industry analyst research groups have identified the top two product vendors: Informatica and IBM (from its acquisition of Ascential Software). So, naturally, these two appear on any evaluation shortlist. The rest of the evaluation shortlist usually includes the bundled products (mentioned in my recent posts) that come with the databases, BI tools or applications that the evaluating company already owns. Beyond these usual suspects, other ETL or data integration products are pretty obscure and almost invisible, at least from a general market perspective.

There are many ETL and integration products in the marketplace that lie somewhere between the mega-products and the bundled tools in terms of functionality and total cost of ownership (TCO). The problem for the vendors that offer these products is that very few people know about them -- other than data integration nerds like me and the vendors’ relatives!

To make matters even worse, industry analysts also seem to ignore these vendors because of their company size and relatively small installed base. I am not arguing with the industry analyst firms’ objective criteria to include/exclude vendors, but it definitely works against the “unknown” vendors.

Also, to add insult to injury, industry pundits generally speak and write about the high tech titans and ignore the smaller vendors. If everyone only talks about the well-known vendors then the smaller vendors will remain unknown.

Conventional wisdom says...

If you need it and can afford it a data integration project will be successful if you buy the “best-in-class” tools. The old IT industry adage was you can’t get fired for buying IBM. Let’s expand that in the current world of ETL and say you can’t get fired if you buy IBM or Informatica (although you can if you don’t complete the project on time and on budget...and in this economy no one’s job is completely safe.)

On the other end of the spectrum, where budgets and data integration needs are limited, the bundled tools may indeed do the job. My qualifier, as I mentioned in my previous post, is that if you skip learning the what and how of data integration processing then you could easily fail at using these tools.

Change the paradigm...

Completing the data integration project tasks is fine, but what about TCO, skills needed and time to market? The answer is that there are other ETL products in the marketplace that compete with the Titans and bundled products. Broaden your evaluation shortlist beyond the usual suspects. Maybe you will find a better fit for your data integration tasks than the “best-in-class” or the cheapest option. Or maybe you’ll still choose the usual suspects. But either way, at least you will have taken a good look at the possibilities.

Final thoughts

Advice to ETL evaluators: broaden your search and you might be pleasantly surprised.

Advice to ETL vendors: get visible. First, get recognized. Second, differentiate yourself. Third, your message needs to be understandable to people who do not know ETL.

Web Seminar - The Key to Operational BI: Keep It Simple

Rick Sherman — Tue, 10 Nov 2009 18:29:59 -0500

Registration is now open for my upcoming free webinar: The Key to Operational BI: Keep It Simple.

Date: November 17, 2009
Time: 12PM ET/ 9AM PT

REGISTER TODAY

Delivering insights to front-line workers can significantly extend the value of your information management investments. What's more, operational BI can offer more immediate, tangible return than traditional, analyst-focused BI. The key is to keep it simple: operational workers often don't have much time for training or learning complex analysis tools. Instead, give them simple applications that deliver targeted advice.

Register for this Espresso Shot Web Seminar to hear Jake Freivald and me discuss the virtues of Operational BI, and outline the practical steps necessary to get there quickly.

Attendees will learn:

How to combine traditional and operational BI to align strategy with operations
Which delivery mechanisms work best for operational BI
How the information infrastructure should be handled
How Operational BI can improve enterprise efficiency

Speakers:

Rick Sherman, Athena IT Solutions
Jake Freivald, Information Builders

Data Integration: Hand-coding Using ETL Tools Part 2

Rick Sherman — Fri, 06 Nov 2009 07:23:21 -0500

This is a continuation of an earlier post that discussed the problems of hand-coding using ETL tools.

What Went Wrong?

There are two aspects of effectively leveraging an ETL tool. First is learning the tool’s mechanics. e.g. taking the tool vendors’ training either in a class or through their on-line tutorials. Most IT people have no problem learning a tool’s syntax. Since they most likely already know SQL, they learn the tool very quickly.

But the second aspect actually involves understanding ETL processes. This includes knowing the data-integration processes needed to gather, conform, cleanse and transform; understanding not only what is dimensional modeling but why and how do you deploy it; being able to implement slowly changing dimensions (SCD) and change data capture (CDC); understanding the data demands of business intelligence; and being able to implement error handling and conditional processing.

Without understanding the why of ETL processing, IT developers either quickly become disillusioned with ETL tools or simply under utilize them. Typically these ETL implementations merely result in the ETL tools executing SQL scripts or stored procedures, e.g. hand-coding.

These hand-coded processes within ETL tools are big trouble-makers. First, the tools have built-in transforms such as SCD and CDC which, if you don't use, make you re-invent the wheel (code something you already bought). In doing so, you're likely doing something inefficient at best and outright wrong at worst.

Second, ETL tools are built to be more efficient at extracting, transforming and loading data than SQL coders.

Third, the IT staff is not likely to code extensive error handling or audit routines that are pre-built in the ETL tools. This lessens productivity and responsiveness to issues in data quality.

Fourth, hand-coded processes are often not documented or, if they are initially, they're not likely to be maintained.

Finally, each hand-coded operation is a custom job that each new developer has to learn, versus being able to bring in a developer who knows an ETL tool.

How to Avoid Repeating History’s Mistakes

You don’t know what you don’t know. It’s not that the IT staff wants to use these ETL tools either incorrectly or inappropriately, but they don’t know any better.

I’ll keep preaching that data-integration processes should be developed using ETL tools rather than hand coding. But what I have learned along the way is I also need to advocate that anyone using these tools learn not just about the tool but more importantly about ETL processing.

Fyi: a good starting place is my articles on ETL. Check out my corporate library pointing to my articles, posts, webinars, podcasts and white papers.

Data Integration: Hand-coding Using ETL Tools

Rick Sherman — Wed, 04 Nov 2009 13:44:18 -0500

We are creatures of habit. It’s not easy to stop doing something the way we’ve always done it. Especially when we think we are right (but actually we’re not). Let’s explain.

I have discussed (some might say preached) in many posts, articles, webinars, podcasts, classes and client discussions that for any recurring data integration tasks IT should use an Extract, Transform and Load (ETL) tool.

This certainly has been the best practice for enterprise data warehousing projects in the Fortune 1000. This is where I got my early experience in data integration and got to use the ETL tools that annually rank in Gartner’s Upper Magic Quadrant and Forrester’s Top Wave. These ETL tools enabled IT groups and SI (system integrator) project teams to tackle data integration challenges too complex and extensive for hand-coding.

However, while the enterprise data warehousing projects were being developed with enterprise class ETL tools, most Fortune 1000 departmental projects and small to medium business (SMB) companies were hand-coding their data-integration processes.

IT groups choose to hand-code because for quite a while the enterprise class ETL tools they heard of were too expensive for their budgets. In addition, these tools required dedicated, trained developers. Life in a small IT group means doing multiple tasks and never really having time to get training. The result is millions of lines of hand-coded ETL in enterprises today -- most of which is not documented and took much longer to develop than it would have with an ETL tool.

Times are Changing

But times have changed. There are now very robust, affordable ETL tools that can easily handle any departmental or SMB data integration projects (and might even be able to handle some enterprise data warehouses too).

In fact, two classes of ETL tools are free (or almost so). First, database vendors have “bundled” ETL tools with their databases. Although initially these tools were very simplistic they have expanded over the years have quite robust functionality. Second, open source software (OSS) ETL tools have also emerged and are capable of handling many departmental and SMB needs.

The emergence of these tools has broken through the pricing barrier with ETL tools that are not only quite capable, but not difficult for a small IT staff to learn. I have been advocating these tools in the departmental and SMB data integration market for years. The good news is these tools are picking up converts but the bad news is IT may not be using these tools as well as they might.

Stay tuned for my next post, where I continue this discussion with Hand-Coding: What Went Wrong and How to Avoid Repeating History’s Mistakes

Discount on my Online DW and BI Data Modeling Course

Rick Sherman — Mon, 02 Nov 2009 13:40:46 -0500

My new DW and BI Data Modeling course is now available on ELearningCurve. This is a great bargain compared to traveling to a conference. From now until November 6 you can get a 20% discount by using the coupon code DM0420.

A well designed data model is the cornerstone to building business intelligence and data warehouse applications that provide significant business value. My DW and BI Data Modeling course will help you:

understand what Logical Data Modeling is, its goals, and its components
know the basics of Entity-Relationship (ER) and dimensional modeling
understand how to benefits of and applicability of Dimensional Data Modeling
be able to create a Dimensional Data Models for BI & DW applications
be positioned to learn more about Data Modeling

Click for a preview of the introduction and to learn more.