Easy ETL

The EasyETL perspective in the Configuration Editor is a highly specialized way of looking at your IBM® Security Directory Integrator configurations, dedicated to get you up and running fast with projects that revolve around simple tasks to Extract, Transform and Load data (ETL) into some form of database.

The EasyETL perspective shows EasyETL projects and lets you run, open and create new ETL projects. The ETL perspective can be shown by choosing Window > Open Perspective and then selecting the Easy toETL perspective.

To start the CE with this perspective add -perspective com.ibm.tdi.rcp.perspective.etl to the CE command line (ibmditk).

Figure 1. EasyETL main window

Use the New Project button to create a new EasyETL project. An EasyETL project is a normal IBM Security Directory Integrator project with a single AssemblyLine with two connectors. Double click or select the project and press the Enter key to open the ETL editor.

The menu for an ETL project has the following items:

Figure 2. EasyETL project menu

• Open – Open the project in the editor
• Open with full AssemblyLine Editor – Open the project in the advanced AssemblyLine editor
• Run fast – Run the project without collecting data from the AssemblyLine
• Run– Run the project and display collected data in the Data-Collector view
• Create files … – Generate files needed to run the project from the command line
• Rename – Rename the project
• Delete – Delete the project

The EasyETL editor shows the two connectors with a table that shows the mapping between the two connectors. The initial screen shows an empty selection for both connectors as shown in this picture:

Figure 3. Initial EasyETL project window

You then typically start by choosing the source connector. There are four options in the type dropdown:

• File System Connector
• LDAP Connector
• Database Connector (JDBC)
• Select Connector...

The top three are quick selections since they are commonly used. The last option, Select Connector… brings up the standard connector selection dialog where you can choose any connector you want. However, the list of connector is limited to those that implement the mode for the source (Iterator) and target (AddOnly) connector.

Once the connector is chosen you can configure the connector. The configuration dialog contains the forms to configure the connector and parser if it is required. In addition, the Delta screen is available for the source connector.

If you select the LDAP Connector you will see the following window:

Figure 4. LDAP Connector in Easy ETL

After discovering the attributes in the connector you can check those attributes you want to read in from the connector. For the target connector, only the list of schema items is present since the mapping is based on the attributes of the input connector.

Once the schema and attributes are available they will be shown in the source attribute column.

Figure 5. Input/Output mapping

Items that are grayed out are attributes that are not mapped. Right click and choose Map Attribute to map the attribute. You can also double click or press the Enter key to map the current selection. In the target attribute column you can click and choose a different output attribute name. Conversely, you can do the same on a mapped attribute to return it to the list of unmapped attributes.

To customize the mapping between the two attribute check the Show Transformations check box. This will add a new table between the source and target tables.

Figure 6. Input/Output mapping, with Transformations

The transformation table shown has arrows that denote verbatim copies between two attributes. Double click a transformation item to open the JavaScript editor for that map.

Figure 7. Transformation script

Enter the script that will perform the custom transformation of the value. Note that all mapped attributes in the source connector are available as top-level beans. This means that you can refer directly to cn instead of using the work.cn notation. The editor is also aware of the Java™ class based on what has been read by the Read and Write Next Record action. The last entry read is also used when you test the script with the Evaluate button so the evaluation of the script can be tested against real live data as shown in the picture above. The message shows the input value and the result of the transformation script (output). The JavaScript Help button is a quick way to access the help page for JavaScript.

The target connector has a check box labeled Disable. This check box, when checked, will disable the output connector and instead dump the entry (after custom transformations) to the console log.

After configuring the connectors you can now run the AssemblyLine either to completion or by stepping one record at a time through the AssemblyLine. When stepping through the AssemblyLine the table will reflect the last entry read and written to the target connector. When you click the Run button the AssemblyLine will execute continuously until it completes or you press the Stop button. When the stop button is pressed during execution, the AssemblyLine breaks and gives control back to you. When the stop button is pressed while you have control, the AssemblyLine terminates. When the AssemblyLine has terminated it shows a completion dialog with some statistics about the run:

Figure 8. Completion dialog