fme extension for in for ma tic a tutorial 2008

8/9/2019 FME Extension for in for Ma Tic A Tutorial 2008

1/36

FME Extension for I nformatica Tutorial


2/36

Safe Software Inc. makes no warranty either expressed or implied, including, but not limited to, any implied warranties of merchantability or fitness for a particular purpose regarding these materials, and makes such materials available solely onan as-is basis.

In no event shall Safe Software Inc. be liable to anyone for special, collateral, incidental, or consequential damages inconnection with or arising out of purchase or use of these materials. The sole and exclusive liability of Safe Software Inc.,

regardless of the form or action, shall not exceed the purchase price of the materials described herein. This manualdescribes the functionality and use of the software at the time of publication. The software described herein, and thedescriptions themselves, are subject to change without notice.

Copyright 2008 Safe Software Inc. All rights are reserved.

Revisions

Every effort has been made to ensure the accuracy of this document. Safe Software Inc. regrets any errors and omissions

that may occur and would appreciate being informed of any errors found. Safe Software Inc. will correct any such errors andomissions in a subsequent version, as feasible. Please contact us at:Safe Software Inc.Fax: [email protected]

Safe Software Inc. assumes no responsibility for any errors in this document or their consequences, and reserves the rightto make improvements and changes to this document without notice.

Trademarks

FME is a registered trademark of Safe Software Inc. Informatica and PowerCenter are registered trademarks of InformaticaCorporation. All brand or product names are trademarks or registered trademarks of their respective companies ororganizations.

Document Name: FME Extension for Informatica TutorialVersion: FME 2008

March 2008


3/36


4/36


5/36


6/36

S a f e S o f t w a r e I n c . F M E I n f o r m a t i c a T u t o r i a l

Contents vi

FM E W or kb en ch ................................................................................................................................................. 26

Opening FME Workbench ..............................................................................................................................26

Importing an FME Source And FME Destination ................................................................................................26 Importing an Informatica Source and Destination ............................................................................................27

Export Informatica Mapping ..................................................................................................................27 Add Informatica Source........................................................................................................................28 Add Informatica Destination..................................................................................................................29

Tips an d Trou bl esho ot ing .................................................................................................................................. 30


7/36


Getting Started

The FME Extension for Informatica extends Informatica PowerCenter with spatial data integration capabilities.

This document contains all the information you need tostart evaluating the FME Extension for Informatica. Thecommon tasks that a PowerCenter user would perform

using the FME Extension are described and samplemappings, workspaces and data have been provided.Steps on how to create various FME workspaces withInformatica and FME sources and destinations aredescribed in the appendices. For detailed instruction inFME please contact Safe Software about the Introductionto FME course .

Install FME Extension

Before you get started, you will need the followingsoftware installed and licensed on the evaluationcomputer:

Informatica PowerCenter 7.x, or 8.x FME 2007 Build 4102 or higher

1

Please refer to the Informatica PowerCenter installationdocumentation for instructions on installing and licensingPowerCenter. For information on installing and licensingFME, please refer to the FME Tutorial which can bedownloaded at http://www.safe.com/downloads .

License FME Extension

An FME Extension for Informatica license is required. Oncelicensed, the FME Extension for Informatica will be anoption available from the FME Administrator.

Configure FM E Extension

In formatica 7.x

Extend with FME Administrator

1.

Open the FME Administrator from Start > All Programs> FME > FME Adminstrator. Extend the FMEExtension for Informatica PowerCenter Server byselecting it in the Applications list and clicking on theExtend button. Do the same for the FME Extension forInformatica PowerMart Client. The Current State foreach Application should read Extended.

Getting Started
http://www.safe.com/downloadshttp://www.safe.com/downloads


8/36


2. Click Close to dismiss the FME Administrator.

Register FME Extension in Repo sitory Server

2

3. Open the Informatica Repository ServerAdministration Console. Expand the InformaticaRepository Servers node and select the RepositoryServer. Right-click and click on Connect. Enter theconnection information and click on OK.

4. Using Windows Explorer, browse to the FME

installation directory, then to \application_extenders\informatica\powercenter7\ and locate thefile fmeintegration.xml. Copy the file into theInformatica installation directory under\RepositoryServer\bin\Plugin\. After this is complete,Return to the Repository Server AdministrationConsole and click on the Available Packages node.Right-click on the newly-copied fmeintegration.xmland select Register.

