Creating an Interface

The following operations must be performed to create an interface:

1. Create a New Interface.

2. Define the Target Datastore.

3. Define the source datastores, filters and joins on these sources.

4. Define the mapping between source and target data.

5. Define the interface flow.

6. Define the flow control.

7. Execute the interface for testing.

1. Create a New Interface

To create a new interface:

1. In the Designer tree, select the Interfaces node in the folder under the project where you want to create the interface. Right click and select insert interface. A blank interface appears in the right panel of the Designer window.

2. Fill in the interface Name.

3. Check the Staging Area different from Target box if necessary and select a logical schema that will be the staging area.

Note: The staging area defaults to the target. It may be necessary to put it on a different logical schema if the target does not have the required transformation capabilities for the interface. This is the case for File, JMS, ... logical schemas. It is also necessary to define a staging area for interfaces with a temporary target datastore.

4. Go to the Diagram tab to proceed.

2. Define the Target Datastore

The target datastore is the element that will be loaded by the interface. This datastore may be permanent (defined in a model) or temporary (created by the interface in the staging area).

2.1 Permanent Target Datastore

To insert the permanent target datastore in an interface:

1. Expand the tree to show the datastore to be inserted as the target.

2. Select the datastore, then drag and drop it as the target of the interface (in the right part of the diagram tab). The target datastore appears in the diagram.

To display the data of the permanent target datastore of an interface:

1. Right click the title of the target datastore in the diagram tab of the interface window.

2. Select data.

A window containing the data of the target datastore appears. Data in a temporary target datastore cannot be displayed since this datastore is created by the interface.

2.2 Temporary Target Datastore

To add a temporary target datastore:

1. In the Diagram tab, double-click the title of the target datastore: Untitled.

2. In the property pane, type in a name for this datastore. Specify if you wish to create the temporary datastore in the Work Schema or Data Schema of the staging area.

Note: You can define a temporary target datastore only if the staging area is different from the target.

Note: The temporary target datastore will be created only if you activate the KM option CREATE_TARGET_TABLE when defining the flow.

The temporary target datastore is created without columns. They must be added to define its structure.

To add a column to a temporary target datastore:

1. Right click the title of the target datastore.

2. Select Add a column.

3. A new empty column appears. Double click this new column in the target and fill in the target column fields (See Mapping).

To delete a column from a temporary target datastore:

1. Right click the column to be deleted from the target datastore.

2. Select Delete.

To add all of the columns from a source datastore to a temporary target datastore:

1. Add the source datastore.

2. Select the title of the entity representing the source datastore.

3. Right click and select Add to the target.

2.3 Define the update key

If you want to use update or flow control features in your interface, it may be necessary to define an update key on the target datastore. This key identifies each record to update or check before insertion into the target. This key can be a unique key defined on the target datastore in its model, or a group of columns specified as a key for the interface.

To define the update key from a unique key:

1. In the Diagram tab, double-click the title of the target datastore.

2. In the property panel, select the Update key from the list.

Note: Only keys defined in the model appear in this list.

To define the update key from the columns:

1. In the Diagram tab, double-click one of the target datastore's columns that is part of the update key.

2. In the properties panel, check the key box. A key symbol appears in front of the column in the mapping.

3. Repeat the operation for each column that making up the update key.

3. Define the Source Datastores

The source datastores contain data used to load the target datastore. Two types of datastores can be used as an interface source: The datastores from the models and temporary datastores target of an interface.

The source datastores of an interface can be filtered during the loading process and must be put in relation through joins. Joins and filters can be recovered from the model definitions and can also be defined for the interface.

3.1 Define the Source Datastores

To add a permanent-type source datastore to an interface:

1. Select the datastore then drag and drop it onto the composition panel (left part of the diagram tab). The datastore appears in the diagram model.

Warning: If there are any filters on the datastore, or references between this datastore and existing datastores in the diagram, they appear along with the datastore. These references and filters are copied as joins and filters in the interface. They are not links to existing references and filters from the model. Therefore, modifying a reference or a filter in a model does not affect the join or filter in the interface, and vice versa.

Using journalized datastores: If the datastore is journalized, it is possible to use only the journalized data in the interface flow. Check the Journalized Data only box in the source datastore properties. A Journalizing filter is automatically created in the diagram.

To add a temporary-type source datastore to an interface:

1. Select the interface, then drag and drop it onto the composition panel (left part of the diagram tab). The target datastore of this interface appears in the diagram model.

To delete a source datastore from an interface:

1. Right click the title of the entity (table) representing the source datastore.

2. Select Delete. A confirmation window appears. Select Yes.

The source datastore disappears, along with the associated filters and joins.

To display the data or the number for rows of a source datastore of an interface:

1. Right click the title of the entity (table) that represents the source datastore.

2. Select Number of rows or data.

A window containing the number or rows or the data of the source datastore appears.

3.2 Define Filters on the sources

To define a filter on a source datastore:

1. Select a column in the entity representing the first table to join, and, holding it selected, drag and drop it onto the composition panel. A filter appears in the model.

2. Modify the implementation to create the required filter. You may call the expression editor by clicking

3. Select the execution location: source or staging area.

4. Click to validate the expression, then click to save.

5. Check the Active Filter box to define whether of not  you want this filter to be used for the execution of the interface.

To delete a filter on a source datastore of an interface:

1. Select the filter to remove.

2. Right click and select Delete.

To display the data or the number of rows resulting from a filter:

1. Right click the filter.

2. Select data or number of rows.

A window containing the data or the number of rows after the filter appears.

3.3 Define Joins between sources

To create a join between the source datastores of an interface:

1. Select a column in the entity representing the first table to join, and, holding it selected, drag and drop it on a column in the entity of the second table to join.

2. A relation linking the two tables appears in the model. In the implementation field, an equality between the two columns concerned also appears.

3. Modify the implementation to create the required relation. The columns of all the tables in the model can be drag-and-dropped into the join text to create multi-table joins. You may call the expression editor by clicking .

4. Click to validate the expression, then click to save.

5. Select the execution location: source or staging area.

6. Select the type of join (right/left, inner/outer, ISO), and the order number.

7. Check the Active Clause box to define whether of not  you want this join be used for the execution of the interface.

To delete a join between source datastores of an interface:

1. Right click the relation that represents the join to remove.

2. Select Delete.

To display the data or the number of rows resulting from a join:

1. Right click the relation representing the join.

2. Select data or number of rows.

A window containing the data or the number of rows resulting from the join appears.

4. Define the mappings

The mapping defines the transformation rules on the sources, allowing the to generation of data to load the target.

The mapping is filled automatically at each new source or target insertion using column names matching. The user-defined mapping always takes precedence over name matching.

To regenerate the automatic mapping by name matching:

1. Right click the target datastore.

2. Select Redo Automatic Mapping.

The target datastore columns are automatically mapped on the source datastores' columns with the closest name.

To define the mapping of a target column:

1. Double click the column of the target datastore.

2. Modify the implementation in the composition panel to perform the required transformation. The columns of all the tables in the model can be drag-and-dropped into the text. You may call the expression editor by clicking .

3. Click to validate the expression, then click to save.

4. Select the execution location: source, target or staging area. See Mappings in the Reference Manual for restrictions on the possible execution locations.

5. Check the boxes from the update zone if you want the mapping to be executed in Insert or Update operations or in KM-specific operations (UD1 to UD5)

6. Check the Active Mapping box to define whether of not  you want this mapping to be used for the execution of the interface.

5. Define the interface flow

In the flow tab, you define the loading and integration strategies for mapped data. Oracle Data Integrator automatically computes a group of source-sets depending on the mapping rules, joins and filters of the interface's diagram. It proposes default KMs for the data flow. The flow tab enables you to view the source-sets and the KMs used to load and integrate data. If you want to define the strategies for the flow, you must change the KMs in use.

To change the KMs in use:

1. In the interface's flow tab, select a source-set by clicking its title.

2. In the properties panel, select a LKM or IKM depending on the strategy to set. For more information on the KM choices, refer to the KM documentation.

3. Set the KM Options.

4. Repeat the operation for all source-sets, then click on Apply.

Note: Only KMs that are relevant for the technologies in use and that are imported into the project appear in the list of KMs.

Note: Some IKM require the use of an update key.

Note: When changing from one KM to another, any options of the same type and the same name are preserved.

6. Set up flow control

The flow control strategy enables you to define the method used to check the flow before insertion into the target. The control strategy is defined by a CKM.

To define the CKM used in an interface:

1. In the Control tab of the interface, select a CKM. For more information on the KM Choice, refer to the KM documentation.

2. Set the KM Options.

3. Select the constraints to be checked.

4. Fill in the Maximum number of errors allowed and check the % box if you want the interface to fail when a number (or percentage) of errors is reached.

5. Click Apply.

Note:Only CKM that are relevant for the technologies in use and that are imported into the project appear in the CKM lists.

Note: Some CKM require the use of an update key.

Note: Only the constraints declared on the target datastore in its model appear in the constraints list.

Warning: Flow control will be executed only if  the FLOW_CONTROL option is activated in the IKM (Flow tab).

7. Execute the interface

Once the interface is created, it is possible to test it.

To run an interface:

1. In the interface's Execution tab, click Execute.

2. If you have not saved your interface yet, then click Yes when asked to save it.

3. Define the Execution options, then click OK.