WIXLIB

eTL Integrator User’s GuideRelease 5.0.1SeeBeyond Proprietary and Confidential

The information contained in this document is subject to change and is updated periodically to reflect changes to the applicablesoftware. Although every effort has been made to ensure the accuracy of this document, SeeBeyond Technology Corporation(SeeBeyond) assumes no responsibility for any errors that may appear herein. The software described in this document is furnishedunder a License Agreement and may be used or copied only in accordance with the terms of such License Agreement. Printing,copying, or reproducing this document in any fashion is prohibited except in accordance with the License Agreement. The contentsof this document are designated as being confidential and proprietary; are considered to be trade secrets of SeeBeyond; and may beused only in accordance with the License Agreement, as protected and enforceable by law. SeeBeyond assumes no responsibility forthe use or reliability of its software on platforms that are not supported by SeeBeyond.SeeBeyond, e*Gate, and eWay are the registered trademarks of SeeBeyond Technology Corporation in the United States and selectforeign countries; the SeeBeyond logo, e*Insight, and e*Xchange are trademarks of SeeBeyond Technology Corporation. The absenceof a trademark from this list does not constitute a waiver of SeeBeyond Technology Corporation's intellectual property rightsconcerning that trademark. This document may contain references to other company, brand, and product names. These company,brand, and product names are used herein for identification purposes only and may be the trademarks of their respective owners. 2003 by SeeBeyond Technology Corporation. All Rights Reserved. This work is protected as an unpublished work under thecopyright laws.This work is confidential and proprietary information of SeeBeyond and must be maintained in strict confidence.Version 20031119161647.eTL Integrator User’s Guide2SeeBeyond Proprietary and Confidential

ContentsContentsChapter 1System Description5Introduction5The eTL Integrator Product DescriptionThe ETL Process56eTL Supporting Features7Supporting Documents9Writing Conventions10Installing eTL11Installing eTL on an eGate Supported System11The SeeBeyond Web Site11Chapter 2Interface to the eGate Enterprise Designer12Enterprise Designer Components12Menu Bar13Enterprise Explorer14Project Editor14Creating Database OTDsImporting Metadata Information for flat files1416Chapter 3Creating a Simple Project24Sample Scenario “Tutorial”24Starting the Enterprise Designer25Create and Name a ProjectCreate a New Object Type DefinitionSelect Database ObjectsUse Enterprise Designer to configure eTL CollaborationsOptional Method for Selecting TableseTL Integrator User’s Guide32526283335SeeBeyond Proprietary and Confidential

ContentsMapping Tables36Map Tables and Apply Business Rules36Using Runtime Filters42Configuring Inserts and UpdatesInput and Output Runtime ArgumentsConditional Extractions424345Validating and Testing48Validate CollaborationExecute a Test Run4849Using eTL With eInsight49Chapter 4Deployment54Creating a Deployment Profile54New Deployment ProfileDeploy your ProjectRun the BootstrapVerify the Output Data54545555Deployment Profile for eTL56Scenario 1Scenario 25661Glossary65Index73eTL Integrator User’s Guide4SeeBeyond Proprietary and Confidential

Chapter 1System DescriptionSection 1.1IntroductionChapter 1System DescriptionSeeBeyond’s eTL Integrator technology is optimized for very large record sets andbuild data scenarios that are fully integrated with the SeeBeyond ICAN suite(Integrated Composite Application Network Suite) to unify the domains of eAI(eBusiness and Application Integration) and ETL. An eTL Collaboration can beintegrated into the enterprise business process or used as a standalone ETL process.1.1IntroductionExtraction Transform and Load (ETL) is a data integration technology that extracts datafrom several heterogeneous data sources, transforms the data, then loads the data in auniform format into a target data source.Figure 1 eTL and the ICAN Product Suite1.1.1.The eTL Integrator Product DescriptionSeeBeyond’s eTL Integrator technology is optimized for very large record sets andbuild data scenarios that are fully integrated with the SeeBeyond ICAN suiteeTL Integrator User’s Guide5SeeBeyond Proprietary and Confidential

Chapter 1System DescriptionSection 1.1Introduction(Integrated Composite Application Network Suite) to unify the domains of eAI(eBusiness and Application Integration), and Enterprise Information Integration (EII).With these unified domains you can build unprecedented solutions using both messagebased processing (eGate) and dataset based processing (eTL) technologies.The eTL Integrator product provides excellent performance at runtime for high volumeextraction and load of tabular data sets. The eTL Integrator can be integrated into theenterprise business processes or used as a standalone product.The eTL Integrator product can be used to acquire a temporary subset of data forreports or other purposes, or acquire a more permanent data set for the population of adata mart or data warehouse. The product may also be used for conversion of onedatabase type to another or for the migration of data from one database or platform toanother.1.1.2.The ETL ProcessIn an ETL process, data is extracted from data sources. The data is then transformed (orprocessed), using rules, algorithms, concatenations, or filters, into a desired statesuitable for loading into a database or data warehouse. See the following Figure 2.In managing databases, extract, transform, load (ETL) refers to three separate functionscombined into a single programming tool.1 First, the extract function reads data from a specified source database and extracts adesired subset of data.2 Next, the transform function works with the acquired data – using rules or lookuptables, or creating combinations with other data – to convert it to the desired state.3 Finally, the load function is used to write the resulting data to a target database,which may or may not have previously existed.eTL Integrator User’s Guide6SeeBeyond Proprietary and Confidential