5. On the Register Plugin screen, select the repository onwhich to register the plug-in. In this example wealready have a repository called FME. Enter ausername and password and select OK.

6. Expand the Repositories node, then expand therepository on which the plug-in was registered.Expand the Registered Packages node under the FMEnode. Click on FME and confirm that FMEConfiguration and FME Transformation are registered.

In formatica 8.x

Extend with FME Administrator

1. Open the FME Administrator from Start > All Programs> FME > FME Adminstrator.

Getting Started


9/36


2. Select FME Extension for Informatica PowerCenter Server 8 and click Extend

3. Select FME Extension for Informatica PowerMart Client8 and click Extend

Register FME Extension with Admin Console

4. You will also have to register the plug-in in therepository service. Select Launch Admin Console fromStart > All Programs > Informatica PowerCenter 8 >Services > Launch Admin Console.

5. Select the repository service in the sidebar

6. Under General Properties click Edit

7. Change OperatingMode to Exclusive, and then click OK

8. Select Yes when asked to Restart Repository Service

9. Choose Complete or Abort (depending on yourpreferences)

10. If the service is not automatically re-enabled, click

Enable to restart it once it has been stopped.

11. Select the Plug-ins tab and click Register RepositoryService plug-in

Getting Started 3


10/36


12. Click Browse and navigate to C:\Program Files\FME(or the directory you installed FME in), then go to\application_extenders\informatica\powercenter8\ andselect fmeintegration.xml.

13. Enter the Repository Username and Password andclick OK

14. A log of the action should appear. You can choose to

Save this log, or just Close

15. The plug-in should now be installed and you shouldsee it in the list of Registered plug-ins and securitymodules

Getting Started 4


11/36


1. Go back to the Properties tab (or select the repositoryservice again) and Edit the General Properties tochange the OperatingMode back to Normal andcomplete the process.

Install Samples

Mappings, workspaces and sample data have beenprovided for this guide. Its recommended that these areinstalled if you plan to try out the examples in this guide.

All FME workspaces and data have been provided in thezip file TutorialData.zip. This guide assumes that thecontents of this zip file are extracted to the C drive,creating the following directories underC:\InformaticaDemoWorkspaces:

FMESourceAndFMEDestination FMESourceAndInformaticaDestination

InformaticaSourceAndFMEDestination InformaticaSourceAndInformaticaDestination

An Access database has been provided (C:\InformaticaDemoWorkspaces\TestData.mdb) that contains a tablecalled Addresses. Import this database into SQL Serverusing the SQL Server Management Studio. First create

the TestData database by clicking on Databases > NewDatabase. Then, right-click on the database and go toTasks > Import Data and follow the instructions in theSQL Server Import and Export wizard to import the datainto the TestData database. Please contact your databaseadministrator if you need assistance with this step.

Getting Started 5


12/36


Getting Started 6

Basic Concepts

After installing and licensing the required software, youcan start to design mappings in PowerCenter that utilizethe capabilities of the FME Extension.

Specify Inputs and Outputs for the FMETransformation

Normally from within the PowerCenter Designer, you willbegin by creating a mapping and configuring an FMETransformation by specifying all the available input andoutput groups, and the associated input and output ports.

Specify FME Workspace Service for the FMETransformation

Once youve done that, you need to specify propertyvalues to associate the FME Transformation with an FMEWorkspace service. The FME Workspace service isrepresented on the file system as a folder containing anFME Workspace file. FME Workspace files can be easilyidentified with the extension . fmw and can be opened withthe FME Workbench Spatial ETL designer.

Configure FME Workspace in FME Workbench

In FME Workbench, you can configure the FME Workspace

to read and write from Informatica formats, read andwrite from over 100 supported FME spatial formats,transform data with over 150 FME spatial transforms,visualize spatial data in the FME Universal Viewer, andmany other things.

For more detailed information on using FME, please referto the FME Tutorial or Introduction to FME course.


13/36


Section 1Transformation w ith FME Source and FME Target

ObjectiveIn this example we will create an Informatica mappingthat has an FME Transformation that reads from an FMEspatial format and writes to an FME spatial format. Inparticular, the FME Transformation will read from the ESRIShape format, apply a transform that counts the numberof rows, and write to the MapInfo TAB spatial format.Since both are spatial formats, this is an example of creating a pure spatial data flow within Informatica.

Mapping

Create Mapping

