ETL tools: What you do and don't want

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Extract transform load tools Data integration for Software as a Service business intelligence software evolves Efficiencies can be gained at the intersection of middleware and data integration Interest in open source data integration technologies growing Informatica offers data integration software by the hour on Amazon EC2 Open source data integration tools good choice for 'standard' ETL jobs Operational BI gets a boost from real-time data loading Data integration costs hurting companies with poor negotiation skills Wholesaler turns to data integration adapters for mainframe to Oracle migration Pervasive the most persuasive, cost-effective data integration platform, study says Integration competency centers centralize data integration projects Integration and SOA data services What are some SOA risks and challenges and how can we mitigate risks? How to estimate SOA costs and design an SOA strategy for integration projects What's the difference between SOA and Web services? What are the components of service-oriented architecture (SOA)? SOA governance best practices Data integration certifications: Finding the value The ETL process and MySQL ERP reporting tools' advantages and disadvantages ETL tools and EDR tools: What's the difference? Data-as-a-service, explained and defined

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

Ahh. ETL tools. Can't you just do it all with COBOL? (Insert laugh track here.)

Seriously, there are some core functions that most ETL tool vendors offer today. These include:

• The ability to deal with multiple input data formats (such as flat files, SQL databases, SAS data sets, etc.)
• Support of a GUI interface that allows the "drag and drop" of data elements from source to destination
• Built-in data profiling and analysis functionality allowing the examination of source data
• Built-in data transformation functions (such as data type conversion, date reformatting, etc.)
• Support for data quality/cleansing functionality (either built-in, or via interfaces to popular data quality tools like Group 1, DataFlux, or Business Objects)
• Metadata support, allowing for the capture and identification of data transformation and business rules
• Job scheduling functionality
• Version control of the ETL maps
• The ability to manipulate data either inside or outside the target database (aka: ETL versus ELT)

What you should avoid

My ETL guys suggest staying away from code-generating ETL tools, which are difficult to troubleshoot and de-bug. They also recommend steering clear of ETL tools that don't provide error tracking and logging functionality.

And a final thought: ETL isn't just for breakfast anymore. What I mean by this is that ETL isn't exclusive to data warehouse projects, but can solve a host of data movement and transformation problems. Best of luck with your tool selection!

For more information on ETL tools

• Read ETL tools transforming, study finds
• Read Data integration software vs. hand coding: Balancing costs and benefits
• Read ETL and CDI, the fundamental differences