Home / Middleware / Oracle Data Integrator

2 Setting Up the Environment

This chapter describes how to set up your SAP ERP environment for use with Oracle Data Integrator.

This chapter includes the following sections:

• Before You Begin

• Validating the Shared Folder Setup

• Validating the FTP Setup

• Validating SAP Privileges

• Validating SAP Transport Layer Name

• Installing Oracle Data Integrator

• Installing and Configuring the Oracle DB Target

• Installing and Configuring JCo

• Setting Up an FTP Server

• Configuring Oracle Data Integrator

Before You Begin

Before you begin, consider the following:

• System Requirements and Certification

• Getting the Right Privileges

• Gathering SAP Connection Information

System Requirements and Certification

Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.

The Oracle Data Integrator requirements are listed in the Installing and Configuring Oracle Data Integrator.

The requirements specific to the Oracle Data Integrator SAP ABAP Adapter are:

• Oracle Data Integrator 11.1.1.7.0 or above.

  Note:

  Most of this documentation also applies to the SAP connectivity in Oracle BI-Applications, which requires Oracle Data Integrator 10g, as specified in more detail in System Requirements and Supported Platforms for Oracle Business Intelligence Applications for SAP.

• A Java Connector (JCo) version compatible with adapter must be used. The list of supported JCo versions is available in the Certification Matrix available from Oracle Technology Network (OTN). A minimum version of JCo 3.0.2 is required.

• A JVM version compatible with both Oracle Data Integrator and JCo must be used. A minimum version of JVM 1.5 is required due to JCo pre-requisites.

• The connector supports two transfer modes for transferring data from SAP system to the ODI agent: data transfer using a Shared Directory and data transfer through FTP. For details and restrictions see Section 8.7.1, "File Transfer Considerations" of Application Adapters Guide for Oracle Data Integrator. Depending on the chosen file transfer mode the following requirements must be met:

  • Data transfer through a Shared Directory (recommended transfer method) The LKM SAP ERP to Oracle (SQLLDR) requires a folder that is shared between the SAP system and the ODI agent. The SAP application server transfers the data by writing it out into a folder that is accessible from the SAP system and the ODI agent machine. This is typically done by sharing a folder of the ODI agent machine with the SAP system. Note that the shared folder does not necessarily have to be located on the ODI agent machine. A shared folder on a third machine is also possible, as long as the shared folder is accessible to both the ODI agent machine and the SAP system.

    Note:

    For security reasons, the SAP folders should not be shared with the ODI agent. Share only folders of the ODI agent machine with the SAP system.

    The shared folder must be accessible to SAP system and not just to the underlying operating system. This means that the folder needs to be declared in SAP transaction AL11 and the folder opens successfully in AL11.

  • Data transfer through FTP LKM SAP ERP to Oracle (SQLLDR) requires a FTP server to upload data from the SAP ERP system. This data is either read locally by the agent executing the mapping (when this agent runs on the FTP server machine), or remotely (when this agent is located on a different machine than the FTP server). This FTP server must be accessible over the network from both the SAP ERP machine and the agent machine.

• For LKM SAP ERP to Oracle (SQLLDR) only: SQL*Loader is required on the machine running the agent the executed mappings using LKM SAP ERP to Oracle (SQLLDR). SQL*Loader is used for loading data extracted from SAP to the Oracle staging area.

Getting the Right Privileges

The SAP Adapter requires privileges to perform set up and execution operations. Please provide your administrators with the list of privileges listed in Appendix A, "SAP ABAP ERP Required Privileges." These privileges are required for the SAP user that they will provide you to login the SAP System.

Gathering SAP Connection Information

In order to connect to the SAP ERP system, you must request the following information from your SAP administrators:

• SAP ERP System IP Address or Hostname: IP address/ Hostname is the technical name given to the host on which SAP is running.

• SAP User: SAP User is the unique user name given to a user for logging on the SAP System.