1. Open the Informatica PowerCenter Designer andconnect to your repository.

2. Click on Tools > Mapping Designer to bring up theMapping Designer canvas.

3. Click on Mappings > Create to create a new mapping.Call this mapping FMESourceAndDestination.

Create FME Transformation

4. Click on the FME Transformation tool on the toolbarand click once on the canvas to add the FMETransformation to the canvas.

Section 1: Transformation with FME Source and FME Target 7


14/36


5. Double-click (or right-click and select Edit) to bring up

the Edit Transformations screen. Click on the Portstab.

6. Click on the Create Input Group tool to add a newInput Group. Call it FMEInit.

7. Select the Add a new port to this transformation toolto add a new input port named Init. Accept the

default values for the other columns.

8. Click on the Create Output Group tool to add a newOutput Group named FMETerminate.

9. Click on the Add a new port to this transformation toolagain to add a new output port named Terminate.

Note: In this example, we are creating an FMEInit andFMETerminate group to pass in a single row from a flat fileto simply initialize and terminate the FME Transformationbecause a transform in a PowerCenter mapping requiresan Informatica Source and Informatica Target to execute.

When the reserved FMEInit input group name is used, theFME Transformation ignores all input data and only utilizesit to initialize the FME Transformation. When the reservedFMETerminate output group is specified, the FMETransformation sends a 1 character row to signal thecompletion of the FME Transformation so that downstreamcomponents can execute. If data from an InformaticaSource is to be used, then FMEInit should not be used.

10. At this point, the Edit Transformations dialog should

appear as follows:

An FME Transformation must point to an FMEworkspace. The workspace for this mapping is calleddemo.fmw and resides in C:\InformaticaDemoWorksp



15/36


aces\FMESourceAndFMEDestination. To refer to thisworkspace, first click on the Metadata Extensions tab.Click once on the Value field for the WorkspaceDirectory extension. Click once on the arrow andenter the path to the larger directory holding all of theFME workspaces (C:\InformaticaDemoWorkspaces\).

11. The Workspace Service is analogous to the foldercontaining the FME workspace to be executed in thisparticular mapping. Enter a value of FMESourceAndFMEDestination under the WorkspaceServices Value column. Click OK. The FMETransformation has now been configured:

Create Source

12. The FME Transformation must be launched. In orderto do this Informatica will read a text file calledFMEInit.txt, which contains one line (StartFME) thatspawns FME. We must import this file as a source.Click on Tools > Source Analyzer to bring up theSource Analyzer canvas. Click on Sources > Importfrom File and navigate to C:\InformaticaDemoWorkspaces\FMESourceAndFMEDestination\SourceData, thenselect Files of Type: All Files (*.*). Select FMEInit.txt

and click OK. The Flat File Import Wizard is launched:



16/36


13. Click Next to go to Step 2, then again to go to Step 3.The column name can be anything, but we will call itInit with a Length/Prec. of 10.e Click Finish to proceed.

Create Target

14. The FME Transformation must also be terminated inthe same fashion it was launched. We will haveInformatica read a text file called FMETerminate.txt,which contains one line (endFME) that terminatesFME. We will import this file as a target. InInformatica 7.x click on Tools > Warehouse Designerto bring up the Warehouse Designer canvas. InInformatica 8.x click on Tools > Target Designer tobring up the Target Designer canvas. Click on Targets> Import from File and navigate to C:\InformaticaDemoWorkspaces\FMESourceAndFMEDestination\Output, then select Files of Type: All Files (*.*). SelectFMETerminate.txt and click OK. The Flat File ImportWizard is launched. Continue clicking on Next untilStep 3. Enter Terminate for the name of the columnwith a Length/Prec. of 10.

Create Link between Source and Transformation

15. Go back to the Mapping Designer by clicking on Tools> Mapping Designer. We will now connect our sourcefile to the FME Transformation container. Drag theFMEInit source from the Sources > Flat File node inthe Repository Navigator to the canvas. Connect theInit field of the Source Qualifier to the Init field in theFME Transformation as shown below.



17/36


Create Link between Transformation and Target

16. Now connect the target file to the FME Transformationcontainer. Drag the FMETerminate target from theTargets folder in the Repository Navigator to thecanvas. Connect the Terminate field of the FMETransformation container to the Terminate field of FMETerminate container.

17. Rearrange the icons by right-clicking on the canvasand selecting Arrange All Iconic.

