QuickStudy: Extract, Transform and Load (ETL)

Marc L. Songini   Today’s Top Stories   or  Other Data Warehousing Stories  

Driving BI Value with Data Integration: Overcoming the Thorniest Challenges The Secure Web Gateway. Mission Critical For Business Get a Grip on Storage Growth and Reduce Costs Survey: Business Intelligence Maturity and the Quest for Better Performance BI @ The Speed of Business Go Green with Webroot® Perimeter Security SaaS! Six Questions To Ask About Information And Competition Computerworld Report- Large Enterprises Pay More for IPAM An Apple for Your Enterprise? Sign up to receive Data Warehousing Resource Alerts

February 2, 2004 (Computerworld) -- ETL stands for extract, transform and load, the processes that enable companies to move data from multiple sources, reformat and cleanse it, and load it into another database, a data mart or a data warehouse for analysis, or on another operational system to support a business process.

The only problem is that the data lies in all sorts of heterogeneous systems, and therefore in all sorts of formats. For instance, a CRM system may define a customer in one way, while a back-end accounting system may define the same customer differently.

The data used in ETL processes can come from any source: a mainframe application, an ERP application, a CRM tool, a flat file, an Excel spreadsheet—even a message queue.

Pulling the Data

Extraction can be done via Java Database Connectivity, Microsoft Corp.'s Open Database Connectivity technology, proprietary code or by creating flat files, says Mike Schiff, an analyst at Current Analysis Inc., a Sterling, Va.-based consultancy.

After extraction, the data is transformed, or modified, depending on the specific business logic involved so that it can be sent to the target repository.

There are a variety of ways to perform the transformation, and the work involved varies. The data may require reformatting only, but most ETL operations also involve cleansing the data to remove duplicates and enforce consistency. Part of what the software does is examine individual data fields and apply rules to consistently convert the contents to the form required by the target repository or application, says Schiff.

For example, the category "male" might be represented in three different systems as M, male and 0/1. The ETL software would recognize that these entries mean the same thing and convert them to the target format.

In addition, the ETL process could involve standardizing name and address fields, verifying telephone numbers or expanding records with additional fields containing demographic information or data from other systems.

Harriet Fryman, group director of product marketing at data warehousing vendor Informatica Corp. in Redwood City, Calif., offers an example. Say, for instance, that a customer runs Oracle financials, PeopleSoft human resources software and SAP manufacturing applications and needs to access the data in each of these systems to complete an order-to-cash process. This will require the company's ETL software to extract data from the originating systems, which isn't as easy as it sounds in some instances—for example, pulling data from the SAP manufacturing application would require the generation of SAP proprietary ABAP code to extract the shipping and open purchase-order information.

The transformation occurs when the data from each source is mapped, cleansed and reconciled so it all can be tied together, with receivables tied to invoices and so on.

Continued...
1 | 2 | NEXT  

Print this Story

Send Us Feedback

E-mail this Story

Digg this Story

Slashdot this Story

QuickStudy: ETL

"Yahoo's owners have spoken and Jerry Yang is out as Yahoo CEO. Does this mean that Microsoft is in?..." Read more... "In five years, IT will still be a viable career path, but it will no longer exist as the stand-alone..." Read more... Read more Business Intelligence posts or See all Blogs

Microsoft's Ballmer ordered to testify in 'Vista Capable' suit Spyware case finally closed for teacher Julie Amero Bush's exit to put new e-records system to the test More top stories...

Judgment favors Novell in ongoing SCO case Symantec sees spike in dangerous Microsoft attacks BlackBerry Storm buyers brave the cold in Boston to be first with the new smart phone