• SAP Password: Case-sensitive password used by the user to log in.

• SAP Language: Code of the language used when logging in For example: EN for English, DE for German.

• SAP Client Number: The three-digit number assigned to the self-contained unit which is called Client in SAP. A Client can be a training, development, testing or production client or represent different divisions in a large company.

• SAP System Number: The two-digit number assigned to a SAP instance which is also called Web Application Server or WAS.

• SAP System ID: The three-character, unique identifier of a SAP system in a landscape.

• SAP SNC Connection Properties (optional) SAP Router String (optional): SAP is enhancing security through SNC and SAP router. It is used when these securities are implemented.

• SAP Transport Layer Name: This string uniquely identifies a transport layer in a SAP landscape. It allows ODI to create transport requests for later deployment in SAP. Even though there is a default value here, this transport layer name must be provided by your SAP Basis team. Not doing so may result in significant delays during installation.

• SAP Version: The version of the SAP system.

• SAP Character Set: The character set is only required if your SAP system is not a UNICODE system. For a complete list of character sets, see "Locale Data" in the Oracle Database Globalization Support Guide. For example, EE8ISO8859P2 for Croatian Data. For UNICODE systems, use UTF8.

Note:

All the connection data listed above (except SAP SNC Connection Properties and SAP Router String) are mandatory and should be requested from the SAP Administrators. You may consider requesting support during connection setup from your SAP administrators.

Validating the Shared Folder Setup

Validating a shared folder setup applies only if you plan to transfer data using a Shared Directory. This section can be skipped, if the FTP transfer is used. The validation of the shared folder setup needs to be performed before any subsequent steps in this guide can be performed. This validation is typically performed by your SAP Basis team.

Step 1: 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. 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.

   Note:

   SAP uses the OS user <sid>adm to connect to the directories declared in AL11.

6. Print screen.

Step 2: Validating folder access from the ODI agent machine

1. Log in to the ODI agent machine using the Windows user ID used for executing the ODI agent.

2. Open shared directory path in Windows Explorer.

   This should list the content of the shared folder. Please make sure that you do NOT need to enter any credentials. If this does not work or you had to enter credentials, please contact your windows administrator or the system administrator of the system your shared folder is physically located on. Do not continue until this test passes.

3. Print screen.

Validating the FTP Setup

Validating an FTP setup applies only, if you plan to transfer data using FTP. This section can be skipped, if you use a Shared Directory for the data transfer. The validation of the FTP setup needs to be performed before any subsequent steps in this guide can be performed. This validation is typically performed by your SAP Basis team.

Step 1: Validating SAPFTPA destination

1. Start the SAPGUI.

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

3. Go to transaction SM59.

4. Expand TCP/IP connections.

5. Open the SAPFTPA destination.

6. Click Test connection.

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

7. Print screen.

Step 2: Testing FTP connection

1. Go to transaction SE38.

2. View the function module RSFTP002.

3. Hit F8 to run the ABAP program.

4. Enter the FTP userID and password.

5. Enter the FTP server host name or IP address.

6. Enter cd / or cd <ODI target directory>.

7. In the RFC_DESTINATION field, enter SAPFTPA.

8. Hit F8 to run the test.

   This should report a successful connection. The message should be similar to the following:

   250 CWD successful.

   If this test is not successful, please contact your SAP basis team. Do not continue until this test passes.

9. Print screen.

Validating SAP Privileges

This section describes how to test some of the key SAP privileges. Proceed with the subsequent steps in this guide only after successful validation of these tests. This validation is typically performed by your SAP Basis team.

Perform the following steps to validate whether a SAP user has appropriate dev rights and owns a dev license key:

1. Start SAPGUI.

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

3. Go to transaction SE38.

4. Enter any sample program name like ZSAP_TEST in the program name field.

5. Click Create.

6. Perform similar tests for the transaction SE37 and SE11.