Save Mapping

18. A mapping that uses an FME Source and an FMEDestination has now been configured. Save therepository by clicking on Repository > Save.

W orkspace ServiceSample FME Workspa ce Service

1. A sample FME workspace service has been providedfor this example. Please refer to the Install Samples section in the Getting Started section.

2. If you followed the instructions in the Install Samples section, you should have a folder calledC:\InformaticaDemoWorkspaces\FMESourceAndFMEDestination which represents the FME Workspace Service.This folder contains an FME workspace which containsan FME source and FME destination. For moreinformation on how to import an FME source and FMEdestination, please refer to the Appendix . To view theFME Workspace, double click on the FME Workspacedemo.fmw file to automatically open it in FMEWorkbench. This workspace takes city parks data inESRI Shape format, calculates the area of each park,tests for parks greater than 250,000 sq. ft and writesthem out to MapInfo TAB format.

WorkflowCreate Work flow

1. This mapping can be run by creating a workflow in theWorkflow Manager. Start the Informatica WorkflowManager in Informatica 7.x or PowerCenter WorkflowManager in Informatica 8.x and connect to your



18/36


repository. In the Workflow Manager click once on thefolder under your repository in the RepositoryNavigator. Click on Tools > Workflow Designer tobring up the Workflow Designer canvas. Click onWorkflows > Create to bring up the Create Workflowscreen. Call the new workflow MyWorkflow and clickOK:

Create Work flow Task

2. Click on Tools > Task Developer, then Tasks > Create.In the Create Task screen enter FMESourceAndFMEDestination for the name of the new task. Click onCreate.

3. Select the FMESourceAndDestination mapping toassociate with this session. Click OK, then Done.

Create Workflow Link

4. Click on Tools > Workflow Designer. Drag theFMESourceAndFMEDestination from the Sessions folderin the Repository Navigator to the canvas. Click on

Tasks > Link Task, then drag the cursor from the Starticon to the FMESourceAndFMEDestination session:

Configure Task

5. Now configure the task. Double-click on theFMESourceAndFMEDestination task to bring up the EditTasks screen. In the Mapping tab select SQ_FMEInitunder the Sources folder. Under Properties, set theSource File Directory to where FMEInit.txt resides(C:\InformaticaDemoWorkspaces\FMESourceAndFMEDestination\SourceData\). Set the source filename toFMEInit.txt (if necessary). Click OK.



19/36



=======================================================

DLL Logged Msg: FME: Completed successfully for customtransformation: 'FME'

Second, navigate to C:\InformaticaDemoWorkspaces\ FMESourceAndFMEDestination\Output in WindowsExplorer and verify that the MapInfo TAB data waswritten. Four files should exist: city_parks_big.dat,city_parks_big.id, city_parks_big.map andcity_parks_big.tab.

=======================================================

First, right-click on the FMESourceAndFMEDestinationtask in the Workflow Monitor and click on Get SessionLog. In the log scroll to the *****START LOADSESSION*****. A few lines below this should be thefollowing line:

9. Verify that the translation was successful by checkingtwo things:

Task Update: MyWorkflow (Succeeded) Start time: 2008-03-28 9:30:23 AM End time: 2008-03-28 9:30:39 AM

Check Results

Save Workfl ow

6. Click on Repository > Save to first save the Repositoryin the Workflow Manger.

Run Workflow

7. Right-click on the canvas and click Start Workflow.The Informatica PowerCenter Workflow Monitorappears.

8. When the Workflow has executed a Status of Succeeded should appear as shown below. (You mayneed to connect to the repository and the service andrestart the workflow)

Keep an eye on the log window. The final messagewill say something similar to:


20/36


Section 2Transformation w ith Informatica Source and

Informatica Target

ObjectiveIn this section we will create a mapping that has an FMETransformation that transforms data from an InformaticaSource and then outputs the transformed data to anInformatica Target.

Mapping

Create Source

1. Set up a connection to the TestData SQL Serverdatabase. Open the Informatica Workflow Manager inInformatica 7.x or PowerCenter Workflow Manager inInformatica 8.x and connect to your repository. Clickon Connections > Relational to open the RelationalConnection Browser. For Select Type, select MicrosoftSQL Server. Click the New button to open theConnection Object Definition screen. Fill in theparameters for your SQL Server connection to theTestData database. An example is shown below.

