Transcription
eTL Integrator User’s GuideRelease 5.0.3SeeBeyond 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 e*Way 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 20040303103909.eTL Integrator User’s Guide2SeeBeyond Proprietary and Confidential
ContentsContentsChapter 1System Description6Introduction6The eTL Integrator Product DescriptionThe ETL Process67eTL Supporting Features8Supporting Documents10Writing Conventions11Additional Conventions11Installing eTL12Installing eTL on an eGate Supported System12The SeeBeyond Web Site12Chapter 2Graphical Interface Tools13Toolbar Icons for eTL Collaborations13Using Operators15Comparison Toolbar Icons—Operators16Boolean Toolbar Icons—Operators19Number Toolbar Icons—Operators20SQL Toolbar Icons—Operators23String Toolbar Icons—Operators25Chapter 3Interface to the eGate Enterprise Designer27Enterprise Designer Components27Menu Bar28Enterprise Explorer29eTL Integrator User’s Guide3SeeBeyond Proprietary and Confidential
ContentsProject Editor29Creating Database OTDsImporting Metadata Information for Flat files2931Chapter 4Extraction Filters and Implementation ConditionsUsing Runtime Filters4040Configuring Inserts and UpdatesInput and Output Runtime ArgumentsConditional ExtractionsOptional Method for Selecting Tables40414346Using Operators - Parenthesis47Chapter 5Creating a Sample Project Using FlatfilesSample Scenario Data4949Create and Name a ProjectCreate a New Object Type DefinitionCreate a Collaboration Definition515156Mapping Tables58Map Tables and Add a Join ConditionApply Business LogicValidating and Testing586263Connectivity Map64Create a Connectivity Map64Deployment Profile for eTL66Run your Project67Run the Bootstrap and Management Agent68Run the Bootstrap68Verify the Output Data69Chapter 6Creating a Sample Project70Sample Scenario Using Oracle70Starting the Enterprise Designer71Create and Name a ProjectCreate a New Object Type DefinitionSelect Database ObjectsUse Enterprise Designer to configure eTL CollaborationsOptional Method for Selecting TableseTL Integrator User’s Guide47172747982SeeBeyond Proprietary and Confidential
ContentsMapping Tables Using Join Views84Join Two TablesJoin More Than Two Tables8489Validating and Testing92Validate CollaborationExecute a Test Run9393Appendix AUsing eTL With eInsight94Using eTL With eInsight94Glossary99e*Gate 4.x Terms in eGate 5.0105IndexeTL Integrator User’s Guide1075SeeBeyond 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 Guide6SeeBeyond 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 Guide7SeeBeyond 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 8.1.7 and 9i (9.0.2), SQL Server 2000, DB2 UDB 8.1, 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 Guide8SeeBeyond 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 Guide9SeeBeyond 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:! SeeBeyond ICAN Suite Installation Guide! eGate Integrator Release Notes! eGate Integrator User’s Guide! Message Server Reference Guide! eGate Integrator 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 Guide10SeeBeyond 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 11.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 Guide11SeeBeyond Proprietary and Confidential
Chapter 1System Description1.5Section 1.5Installing eTLInstalling eTLDuring the eGate Integrator installation process, the Enterprise Manager, a web-basedapplication, is used to select and upload products from the eGate installation CD-ROMto the Repository.When the Repository is running on a UNIX operating system, eGate and eTL areinstalled using the Enterprise Manager from a computer running Windows, connectedto the Repository server.Refer to the SeeBeyond ICAN Suite Installation Guide.1.5.1.Installing eTL on an eGate Supported SystemeTL is installed during the installation of the eGate Integrator. The eGate installationprocess includes the following operations:! Install the eGate Repository! Upload products to the Repository! Download components (such as the SeeBeyond Enterprise Designer and LogicalHost)Follow the instructions for installing the eGate Integrator in the SeeBeyond ICAN SuiteInstallation Guide, and include the following steps:1 During the procedures for uploading files to the eGate Repository using theEnterprise Manager, after uploading the eGate.sar file, select and upload thefollowing files:" eTL.sar" eTLDocs.sar (to download the eTL Integrator User’s Guide)2 Continue installing the eGate Integrator as instructed in the SeeBeyond ICAN SuiteInstallation Guide1.6The SeeBeyond Web SiteThe SeeBeyond Web site is your best source for up-to-the-minute product news andtechnical support information. The site’s URL is:http://www.seebeyond.comeTL Integrator User’s Guide12SeeBeyond Proprietary and Confidential
Chapter 2Graphical Interface ToolsSection 2.1Toolbar Icons for eTL CollaborationsChapter 2Graphical Interface ToolsThis chapter introduces the graphical interface tools (GUIs) that are available on themenus and by right-clicking on certain icons. By clicking on icons you can place userfriendly operators within (method boxes), tables, functions, and other GUIs on yourwork space (designer window or canvas). By entering va
! eGate Integrator Tutorial! SeeBeyond ICAN Suite Deployment Guide! SeeBeyond ICAN Suite Primer See the SeeBeyond ICAN Suite Primer for a complete list of eGate Integrator documentation. You can also refer to the appropriate Windows or UNIX documents, if necessary. Chapter 1 Section 1.4 System Description Writing Conventions eTL Integrator User’s Guide 11 SeeBeyond Proprietary and .