If a transaction allows the creation of a program without asking for any key or other authorization message, then the SAP user has validated that it has the appropriate dev rights and license key. Otherwise your SAP basis team needs to register the SAP user in service.sap.com to get the license key and a Basis person can help him with dev rights.

Validating SAP Transport Layer Name

As the SAP connector creates SAP objects, such as, for example, function modules, into the SAP development system, these changes need to be transported into QA and production systems once the development is done. The SAP's change and transport system uses the SAP Transport Layer Name to identify the route a change has to take. A transport layer is assigned to each development class and thus to all objects in that class. The transport layer determines:

• In which SAP System developments or changes to the repository objects are made

• If objects are transported to other systems within the group when development work has been completed

A consolidation route is created from the development system to the quality assurance system through the transport layer Z<SID>. It then becomes the standard transport layer for customer development and customizing.

A consolidation route is created from the development system to the quality assurance system through the transport layer SAP for the transport of SAP Standard objects.

It is important to specify the correct transport layer name before running the RKM SAP ERP for the first time. Perform the following steps to identify the list of defined transport layers in your SAP landscape:

1. Log on in client 000 in the SAP System serving as the transport domain controller via transaction STMS.

2. Select Overview > Transport Routes. The Display Transport Routes dialog is displayed.

3. Select Goto > Graphical Editor.

4. To switch the mode, select Configuration > Display <-> Change.

5. Position the cursor on the SAP System.

6. Select Edit > System > Change. The Change System Attriburtes dialog is displayed.

7. Select the StandardTransport Layer tab.

8. Change the transport layer of the SAP System.

9. The result is the list of the different transport Layers.

By default, the RKM option SAP_TRANSPORT_LAYER_NAME is set to SAP. Ask your SAP basis admin which transport layer you should use. This transport layer name must be set on the SAP_TRANSPORT_LAYER_NAME RKM option. A wrong or invalid transport layer name will cause serious delays during the installation process.

Installing Oracle Data Integrator

Before starting with this project, you need to install and configure Oracle Data Integrator. See the Oracle Fusion Middleware Getting Started with Oracle Data Integrator and the Installing and Configuring Oracle Data Integrator for more information.

Installing and Configuring the Oracle DB Target

This project uses an Oracle database engine as the target and as the staging area for the mappings. You can download for example Oracle Database 11g Express Edition from Oracle Technology Network (OTN). Install and configure this database.

This project targets an Oracle table that can be created using the following script:

-- Create demo target schema
CREATE USER ODI_SAP_DEMO IDENTIFIED BY ODI_SAP_DEMO DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO ODI_SAP_DEMO;

-- Create demo target table
CREATE TABLE ODI_SAP_DEMO.W_GEO_DS (
      LANGUAGE_KEY CHAR(20), 
      COUNTRY VARCHAR(50),
      COUNTY VARCHAR(50),
      STATE_PROV VARCHAR(50),
      NATIONALITY VARCHAR(50));

ALTER TABLE ODI_SAP_DEMO.W_GEO_DS ADD CONSTRAINT PK_W_GEO_DS PRIMARY KEY (LANGUAGE_KEY);

Installing and Configuring JCo