2. Open the Informatica PowerCenter Designer if it is notalready open. Connect to your repository.

3. Expand your folder and click on Tools > SourceAnalyzer to bring up the Source Analyzer canvas.

4. Add an Informatica source by clicking on Sources >Import from Database to bring up the Import Tablesscreen. Connect to the TestData (SQLServer) ODBCdata source. Select the dbo.Addresses table and clickon OK to add the metadata to the Source Analyzercanvas.

Section 2: Transformation with Informatica Source and Informatica Target 14


21/36


Create Target

5.

In Informatica 7.x click on Tools > WarehouseDesigner. In Informatica 8.x click on Tools > TargetDesigner. Add an Informatica destination by clickingon Targets > Import from Database to bring up theImport Tables screen. Again, connect to the TestData(SQLServer) ODBC data source. Select dbo.Output_Addresses (you may need to create this table inSQLServer first. It should have the same schema asthe source Addresses table). Click OK to add the

metadata to the canvas.

Create Mapping

6. Now that the Informatica source and destination havebeen added it is time to create the mapping. Click onTools > Mapping Designer. Click on Mappings >Create to bring up the New Mapping screen. EnterInformaticaSourceAndInformaticaDestination as thename of the new mapping.

7. Navigate to the Sources folder in the RepositoryNavigator and drag the Addresses source onto thecanvas. Do the same for the Output_Addresses

metadata in the Targets folder.



22/36


Create FME Transformation

8. Click on the FME Transformation tool and click onceon the canvas to add an FME Transformation containerto the canvas.

9. Double-click (or right-click and select Edit) to bring upthe Edit Transformations screen. Click on theMetadata Extensions tab. Click once on the Value field

for the Workspace Directory extension. Click on thearrow and enter the path holding all the FMEworkspaces (C:\InformaticaDemoWorkspaces\).

10. For the Workspace Service enter InformaticaSourceAndInformaticaDestination. This folder contains aworkspace called demo.fmw. Click OK, then OK again.

11. Now configure the input and output groups for the FMETransformation. Double-click on the FMETransformations container to bring up the EditTransformations screen. Click on the Ports tab, thenon the Create Input Group tool . Name the InputGroup AddressesIn. Click on the Create Output Grouptool and name the Output Group AddressesOut (theinput and output groups appear as feature types inFME). Click OK.

Create Link between Source and Transformation

12. Select all the fields in SQ_Addresses and drag themunderneath the AddressesOut section in the FMETransformation container to connect them.

13. These fields really need to be under the AddressesInport. To move these fields to the AddressesIn port,bring up the Edit Transformation screen by double-clicking on the FME Transformation container. Click onthe Ports tab and select the AddressesOut row. Clickon the Move the Selected Port Down tool andcontinue clicking until AddressesOut is below the

Longitude field.



23/36


Create Link between Transformation and Target

14. The output port will contain the same schema as theinput for this simple transformation. In the Ports tabof the Edit Transformation screen, copy each fieldand paste them underneath the AddressesOut port,then checkmark the O (output) column for each anduncheck the I (input) column (the output columnneeds to be checked before the input column isunchecked). Also make sure to add a 1 to the end of each port name so that there is no duplication. ClickOK.

15. On the Mapping Designer canvas, the SQ_Addressesfields have already been mapped to the input ports of the FME Transformation. Now select the AddressesOutfields and map them to the input ports of theOutput_Addresses container.

Save Mapping

16. Save the Repository by clicking on Repository > Save.

W orkspace Service

Sample FME Workspa ce Service


2. If you followed the instructions in the Install Samples section, you should have a folder called C:\InformaticaDemoWorkspaces\InformaticaSourceAndInformaticaDestination which represents the FME WorkspaceService. This folder contains an FME workspace whichcontains an Informatica source and Informatica



24/36



destination. For more information on how to import anInformatica source and Informatica destination, pleaserefer to the Appendix . To view the FME Workspace,double click on the FME Workspace demo.fmw file toautomatically open it in FME Workbench. Thisworkspace takes the AddressesIn Informatica source,sorts the records by city (in ascending order), thenwrites them to the AddressesOut Informaticadestination.

Workflow

Create Wo rkflow

1. Create a new workflow to run this mapping in theWorkflow Manager. Open the Workflow Manager and

connect to your repository.