2008 Salary Survey: IT pay takes tiny leaps If you're like our 7,000 survey respondents, your paycheck this year has been flattened and your bonus obliterated. We offer 12 ways to plump up your paycheck. Windows 7 in-depth review and video: This time Microsoft gets it right Microsoft's next OS might more accurately be called Windows 6.5: It's essentially a better version of Vista. Twitter for business: 5 ways to tap the power of the tweet Twitter can be a valuable business tool -- if you know what you're doing. Here's how to juice it for all it's worth. Microsoft's 'Vista Capable' changes outraged HP, insider e-mails show By helping Intel with loosened 'Vista Capable' requirements, Microsoft 'severely damaged' its credibility, said an HP exec in a newly unsealed Feb. 2006 e-mail. Windows 7 Get the latest news, reviews and more about Microsoft's newest desktop operating system More Continuing Coverage Windows 7 Voting Technology Google's Chrome browser Economy in turmoil: Latest news and tips iPhone 3G Bill Gates moves on Windows Vista A to Z Mac A to Z 2008 Salary Survey Find wage data for 50 IT job titles. More Special Reports 2008 Premier 100 IT Leaders 2007 Premier 100 IT Leaders 2008 Best Places to Work in IT 2007 Best Places to Work in IT 2008 Salary Survey 2007 Salary Survey 2006 Salary Survey 2008 IT Forecast 2007 IT Forecast 40 Years of Computerworld Top 12 Green-IT Users The FAQs About Going Green IT Schools to Watch iPhone: One Year Later Global Mobile Your Next Data Center Management: Reinventing IT Stop Data Leaks Web 2.0 Security Surviving Disasters Managing Storage Virtualization The Lean Storage Machine Storage on Trial Can Web 2.0 Save BI? Bypass These BI Potholes All Zones Business Continuity Zone The File Data Management Zone Security Management Zone The SAS Zone Business Intelligence and Analytics Zone The Enterprise Search Zone Software as a Service Zone The Security Zone See your link here

The Business Value of Tape Storage Download this complimentary Technology Briefing today! (Source: Dell) Reliable. Long-lived. Portable. Affordable. Energy-stingy. These and many other attributes of tape storage are virtually assuring a continued market for tape storage, a conclusion reached by IDC and many others. Combined with other storage solutions, tape is an integral part of any data archiving strategy. Download this executive briefing  Quick Sizing Guide for SAS Grid Running on HP BladeSystems and EVA Storage Download this white paper today! (Source: HP) Designed for CIOs, IT managers, data center managers and grid computing architects seeking to improve performance, SAS Grid Computing on the HP BladeSystem c-Class helps accelerate growth and mitigate risks with a simplified, consolidated infrastructure that's agile enough to efficiently handle change. SAS Grid Manager on HP BladeSystem can lower costs through automation, virtualization and improved IT efficiency. Download this white paper  Turning information into a Competitive Advantage Turning information into a Competitive Advantage View this webcast now! Go to the webcast  White Papers Read up on the latest ideas and technologies from companies that sell hardware, software and services. Infoblox IP Address Management Solutions Brief 2008 Internet Malware Trends Report Enterprise Findability Without the Complexity View more whitepapers

• QuickStudy: ETL

• Microsoft's Ballmer ordered to testify in 'Vista Capable' suit
• Judgment favors Novell in ongoing SCO case
• Spyware case finally closed for teacher Julie Amero
• More top stories 

SAS Business and Analytics Zone No matter where your organization is on the path toward evolving your IT infrastructure, SAS can adapt to your situation to meet your long-term enterprise intelligence needs. We can help you drive intelligence evolution to the next level, while leveraging and extending the value of your existing IT investments. Learn more in the Business and Analytics Zone See All Zones

"Turning information into a Competitive Advantage" Companies today are realizing that competitive advantage is harder to sustain when based solely on gains in productivity and cost efficiency. The focus is shifting to invest more in business optimization initiatives which rely on trusted information to develop new insights that deliver better business results. But how can this be done efficiently in a business environment across multiple applications and processes. The answer is an Information Agenda - an innovative approach to transforming business information into a strategic asset for competitive advantage. View this webcast now!  See more Webcasts