The SAP adapter uses Java Connector (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 Certification Matrix available at Oracle Technology Network. Note that a minimum version of JCo 3.0.2 is required.

   Notes:

   • Choose the SAP JCo package matching your operating system and your system architecture (32/64Bit). E.g. if you are running ODI inside a 32-Bit JVM, you must download the 32-Bit SAP JCo, even if the CPU and OS are 64-Bit. Mixing 32-bit and 64-bit architecture is not possible due to native libraries required by SAP JCo and will result in connection failure.

   • odi.conf contains the JDK path used for ODI Studio.

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:

   For ODI 10g: Copy sapjco3.jar and sapjco3.dll (or respective binary like libsapjco3.so) into the oracledi/drivers directory.

   For ODI 11g: Copy sapjco3.jar and sapjco3.dll (or respective binary like libsapjco3.so) into the <ODI_HOME>/odi_misc directory (ODI Studio) and into the <ODI_HOME>/oracledi/agent/drivers directory (Standalone Agent).

   For ODI 12c: Copy sapjco3.jar and sapjco3.dll (or respective binary like libsapjco3.so) into the <ODI_HOME>/odi/sdk/lib directory.

5. Restart ODI Studio and any agents.

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

Setting Up an FTP Server

The SAP adapter extracts SAP data and uploads it to an FTP server. Configure an FTP server or use an existing FTP server. You must create a user in this FTP server and a directory into which this user will be able to upload files.

Configuring Oracle Data Integrator

Perform the following steps to configure Oracle Data Integrator:

1. Set Up the Topology

2. Add the Open Tool

Set Up the Topology

Perform the following operations after installing or upgrading your Oracle Data Integrator version:

1. Connect to Topology Manager.

2. If this SAP ABAP technology does not exist in your Master Repository, import the SAP ABAP technology in Synonym INSERT_UPDATE mode from the /impexp folder.

3. Perform an upgrade of the Master Repository. Refer to the Installing and Configuring Oracle Data Integrator for more information on the Master Repository upgrade process.

4. In Topology Manager, open the JavaBeanShell technology and check on the Language tab that the JYTHON language is listed. If not, add it.

5. Create a File data server pointing to an existing FTP server into which the extraction file will be pushed from SAP and picked up for SQL*Loader. Set the parameters for this data server as follows:

   • Host (Data Server): FTP server IP host name or IP address.

   • User: Username to log into FTP server.

   • Password: Password for the user.

6. For use with "LKM SAP ERP to SQL" these additional parameters must be configured:

   • JDBC driver class: com.sunopsis.jdbc.driver.file.FileDriver

   • JDBC URL: jdbc:snps:dbfile?ENCODING=UTF8

     The above URL is for SAP UNICODE systems. For non-UNICODE systems, please see details on ENCODING parameter in section 3.3.1, "Creating a File Data Server" of Connectivity and Knowledge Modules Guide for Oracle Data Integrator. The encoding chosen on this URL must match the code page used by the SAP Application Server.

7. In this File data server create a Physical Schema representing the folder in the FTP host where the extraction file will be pushed. Specify the Data and Work Schemas as follows:

   • Data Schema: Path on the FTP server to upload or download extraction files from the remote location. This path is used when uploading extraction files from the SAP ERP system into the FTP server. It is also used by a remote agent to download the extraction files. Note that this path must use slashes and must end with a slash character.

   • Work Schema: Local path on the FTP server's machine. This path is used by an agent installed on this machine to access the extraction files without passing via the FTP server. This access method is used if the FTP_TRANSFER_METHOD parameter of the LKM SAP ERP to Oracle (SQLLDR)/ LKM SAP ERP to SQL is set to NONE. As Work Schema is an OS file name, slashes/ backslashes should be used according to OS.

     Path names need to end on slash/ backslash. Path names given on Data and Work schemas are not necessarily the same: the FTP service may provide access to a FTP directory named /sapfiles while the files can be stored locally in c:\inetpub\ftproot\sapfiles.

   Refer to Section 8.7.1, "File Transfer Considerations" of Application Adapters Guide for Oracle Data Integrator.

8. If the corresponding Logical Schema called File Server for SAP ABAP does not exist yet, create it. This Logical Schema name is fixed and must be mapped to the Physical Schema created in the previous step.

Add the Open Tool

The complete process of installing and adding an Open Tool to ODI is described in Appendix A, "Oracle Data Integrator Tools Reference" of Developer's Guide for Oracle Data Integrator. This section details only the SAP ABAP specific steps.

1. Connect to Designer.

2. Select File > 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._OdiSapAbapExecuteStoppable

4. Click Add Open Tool.

5. The Add Open Tools window appears as shown below.

   
   Description of the illustration image002.gif
   
   

6. Click OK.