2. Click on Tools > Task Developer, then Tasks > Create.In the Create Task screen enter InformaticaSourceAndInformaticaDestination for the name of the new task.Click Create. Select the InformaticaSourceAndInformaticaDestination mapping to associate with thesession. Click Done.

3. Click on Tools > Workflow Designer. From theSessions folder in the Repository Navigator, drag the

InformaticaSourceAndInformaticaDestination sessiononto the canvas. Link the session to the Start arrowusing Tasks > Link Task.

4. Save the repository by clicking Repository > Save.

Run Workflow

5. Right-click on the canvas and select Start Workflow. AStatus of Succeeded should appear in the WorkflowMonitor for this workflow.

Check Results

6. Verify the translation was successful by checking thesession log. Right-click on the InformaticaSourceAndInformaticaDestination task in the Workflow Monitorand click on Get Session Log. In the log scroll to theSESSION LOAD SUMMARY section. Verify that 661

rows were affected in the Source Load Summary and661 rows were affected in the Target Load Summary.


25/36


Section 3Transformation w ith Informatica Source and FME

Target

Objective

In this section we will create a mapping that has an FMETransformation that transforms data from an InformaticaSource and then outputs the transformed data to an FMEdestination. This example begins by modifying the

mapping from Section 2 .

Mapping

Create Mapping

1. Open the Designer. We will use one of the existingmappings (InformaticaSourceAndInformaticaDestination) and modify it to use an FME destination.

2. Select Tools > Mapping Designer. If the InformaticaSourceAndInformaticaDestination is not shown on thecanvas, drag it onto the canvas from the Mappingsfolder in the Repository Navigator.

3. Select Mappings > Copy As and type in InformaticaSourceAndFMEDestination for the name of the newmapping.

4. Right-click on the canvas and select Arrange All Iconic:

Informatica 7.x Icons


Section 3: Transformation with Informatica Source and FME Target 19


26/36


5. Select and delete the Output_Addresses (MicrosoftSQL Server) container since we will be replacing thisInformatica target with an FME target.



6. Double-click on the FME Transformation container,then double-click again to bring up the EditTransformations screen. Navigate to the Ports tab anddelete all but one (it does not matter which one) of therows listed under AddressesOut. Since we are writingout to FME, change AddressesOut to FMETerminate.Rename the remaining port to Terminate and set itsprecision to 10.

7. Drag FMETerminate from the Targets folder in theRepository Navigator onto the canvas. This willterminate the FME Transformation, so place it after theFME Transformation container and connect themtogether through the Terminate column.

8. Double-click on the FME Transformation container

again and click on the Metadata Extensions tab.Change the Workspace Service to reflect the folder theworkspace for this section resides in (InformaticaSourceAndFMEDestination). Click OK, then OK again.



27/36


Save Mapping

9. In the Designer, click on Repository > Save to savethe new mapping.

W orkspace Service



2. If you followed the instructions in the Install Samplessection, you should have a folder calledC:\InformaticaDemoWorkspaces\InformaticaSourceAndFMEDestination which represents the FMEWorkspace Service. This folder contains an FMEworkspace which contains an Informatica source andFME destination. For more information on how toimport an Informatica source and FME destination,please refer to the Appendix . To view the FMEWorkspace, double click on the FME Workspace

demo.fmw file to automatically open it in FMEWorkbench. This workspace takes the AddressesIndata from Infomatica, creates points using the latitudeand longitude columns, then writes them out to ESRIShape format.

Workflow

Create Work flow

1. Open the Workflow Manager. Create a new task forthis mapping. Click on Tools > Task Developer, thenTasks > Create. In the Create Task screen enterInformaticaSourceAndFMEDestination for the name of the new task. Click on Create. Select the InformaticaSourceAndFMEDestination mapping to associate withthe session. Click OK, then Done.

2. Click on Tools > Workflow Designer. From theSessions folder in the Repository Navigator, drag theInformaticaSourceAndFMEDestination session onto thecanvas.

3. Rather than linking this session to the Start arrow, wewill chain all three sessions together. Link this sessionto the FMESourceAndFMEDestination session. Deletethe existing link between this latter session and theStart arrow.

4. Create a link between the InformaticaSourceAndInformaticaDestination session and the FMESourceAndFMEDestination session.



28/36

S a f e S o f t w a r e I n c .

Section 3: Transformation with Informatica Source and FME Target

F M E I n f o r m a t i c a T u t o r i a l

22

7. Each task is run in succession in the Workflow Monitor.