Chapter 1System DescriptionSection 1.2eTL Supporting FeaturesFigure 2 The ETL ProcessWarehouseTransformExtractExtract data froma source1.2Process through a seriesof transformationsLoadLoad data into atarget/warehouseeTL Supporting FeatureseTL Integrator is compatible with the following systems and platforms:! Oracle, SQL Server versions 2000, 8.1.7 or newer, and 9i, and tabular formatted flatfiles! Multiple sources and multiple destinations! Standard eGate platform supportBuilt in Integration CapabilityeTL Integrator enables seamless filtering and data transformation.! Merge/upsert (updates or inserts as appropriate)! Drag and drop GUI design features (create joins across disparate data sources)! Validate Collaborations before performing the ETL processes (ICAN Suite providesversioning and history)Design ToolsUser friendly, state-of-the-art, design tools reduce development time and cost.eTL Integrator User’s Guide7SeeBeyond Proprietary and Confidential

Chapter 1System DescriptionSection 1.2eTL Supporting FeaturesThe eTL Integrator Collaboration editor has two key characteristics that maximizeproductivity and ease of use:1 GUI based Collaboration editor employs drag and drop design features" User friendly Wizards (easy OTD creation)" Graphical operators (dragged from a toolbar)" Graphical tools (create underlying SQL)2 Tight integration among ICAN Suite business data systems" Web Services interface" Seamless integration with the ICAN SuiteDevelopment ToolsDevelopment is simplified with GUI based development tools that are appropriate forSQL Collaborations. Graphical drag and drop modeling tools enable SQL operations invarious categories:! Number! Comparison! Boolean! SQL Specific! StringTransformation CapabilityeTL Integrator provides all of the common operations in the following areas:! SQL operators! Mathematical operators! String manipulations! Source date format must match the target date format (Date format conversions -later release)! Conditional data transformationsArchitectureRobust business application integration throughout the ICAN Suite makes eTL a moreversatile and powerful tool.! A deployed eTL engine runs as a JCA compliant (J2EE) resource adapter inside theSeeBeyond Integration server.! The business rules defined by the eTL Collaboration definition are stored in theSeeBeyond Repository.! At deployment time, the business rules are used to generate the appropriateplatform specific SQL.! eTL Integrator leverages OTDs defined in the Enterprise Designer so you don’thave to create OTDs specifically for an eTL Collaboration.eTL Integrator User’s Guide8SeeBeyond Proprietary and Confidential

Chapter 1System DescriptionSection 1.3Supporting DocumentsKey Operations and FunctionalityAn extensive array of operators, filtering, and data manipulation tools offer unlimiteddata design capability.! Join" Auto-detect primary key relationships between tables, as indicated in OTDs" Between tables from disparate data sources that have no relationship" Supports inner, left, right, and full outer joins! Lookups" Extensive list of operators allows you to create lookups as part of the eTLprocess, using joins across tables! Merge" Automatic update if row exists" Automatic insert if row doesn’t exist! Test data and test runs! Runtime variables (configured by the user)1.3Supporting DocumentsThe following SeeBeyond documents provide additional information about eGateIntegrator:! eGate Integrator Installation Guide! eGate Integrator Release Notes! eGate Integrator User’s Guide! Message Server Reference Guide! eGate Tutorial! SeeBeyond ICAN Suite Deployment Guide! SeeBeyond ICAN Suite PrimerSee the SeeBeyond ICAN Suite Primer for a complete list of eGate Integratordocumentation. You can also refer to the appropriate Windows or UNIX documents, ifnecessary.eTL Integrator User’s Guide9SeeBeyond Proprietary and Confidential

Chapter 1System Description1.4Section 1.4Writing ConventionsWriting ConventionsThe following writing conventions are observed throughout this document.Table 1 Writing ConventionsTextConventionExampleButton, file, icon, parameter,variable, method, menu, andobject names.Bold text!!!!!Command line argumentsand code samplesFixed font. Variablesare shown in bolditalic.bootstrap -p passwordHypertext linksBlue textClick OK to save and close.From the File menu, select Exit.Select the logicalhost.exe file.Enter the timeout value.Use the getClassName()method.! Configure the Inbound FileeWay.For more information, see“Writing Conventions” onpage 10.Additional ConventionsWindows SystemsFor the purposes of this guide, references to “Windows” will apply to MicrosoftWindows Server 2003, Windows XP, and Windows 2000.Path Name SeparatorThis guide uses the backslash (“\“) as the separator within path names. If you areworking on a UNIX system, please make the appropriate substitutions.eTL Integrator User’s Guide10SeeBeyond Proprietary and Confidential