Sap connectivity with Oracle Data Integrator

Sap connectivity with Oracle Data Integrator:

Author: Himanshu Porwal
Connect him in Linkedin

To know more about the Overview and System Requirement you can visit this link where Oracle has mentioned the details on SAP ABAP KM, Perquisites etc. However the description given in this link is based on ODI 10G.. Today we will do all the configuration and changes on ODI 11g.

http://www.oracle.com/technetwork/middleware/data-integrator/overview/odigs-sapabap-168070.pdf

Prerequisite for SAP connectivity

• SAPServiceONS user which is by default in SAP server, should be created in server of shared directory folder, It has same user name and password as SAPServiceONS user and should have the administrative right(SAPserviceONS) to the shared folder.
• A SAP dialog user is required for connecting to the SAP system, for the RKM installation, and for the LKM execution during development. A SAP RFC user is NOT sufficient. During development, this user must have a developer license key. License keys can be retrieved from http://service.sap.com. Any execution attempts without this developer license key will lead to failure and may cause the need for clean-up operations. For RKM and LKM execution in production, a SAP RFC user is sufficient

Validating the Shared Folder Setup

This validation is typically performed by your SAP Basis team.

• Validating folder access from the SAP application server

1. Start the SAPGUI.

2. Use the ODI SAP user and password to connect to the SAP system and client.

3. Go to transaction AL11.

4. Create the shared  folder and assign in AL11 and Select the shared folder.

5. Double-click the shared folder to test the directory declaration.

This should report a successful connection. If not, please contact your SAP basis team. Do not continue until this test passes.

• Validating folder access from the ODI agent machine

Refer the doc for more details.

Validating SAP Privileges

Refer the doc for more details.

Validating SAP Transport Layer Name

Refer the doc for more details.

Installing and Configuring JCo

 The SAP adapter uses JCo to connect to the SAP system. JCo must be configured before proceeding with the project.

To install and configure JCo:

1. Download a supported JCo version for your configuration from http://service.sap.com/connectors. Check the supported JCo version in the Compatibility Matrix available at Oracle Technology Network. Note that a minimum version of JCo 3.0.2 is required.

2. Unzip the appropriate distribution package into an arbitrary directory {sapjco-install-path}.

3. Follow the installation instructions in {sapjco-install-path}/javadoc/installation.html for the respective platform.

4. Copy sapjco3.jar and sapjco3.dll (or respective binary) into the ODI_HOME/odi_misc directory.

5. Check the JCo installation. This will be also checked later in this guide.

Add the Open Tool

 The complete process of installing and adding an Open Tool to ODI

This section details only the SAP ABAP specific steps.

1. Connect to Designer.

2. Select ODI > Add/Remove Open Tools…

3. In the Add/remove Open Tools window, enter the following name in the Open Tool class name field:

oracle.odi.sap.km._odiSapAbapExecute

4. Click green (+) button to Add Open Tool.

5. Click OK

Note: sapjo3.jar file should be in <ide.user.dir>, to know actual path go to Help–> About section—> property

Configuring the SAP Source Server

Configuring the Data Server:

Go to topology and create a new data server under SAP ABAP technology. Give the SAP User id and password.

Set the Flexfield values for this data server in the Flexfields tab.

Now create the physical schema

In the Physical Schema window, do not edit the Definition tab. Select the Context tab, click Add and enter the Logical Schema name LOGICAL_SAP_ERP

Create a New Project

Now create a new project and add the following KMs.

IKM Oracle Incremental Update

LKM SAP ERP to Oracle (SQLLDR)

RKM SAP ERP

RKM SAP ERP Connection Test

Reverse-Engineering the Data Models

Reverse-Engineering the SAP Source Datastores

Here we have to do a Customized reverse engineering using RKM SAP ERP.

Validating the SAP Connection

1. In the Models tree view, open the SAP ERP Source Model

2. Click Reverse and confirm the start of the reverse-engineering process. This process runs two tests:

• Establish a test connection to the SAP system and
• Validate the proper setup of SAP JCo by displaying the About dialog.
• In a few seconds, you should see the SAP JCo-About dialog as shown here:

3. In a few seconds, you should see the SAP JCo-About dialog as shown here:

4. Once you have validated SAP JCo installation, open ODI Operator.

5. In the Operator, expand the Reverse-Engineering session down to the task level.

6. Verify whether the task “Test SAP Connection” has been executed successfully. If not, view the task details to identify the connection problem.

Note: Do not continue until you have successfully validated the SAP connection!

Starting the Reverse-Engineering Process

Reverse-Engineering without GUI

As mentioned in the doc to perform a reverse-engineering without using the SAP Metadata Browser, we can enter the following parameters in the SAP ERP KM options:

USE_GUI: No

SAP_TABLES_NAME : T005T, T005U

With this configuration, the tables will be reverse-engineered without displaying the SAP Metadata Browser.

By using the appropriate KM options, you can also select a filter for the reversed tables based on for example a SAP Module or a Package name. You can also specify a table name mask in the Mask field of the Reverse tab to filter table names.

To start the reverse-engineering process of the SAP ERP datastores:

1. In the Models tree view, open the SAP ERP Source Model.

2. In the Reverse tab:

a. Select the Global context.

b. Select the Customized option.

c. Select the RKM SAP ERP you have imported in the SAP ERP Demo project.

d. Set the USE_GUI KM option to YES.

e. For first time RKM use only: Set SAP_TRANSPORT_LAYER_NAME to the name of the transport layer your SAP basis admin has provided you with.

f. For first time RKM use only: Set UPLOAD_ABAP_CODE to Yes After the first execution this value must be set back to No.

g. Set other parameters according to your SAP configuration.

3. Click Apply to save your changes.

4.  Click on reverse engineer button.

You might face few errors while attempting to do your 1^st reverse engineering. Details on the error and solution given below.

Error 1:

Solution:
Put SapJco3.dll, SapJco.jar, and SapJco3.jar file into proper location. In ODI 11g location must be .ODI_HOME\odi_misc directory
and in ODI 10g location would be ODI_HOME\oracledi\driver directory

Error 2:

Solution: To resolve it, delete the package ZODI_DEV_CLASS from your SAP system. Ask SAP basis admin to delete the mentioned package.

Error 3:

Solution: Change agent to no local agent as below

Here is the preview of the log displayed in operator.

Now verify the datastore in the model.

Using the SAP Metadata Browser:

As you have set the USE_GUI option to Yes, the RKM displays in the SAP Metadata Browser the SAP objects organized in the tree view as shown below.

The Tree View

The SAP Modules are organized into a tree view. Only the SAP Modules available to the current user are displayed. By expanding the tree view, you can see SAP tables, and select those that you want to reverse-engineer.

Note that only the tables selected in the tree view are reverse-engineered.

What happens when you reverse-engineer SAP ERP tables?

Refer the above doc.

Now lets create the interface

.

FILE_TRANSFER_METHOD:

Specifies how the extracted data is transfered to the ODI agent machine. Permitted values are:

FTP:
SAP AS pushes data file to FTP server and ODI agent downloads data file from FTP server via FTP.

SFTP:
SAP AS pushes data file to FTP server and ODI agent downloads data file from FTP server via SFTP.

SCP:
SAP AS pushes data file to FTP server and ODI agent downloads data file from FTP server via SCP.

NONE:
SAP AS pushes data file to the FTP server installed on ODI agent machine.

FSMOUNT:
SAP AS writes data into file on shared directory of File Server. ODI agent copies the file from shared directory.

FSMOUNT_DIRECT:
SAP AS writes data into file on shared directory of ODI agent machine.

FILE_PASSIVE_MODE:
Specify mode of FTP transfer.

Everything is done. Now execute the interfaces and see the log in operator navigator.

Thats all for today.

Special thanks to Himanshu for contributing his experience with ODI Community.