5. Click on Repository > Save.

Run Workflow

6. Start the workflow by right-clicking on the canvas andselecting Start Workflow.

Check Results

8. When all three tasks have succeeded executing, verify

that the output files were created in C:\InformaticaDemoWorkspaces\InformaticaSourceAndFMEDestination\Output. There should be three files here:AddressesIn.shp, AddressesIn.shx andAddressesIn.dbf. To view the ESRI Shape data openthe FME Universal Viewer (Start Menu > All Programs> FME > FME Universal Viewer). Click on File > OpenDataset. In the Select Dataset to View screen enterESRI Shape for Format. Enter C:\InformaticaDemo

Workspaces\InformaticaSourceAndFMEDestination\Output\AddressesIn.shp for Dataset. The points aredisplayed in the Universal Viewer:


29/36


Section 4Transformation w ith FME Source and I nformatica

Target

Objective

In this section we will create a mapping that has an FMETransformation that transforms data from an FME sourceand then outputs the transformed data to an Informaticadestination. This example begins by modifying themapping from Section 2 .

Mapping

Create Mapping

1. In the Designer, click Tools > Mapping Designer. Dragthe InformaticaSourceAndInformaticaDestinationmapping onto the canvas from the Mappings folder inthe Repository Navigator.

2. Click on Mappings > Copy As and name the newmapping FMESourceAndInformaticaDestination.

3. There is already an Informatica target in this mapping(AddressesOut); the source needs to be modified to be

an FME source. Begin by removing the existingInfomatica source containers (Addresses andSQ_Addresses).

4. From the Sources folder in the Repository Navigator,drag the FMEInit source onto the canvas.



5. Double-click on the FME Transformation container tobring up the Edit Transformations screen. Click on thePorts tab. Remove all but one of the ports underneath

Section 4: Transformation with FME Source and Informatica Target 23


30/36


the AddressesIn group. Rename the remaining port toInit (with a data type of string and a precision of 10).Also rename the AddressesIn port to FMEInit.

6. Click OK to close the Edit Transformations screen.

7. Link the Init column from SQ_FMEInit to the Initcolumn underneath the FMEInit port of the FMETransformation.

8. Double-click the FME Transformation again to bring upthe Edit Transformations screen. Rename thetransformation and call it FMESourceAndInformaticaDestination.

9. Click on the Metadata Extensions tab. The WorkspaceDirectory is already set. Change the WorkspaceService to FMESourceAndInformaticaDestination. ClickOK.

Save Mapping

10. Save the repository by clicking Repository > Save.

W orkspace Service



2. If you follow the instructions in the Install Samples section, you should have a folder calledC:\InformaticaDemoWorkspaces\ FMESourceAndInformaticaDestination which represents the FMEWorkspace Service. This folder contains an FMEworkspace which contains an FME source andInformatica destination. For more information on howto import an FME source and Informatica destination,please refer to the Appendix . To view the FME



31/36



Workspace, double click on the FME Workspacedemo.fmw file to automatically open it in FMEWorkbench. This workspace takes some NJ and NY

address data in CSV format, filters out the NJ addressrecords and writes them out to Informatica.

Workflow

Create Wo rkflow

1. Now create a workflow in the Workflow Manager forthis new mapping. Open the Workflow Manager andClick Tools > Task Developer. Click on Tasks > Createand name the new task FMESourceAndInformaticaDestination, then click Create. Select the FMESourceAndInformaticaDestination mapping, then click Done.

2. Click on Tools > Workflow Designer. Drag theFMESourceAndInformaticaDestination session from theSessions folder in the Repository Navigator onto thecanvas. Link this session to the InformaticaSourceAndFMEDestination session.

3. Double-click on the FMESourceAndInformaticaDestination task to bring up the Edit Tasks screen. In

the Mapping tab select SQ_FMEInit under the Sourcesfolder. Under Properties, set the Source File Directoryto C:\InformaticaDemoWorkspaces\FMESourceAnd

InformaticaDestination\SourceData\. Set the SourceFilename to FMEInit.txt (if necessary). Click OK.

5.

Right-click on the canvas and select Start Workflow.Wait for all the sessions to finish executing in theWorkflow Monitor.

4. Click on Repository > Save.

Run Workflow

Check Results

6. When the FMESourceAndInformaticaDestinationsession has succeeded, right-click on the session and

