6 Creating and Running the Integration Interface

This chapter describes how to create and run the integration interface.

It contains the following topics:

Creating the Integration Interface

Now that the source and target data models are created, it is possible to create an interface to integrate data from the T005T and T005U SAP tables to the WS_GEO_DS Oracle table.

To create the integration interface, perform the following steps:

  1. Create the Interface

  2. Define the Source and Target Datastores

  3. Define Joins between Sources

  4. Create the Mappings

  5. Define the Interface Flow

Create the Interface

  1. Open Designer.

  2. In the Projects tree view, expand the SAP Demo Project.

  3. Expand the SDE_SAP_GeoDimension folder.

  4. Select the Interfaces node.

  5. Right-click and select Insert Interface.

  6. In the Interface Definition tab, enter the interface name: SDE_SAP_GeoDimension.W_GEO_DS.

  7. Select the Diagram tab.

  8. Click OK if the following information window appears.

    Description of image034.jpg follows
    Description of the illustration image034.jpg

The interface diagram looks as follows:

Description of image036.jpg follows
Description of the illustration image036.jpg

Define the Source and Target Datastores

To define the source and target datastores:

  • In the Models tree view, select the WS_GEO_DS datastore from the Oracle Target Model.

  • Drag this datastore into the Target Datastore panel (right area of the Diagram tab). This panel appears now as shown below:

    Description of image038.jpg follows
    Description of the illustration image038.jpg

  • In the Models tree view, select the T005T Country Names datastore from the SAP ERP Source model.

  • Drag this datastore into the Sources diagram panel (left area of the diagram tab).

  • If a popup window prompting "Do you want to perform Automatic Mapping?" appears, click No.

  • Perform the same operation for the T005U Taxes: Region Key datastore from the SAP ERP Source model. The Sources diagram should look as shown below:

    Description of image040.jpg follows
    Description of the illustration image040.jpg

Define Joins between Sources

To create joins between the source datastores of an interface:

  1. In the Sources diagram drag the SPRAS column from the T005T Country Name datastore onto the SPRAS column in the T005U Taxes: Region Key table. This defines the first join on the Language Key identifier, as shown below:

    Description of image042.jpg follows
    Description of the illustration image042.jpg

  2. Drag the LAND1 column from the T005T Country Name table onto the LAND1 column in the T005U Taxes: Region Key table. The join is extended with the new columns. In the Properties panel, you can see the join clause: (T005T.SPRAS=T005U.SPRAS) AND T005T.LAND1=T005U.LAND1

  3. In the Properties panel, select Ordered join and make sure that this join is executed on the Source as shown below:

    Description of image044.jpg follows
    Description of the illustration image044.jpg

  4. Make sure that table number of T005U is smaller than that of T005T. In 1:n relationships the parent table has to be the first in a join.

Create the Mappings

To create the mappings of the target datastore:

  1. Select the LANGUAGE KEY column from the target datastore.

  2. Drag the SPRAS column from the T005T Country Names source table into the Implementation field of the Properties panel.

  3. Make sure that the Execution On is set to Source. The mapping should look as shown below:

    Description of image046.jpg follows
    Description of the illustration image046.jpg

  4. Repeat this operation to perform the following simple mappings:

  • W_GEO_DS.COUNTRY = T005T.LAND1

  • W_GEO_DS.COUNTY = T005T.LANDX

  • W_GEO_DS.STATE_PROV = T005U.BLAND

  • W_GEO_DS.NATIONALITY = T005T.NATIO

Define the Interface Flow

To define the interface flow:

  1. Select the Flow tab. The Flow diagram looks as shown below;

    Description of image048.jpg follows
    Description of the illustration image048.jpg

  2. Select the source set SS_0 (SAP_ERP) that contains both the T005T and T005U tables. This source set represents the source dataset made up of two joined SAP ERP tables.

  3. In the LKM Selection box, select LKM SAP ERP to Oracle (SQLLDR).

  4. Select the Target + Staging Area box. This represents the target Oracle database, which is also used as the staging area for this interface.

  5. In the LKM Selection, select LKM SAP ERP to Oracle (SQLLDR)or LKM SAP ERP to SQL.

  6. In the IKM Selection, select IKM Oracle Incremental Update.

  7. Set the IKM options as follows:

  1. Select No for the FLOW_CONTROL option.

  2. If you are using FTP for the data transfer, please set the FTP_TRANSFER_METHOD option to FTP. If the FTP server is installed on your ODI agent machine, select NONE.

  3. Leave other options as is. The flow should look as shown below:

    Description of image050.jpg follows
    Description of the illustration image050.jpg

  4. Click OK to save and close your interface.

Running the Integration Interface

This section contains the following topics:

Running the Interface

To run the integration interface:

  1. In the Projects tree view, expand the SAP Demo Project

  2. Expand the SDE_SAP_GeoDimension folder.

  3. Expand the Interfaces node.

  4. Select the SDE_SAP_GeoDimension.W_GEO_DS interface.

  5. Right-click and select Execute.

  6. In the Execution window that appears, click OK.

  7. Click OK in the Session Started window.

What happens when you run the interface?

First, Oracle Data Integrator creates an ABAP program that does the following:

  • It performs the extraction of the data on the SAP engine. In this example, the extraction joins the two source tables and then returns the joined resultset in an extraction file.

  • It uploads the extraction file into a FTP data server. This FTP host is specified via the File Logical Schema called File Server for SAP ABAP. This Logical Schema is mapped to a Physical Schema and therefore a data server in the given context. This data server contains the connection information for the FTP host.

Then, the ABAP code is uploaded using the OdiSapAbapExecute tool. The ABAP program is pushed into the SAP Function group given in the SAP_FUNCTION_GROUP_NAME LKM option. This phase can be suppressed by setting the UPLOAD_ABAP_CODE LKM option to No.

The ABAP code is executed also by the OdiSapAbapExecute tool. At the end of the ABAP code execution, the extraction file is available in the FTP host.

The Oracle Data Integrator agent is able to download this extraction file from the FTP host, or directly access it, depending on the FTP_TRANSFER_METHOD specified in the IKM option.

Finally, when the agent accesses the extraction file, it uses SQL*Loader or a JDBC Connection to load this file into the Oracle/non-Oracle staging area. The rest of the integration process takes place within the Oracle/non-Oracle engine.

Review the Interface Execution

To review the interface execution:

  1. Connect to Operator.

  2. In the Operator, select the Session List tree view.

  3. Expand the All Executions node in this tree view.

  4. The latest session is the first entry of this list and appears as shown below.

    Description of image052.jpg follows
    Description of the illustration image052.jpg

  5. Double click the SDE_SAP_GeoDimension.W_GEO_DS node to see the session details and the number of lines processed.

Review the Resulting Data

To review the resulting data:

  1. In the Designer, in the Models tree view, expand the Oracle Target Model.

  2. Select the W_GEO_DS datastore, right-click and select Data to view the data integrated into the target Oracle table.