This chapter describes how to create and run the mapping.
This chapter includes the following sections:
Now that the source and target data models are created, it is possible to create a mapping to integrate data from the T005T
and T005U
SAP tables to the WS_GEO_DS
Oracle table.
To create the mapping, perform the following steps:
Open Designer.
In the Projects tree view, expand the SAP Demo
Project.
Expand the SDE_SAP_GeoDimension
folder.
Select the Mappings node.
Right-click and select New Mapping.
In the Mapping Definition tab, enter the mapping name: SDE_SAP_GeoDimension.W_GEO_DS
.
Save the mapping.
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 area of the Logical diagram. (Right area of the Logical diagram)
In the Models tree view, select the T005T Country Names
datastore from the SAP ERP Source
model.
Drag this datastore into the Sources
area of the Logical diagram.
Perform the same operation for the T005U Taxes: Region Key
datastore from the SAP ERP Source
model. The Sources area of the Logical diagram should look as shown below:
To create joins between the source datastores of a mapping:
In the Sources area of the Logical diagram drag the SPRAS attribute from the T005T Country Name
datastore onto the SPRAS
attribute in the T005U Taxes: Region Key
datastore.
On the Create Lookup or Join popup, select Join and click OK. This defines the first join on the Language Key identifier, as shown below:
Drag the LAND1
attribute from the T005T Country Name
datastore onto the LAND1
attribute in the T005U Taxes: Region Key
datastore. The join is extended with the new attributes. In the Properties panel, you can see the join clause: T005T.SPRAS=T005U.SPRAS AND T005T.LAND1=T005U.LAND1
In the Properties panel, select Generate ANSI Syntax and Join Order options, and make sure that the Execute on Hint option is set to Source as shown below:
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.
To create the mappings of the target datastore:
Select the LANGUAGE KEY
attribute from the target datastore.
Drag the SPRAS
attribute from the T005T Country Names
source datastore onto the LANGUAGE KEY attribute of the target datastore.
Make sure that the Execution on Hint option is set to Source. The mapping should look as shown below:
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
To define the mapping flow:
Select the Logical diagram. The Flow diagram looks as shown below;
In the target group, select the access point for the source group that contains both the T005T
and T005U
datastores. This source group represents the source dataset made up of two joined SAP ERP datastores.
In the Property panel, under Loading Knowledge Module, select LKM SAP ERP to Oracle (SQLLDR)
or LKM SAP ERP to SQL
from the Loading Knowledge Module drop-down list.
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
.
Leave other options as they are.
In the target group, select the target Oracle database, which is also used as the staging area for this mapping.
In Property panel, under Integration Knowledge Module, select IKM Oracle Incremental Update
from the Integration Knowledge Module drop-down list.
Set the IKM options as follows:
Select No
for the FLOW_CONTROL option.
Leave other options as they are.
From the File menu, click Save and close your mapping.
This section contains the following topics:
To run the mapping:
In the Projects tree view, expand the SAP Demo
Project
Expand the SDE_SAP_GeoDimension
folder.
Expand the Mappings node.
Select the SDE_SAP_GeoDimension.W_GEO_DS
mapping.
Right-click and select Execute.
In the Execution window that appears, click OK.
Click OK in the Session Started window.
What happens when you run the mapping?
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
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.
To review the mapping execution:
Connect to Operator.
In the Operator, select the Session List tree view.
Expand the All Executions node in this tree view.
The latest session is the first entry of this list.
Double click the SDE_SAP_GeoDimension.W_GEO_DS
node to see the session details and the number of lines processed.
To review the resulting data:
In the Designer, in the Models tree view, expand the Oracle Target
Model.
Select the W_GEO_DS
datastore, right-click and select Data to view the data integrated into the target Oracle table.