select Get Session Log. Navigate to the bottom of thelog (Session Load Summary) to confirm that rowswere written to Output_Addresses.

MAPPING> TM_6253 Target Load Summary.MAPPING> CMN_1740 Table: [Output_Addresses](Instance Name: [Output_Addresses])

Output Rows [593], Affected Rows [593],Applied Rows [593], Rejected Rows [0]


32/36


AppendixFME Workbench

For more detailed information, please refer to the FMETutorial at http://www.safe.com/downloads .

Opening FME Workbench

Start FME Workbench from Start > All Programs > FME >FME Workbench.

Im porting an FME Source AndFME Destination

1. Click on Open the Workspace Dialog then click on OK.2. When the New Workspace screen appears, click on the

browse button next to Format under the Sourcesection. Navigate to ESRI Shape format in theFormats Gallery, select it and click on OK.

Appendix 26
http://www.safe.com/downloadshttp://www.safe.com/downloads


33/36


3. For Dataset, navigate to C:\InformaticaDemoWorkspaces\FMESourceAndFMEDestination\SourceData and select city_parks.shp

4. For the Destination format, select MapInfo TAB usingthe browse button. Click OK.

5. The ESRI Shape to MapInfo TAB translation is shownon the canvas. Save the workspace.

Im porting an InformaticaSource and Destination

Export Informatica Mapping

1. In order to use an Informatica source/destination in anFME workspace, a mapping that uses an FMETransformation must first be exported fromInformatica. We will work with the InformaticaSourceAndInformaticaDestination mapping in this example.

From the Designer, export the InformaticaSourceAndInformaticaDestination mapping by navigating to thisfolder under the Mappings folder in the RepositoryNavigator, right-clicking and selecting Export Objects.

Appendix 27


34/36


Informatica 7.x Repository Navigator

Informatica 8.x Repository Navigator

2. Save the XML to C:\InformaticaDemoWorkspaces\ InformaticaSourceAndInformaticaDestination\SourceData\InformaticaSourceandInformaticaDestination.XML.

3. Click on Close when the Exported Objects screenappears:

Add Info rmatica Source

1. Start FME Workbench from Start > All Programs > FME> FME Workbench.

2. Click on Create a Blank Workspace then click on OK.

3. Add an Informatica source dataset. Click on SourceData > Add Dataset to bring up the Add Source

dataset screen. Using the Browse button selectInformatica as the source format.

4. Click on the Settings button and enter C:\InformaticaDemoWorkspaces\InformaticaSourceAndInformaticaDestination\SourceData\Informatica_Source_and_Informatica_Destination.XML for the metadata file. Click onthe Browse button next to Custom Transformation.

Appendix 28

S f S f I F M E I f i T i l


35/36


The name of the FME Transformation from themapping will be listed (FME) in the InformaticaTransformation List screen. Select this and click on

OK.

5. Click OK again in the Add Source Dataset screen. TheInformatica dataset, with the AddressesIn source fromthe mapping, has been added to the canvas.

6. Anytime an Informatica source is added, certainSource parameters MUST be published. These are:

Source Dataset (must be published by right-clicking the parameter in the Navigation Pane andclicking Publish Parameter)

Source Establishing Host (automatically publishedby default)

Source Port or Service to Use (automaticallypublished by default)

The two automatically published parameters arepublished with the names SourceHost_INFA andSourcePortService_INFA. These names must not be

changed or else the integration will not work.

Add Informatica Destination

1. Add a destination Informatica dataset. Click onDestination Data > Add Dataset. Select Informatica asthe destination format and click on OK. Click on Nowhen prompted to add a new feature type.

2. We will import the destination feature type(AddressesOut). Click on Destination Data > ImportFeataure Type Definitions. Click on the Settingsbutton on the Import Feature Type Definitions screen.When the Informatica Input Settings screen appearsnotice the fields are already populated from the last

time (step 7). Click on OK, then OK again.AddressesOut is added to the canvas.

3. At this point, any number of FME transformers can beadded between the source and destination totransform the data. In C:\InformaticaDemoWorkspaces\InformaticaSourceAndInformaticaDestination\demo.fmw a Sorter transformer has been added tosort the data based on City name. For now, simply

connect the source AddressesIn feature type to thedestination AddressesOut feature type by connect theyellow arrow on the source the red arrow on thedestination:

Appendix 29


36/36

fme extension for in for ma tic a tutorial 2008

Documents