| Oracle® Fusion Middleware Application Adapters Guide for Oracle Data Integrator 11g Release 1 (11.1.1) E17466-03 |
|
 Previous |
 Next |
| Oracle® Fusion Middleware Application Adapters Guide for Oracle Data Integrator 11g Release 1 (11.1.1) E17466-03 |
|
Previous |
Next |
This chapter describes how to work with SAP BW Knowledge Modules in Oracle Data Integrator.
This chapter includes the following sections:
The SAP BW Knowledge Modules let Oracle Data Integrator connect to SAP-BW system using SAP Java Connector (SAP JCo) libraries. These adapters allow mass data extraction from SAP-BW systems.
If this is the first time you are using the SAP BW adapter, it is recommended to review the Oracle Data Integrator Getting Started with SAP ABAP BW Adapter on Oracle Technical Network (OTN):
http://www.oracle.com/technetwork/middleware/data-integrator/learnmore/index.html
It contains the complete pre-requisites list as well as step-by-step instructions including SAP connection testing.
The SAP BW Knowledge Modules for Oracle Data Integrator use mature integration methods for SAP-BW system, in order to:
Reverse-Engineer SAP BW metadata
Extract and load data from SAP BW system (source) to an Oracle Staging Area
The reverse-engineering process returns the following SAP BW objects inside an ODI model:
Each ODS/DSO object is represented as an ODI datastore.
Each InfoObject will be represented in ODI as a submodel containing up to three datastores:
InfoObjects having master data have a master data datastore containing all InfoObject attributes
InfoObjects having attached text data have a text datastore containing all text related columns
InfoObjects having hierarchies defined have a hierarchy datastore containing all hierarchy related columns
Each InfoCube will be represented as a single ODI datastore. This datastore includes columns for all characteristics of all dimensions as well as for all key figures.
Each OpenHubDestination is represented as an ODI datastore.
Oracle Data Integrator provides the Knowledge Modules listed in Table 6-1 for handling SAP BW data.
The Oracle Data Integrator SAP BW Knowledge Modules provide integration from SAP BW systems using SAP JCo libraries. This set of KMs has the following features:
Reads SAP BW data from SAP BW system.
Loads this data into Oracle Staging Area.
Reverse-engineers SAP Metadata and proposes a tree browser to select only the required Metadata.
Uses flexfields to map the SAP BW data targets types (InfoCube, InfoObject, ODS/DSO, OpenHub and Text Table) and their columns.
Table 6-1 SAP BW Knowledge Modules
| Knowledge Module | Description |
|---|---|
|
LKM SAP BW to Oracle (SQLLDR) |
Extracts data from SAP BW system into a flat file and then loads it into Oracle Staging Area using the SQL*LOADER command line utility. |
|
RKM SAP BW |
Reverse-engineering Knowledge Module to retrieve SAP specific metadata for InfoCubes, InfoObjects (including Texts and Hierarchies), ODS/DSO and OpenHubDestinations. |
The RKM SAP BW enables Oracle Data Integrator (ODI) to connect to SAP BW system using SAP JCo libraries and perform a customized reverse-engineering of SAP BW metadata.
The LKM SAP BW to Oracle (SQLLDR) is in charge of extracting and loading data from SAP BW system (source) to an Oracle Staging Area.
|
Note: Access to SAP BW is made through ABAP. As a consequence, the technology used for connecting is SAP ABAP, and the topology elements, as well as the model will be based on the SAP ABAP technology. There is no SAP BW technology in ODI, but SAP BW-specific KMs based on the SAP ABAP technology. |
Reverse-engineering uses the RKM SAP BW.
This knowledge module automatically installs dedicated RFC programs to retrieve SAP BW metadata. It extracts the list of SAP BW data objects and optionally displays this list in a Metadata Browser graphical interface. The user selects from this list the SAP BW objects to reverse-engineer.
In the reverse-engineering process, data targets, primary keys, foreign keys and index are reverse-engineered into an Oracle Data Integrator model.
Data integration from SAP is managed by the LKM SAP BW to Oracle (SQLLDR).
This KM is used for interfaces sourcing from SAP BW via ABAP and having a Staging Area located in an Oracle Database.
The KM first generates optimized ABAP code corresponding to the extraction process required for a given interface. This code includes filters and joins that can be processed directly in the source SAP BW server. This ABAP program is automatically uploaded and is executed using the OdiSAPAbapExecute open tool to generate the extraction file.
The KM then transfers this extraction file to a pre-configured FTP server. This file is downloaded from this server using FTP, SFTP or SCP to the machine where the Oracle Staging Area is located, and is finally loaded using SQL*Loader to the staging area. The agent can also directly read the extraction file on the FTP server's disk if the FTP server is installed on the same machine as the ODI agent.
The rest of the integration process (data integrity check and integration) is managed with other Oracle Data Integration KMs.
Make sure you have read the information in this section before you start working with the SAP BW data:
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 list of supported platforms and versions is available on Oracle Technical Network (OTN):
http://www.oracle.com/technology/products/oracle-data-integrator/index.html.
Some of the Knowledge Modules for SAP BW use specific features of SAP-BW system and Oracle database. This section lists the requirements related to these features.
A JCo version compatible with adapter must be used. The list of supported JCo versions is available from the Oracle Technology Network (OTN). See Section 6.2.1, "System Requirements and Certifications" for more information.
A JVM version compatible with both Oracle Data Integrator and JCo must be used.
SQL*Loader is required on the machine running the agent when executing interfaces using LKM SAP BW to Oracle (SQLLDR). SQL*Loader is used for loading data extracted from SAP to the Oracle Staging Area.
The LKM SAP BW to Oracle (SQLLDR) requires a FTP server to upload data from the SAP BW system. This data is either read locally by the agent executing the interface (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 BW machine and the agent machine. We recommend using a FTP server installed on the ODI agent machine instead of on a third machine. This allows using the FTP_TRANSFER_METHOD = NONE and optimizes performance. See File Transfer Configurations for more information.
Oracle Data Integrator connects to the SAP-BW system hosting the SAP BW data using JCo. It also uses a FTP Server to host the data extracted from the SAP system.
This section describes the required connection information:
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:
Download a supported JCo version for your configuration from http://service.sap.com/connectors.
Unzip the appropriate distribution package into an arbitrary directory <sapjco-install-path>.
Follow the installation instructions provided in the JCo documentation (<sapjco-install-path}/javadoc/installation.html)for your platform.
Copy the required files (sapjco3.jar and sapjco3.dll) into the <ODI_HOME>/odi_misc directory.
Restart the ODI Components using SAP (ODI Studio, Standalone Agent)
Check the JCo installation.
In order to connect to the SAP BW system, you must request the following information from your SAP administrators:
SAP BW System IP Address or Hostname: IP address/ host name of 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 BW Version: 3.5 or 7.0
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. |
The SAP BW system will push data to a server using the FTP protocol. Collect the following information from your system administrator:
FTP server name or IP address
FTP login ID
FTP login password
Directory path for storing temporary data files
Validate that the FTP server is accessible both from SAP System and from ODI agent machine.
The SAP Adapter requires privileges to perform setup and execution operations. Please provide your administrators with the list of privileges listed in Appendix A, "Additional Information for SAP ABAP BW Adapter".
These privileges are required for the SAP user to log in the SAP System with ODI. This user is specified when creating the SAP data server.
You must define the two data servers used for SAP integration. The SAP ABAP Data Server and the FTP data server.
This data server corresponds to the FTP server into which the extraction file will be pushed from SAP and picked up for SQL*Loader.
Create a File data server as described in "Creating a File Data Server" of the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator and 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
Data Schema: Path on FTP server
In this File data server create a Physical Schema as described in "Creating a File Physical Schema" of the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator.
This schema representing the folder in the FTP host where the extraction file will be pushed. Refer to Section 6.7.1, "File Transfer Configurations" for more information.
Specify the Data and Work Schemas as follows:
Directory (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 BW 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.
Directory (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 FTP. This access method is used if the FTP_TRANSFER_METHOD parameter of the LKM SAP BW to Oracle (SQLLDR) is set to NONE. The Work Schema is a local directory location. As a consequence, slashes or backslashes should be used according to the operating system. This path must end with a slash or backslash.
Path names given on Data and Work schemas are not necessarily the same: the FTP server may provide access to a FTP directory named /sapfiles - the value for Directory (Schema) - while the files are accessed locally in c:\inetpub\ftproot\sapfiles - the value for Directory (Work Schema).
Create a File Logical Schema called File Server for SAP ABAP, and map it to the Physical Schema. The name of this Logical Schema name is predefined and must be File Server for SAP ABAP.
This SAP ABAP data server corresponds to the SAP server from which data will be extracted.
To configure a SAP ABAP data server:
Create a data server for the SAP ABAP technology using the standard procedure, as described in "Creating a Data Server" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This data server uses the SAP connection information.
Set the parameters for this data server as follows:
Name: SAP_BW. The name of the data server as it will appear in ODI.
Host (Data Server): SAP BW System IP Address or host name.
User: SAP BW User, as provided by the SAP Administrator.
Password: This user's SAP BW Password. This password is case-sensitive.
Set the flexfields values for this data server in the Flexfields tab.
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: SNC Connection Properties. This parameter is optional and can be left empty.
SAP Router String: Router String. This parameter is optional and can be left empty.
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.
SAP BW Version: Enter the SAP BW Version as follows:
For SAP BW 7.0 systems enter 700
For SAP BI 3.5 systems enter 350
SAP ERP Version & SAP ABAP Version: not used in SAP BW Connector.
|
Note: The Test button for validating the SAP Connection and the FTP Connection definition is not supported. |
Except for Data Server Name, all the parameters that you provide while defining the SAP Data Server should be provided by the SAP Administrators. See Gathering SAP Connection Information for more information about these parameters.
To configure a SAP ABAP schema:
Create a Physical Schema under the SAP ABAP data server as described in "Creating a physical schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This schema does not require any specific configuration. Only one physical schema is required under a SAP ABAP data server.
Create a Logical Schema for this Physical Schema as described in "Creating a Logical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator in the appropriate context.
Setting up a project using SAP BW features follows the standard procedure. See "Creating an Integration Project" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Import the following KMs into your Oracle Data Integrator project:
RKM SAP BW
LKM SAP BW to Oracle (SQLLDR)
In addition to these specific SAP BW KMs, import the standard Oracle LKMs, IKMs, and CKMs to perform data extraction and data quality checks with an Oracle database. See "Oracle Database" in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator for a list of available KMs.
This section contains the following topics:
Create an SAP BW Model based on the SAP ABAP technology and on the SAP ABAP logical schema using the standard procedure, as described in "Creating a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
To perform a Customized Reverse-Engineering with the RKM SAP BW, use the usual procedure, as described in "Reverse-engineering a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields specific to SAP BW:
In the Reverse tab of the SAP BW Model, select the RKM SAP BW.
For the RKM SAP BW, set the USE_GUI KM option to true.
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.
|
Caution: A wrong or invalid transport layer name will cause serious delays during the installation process. Please use only the value provided by your SAP Basis Admin. |
For first time RKM use only: Set UPLOAD_ABAP_CODE to Yes.
After the first execution this value must be set back to No.
Save the model.
Click Reverse-Engineer in the Model Editor toolbar.
Set UPLOAD_ABAP_CODE back to No.
For first time RKM use only: Validate in Operator that the session is now in status running.
If session has failed, please validate settings. Do not move on until all installation steps have been completed successfully.
The Tree Metadata Browser appears after the session is started. Select the data store object(s) to reverse.
Click Reverse-Engineer in the Tree Metadata Browser window.
The reverse-engineering process returns the selected data store objects as datastores.
|
Note: If the reverse-engineering is executed on a run-time agent, the USE_GUI option should be set to false. This option should be used only when the customized reverse-engineering is started using the agent built-in the Studio. |
To create an interface loading SAP BW data into an Oracle staging area:
Create an interface with source datastores from the SAP BW Model. This interface should have an Oracle target or use an Oracle schema as the Staging Area.
Create joins, filters and mappings for your interface.
In the Flow tab of the interface, select the source set containing the SAP BW source data object(s) and select the LKM SAP BW to Oracle (SQLLDR).
This section includes the following topics:
The ODI SAP adapter extracts data using ABAP programs. At the end of the extraction process these ABAP programs upload the data to a FTP server. For better performances this FTP server should be located on the same machine as the run-time agent.
If the agent is not located on the same machine as the FTP server, it will download the file from the FTP server before loading it to the staging area using SQL*Loader. This download operation is performed using FTP, SFTP or SCP.
Figure 6-1 Configuration 1: FTP Server is installed on an ODI Agent machine
The configuration shown in Figure 6-1 is used, when FTP_TRANSFER_METHOD = NONE. In this configuration the following data movements are performed:
The ABAP program extracts the data and uploads the data file to the FTP server.
SQL*Loader reads locally the data file and loads the data into the Oracle staging area.
This configuration requires the following Topology settings:
Create a File data server pointing to the FTP server:
Host (Data Server): FTP server host name or IP address.
User: User name to log into FTP server.
Password: Password for the user.
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:
Directory (Schema): Path on the FTP server for uploading SAP extraction files.
Directory (Work Schema): Local path on the FTP server's machine containing the SAP extraction file. The agent and SQL*Loader read the extraction files from this location.
Figure 6-2 Configuration 2: FTP Server is not installed on ODI Agent machine
The configuration shown in Figure 6-2 is used, when FTP_TRANSFER_METHOD is FTP, SFTP or SCP. In this configuration the following data movements are performed:
The ABAP program extracts the data and uploads the data file to the FTP server.
The ODI agent downloads the file from the FTP server into the directory given by KM Option TEMP_DIR.
SQL*Loader reads the data file from this TEMP_DIR and loads the data into the Oracle staging area.
This configuration requires the following Topology settings:
Create a File data server pointing to the FTP server:
Host (Data Server): FTP server host name or IP address.
User: User name to log into FTP server.
Password: Password for the user.
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:
Directory (Schema): Path on the FTP server for uploading SAP extraction files.
Directory (Work Schema): <undefined>; this path is left blank, as data files are never accessed directly from the FTP server's file system.
During development, ABAP code is uploaded to the SAP system with every interface execution. This upload can be explicitly turned off by setting the LKM option UPLOAD_ABAP_CODE to No.
Once an Interface or Package has been unit tested and is ready to be migrated out of the development environment, ODI should no longer upload ABAP code, as the ABAP code will be transported by SAP's CTS (Change and Transport System).
Alternatively, the upload can be turned off using the FlexField SAP Allow ABAP Upload defined on the SAP data server in the Topology: The ABAP code is only uploaded, if both the LKM option UPLOAD_ABAP_CODE and the Flexfield SAP Allow ABAP Upload are set to Yes. For disabling upload in production systems you can set the Flexfield SAP Allow ABAP Upload to 0 in the Topology.
|
Note: Before starting the extraction process, ODI verifies that the interface/scenario matches the code installed in SAP. If there is a discrepancy - for example, if the scenario was modified but the ABAP code was not re-uploaded - an exception is thrown. |
During the RKM and LKM execution several log files are created. These log files may contain valuable details for troubleshooting. Table 6-2 describes the different log files and their usage:
Table 6-2 Log Files
| Default Log File Name | KM / Phase | Content |
|---|---|---|
|
|
RKM |
Execution Log of metadata retrieval |
|
|
RKM |
Information about first time installation of SAP RFC for RKM |
|
|
LKM - Generation Time |
Information about code generation for ABAP extractor |
|
|
LKM - Runtime |
Information about installation of ABAP extractor |
|
|
LKM - Runtime |
Information about Delta Extraction |
|
|
LKM - Runtime |
SQL*Loader log file |
|
|
LKM - Runtime |
OS std output during SQL*Loader execution, may contain information, e.g. when SQL*Loader is not installed |
|
|
LKM - Runtime |
OS error output during SQL*Loader execution, may contain information, e.g. when SQL*Loader is not installed |
The SAP ABAP BW adapter has the following limitations:
The Test button for validating SAP Connection definition in ODI's Topology manager is not supported.
The SAP BW data store type (InfoCube, InfoObject, ODS/DSO, OpenHub, Hierarchy, and Text Table) cannot be changed after a table has been reverse-engineered.
The SAP ABAP KMs only support Ordered Joins.
Full Outer join and Right outer joins are not supported.
In one-to-many relationships (InfoCube and associated InfoObject join), the first data target should be InfoCube and then InfoObjects and its TextTables.
Hierarchy datastores cannot be joined on source with any other SAP BW objects.
Text datastores of InfoObjects having no master data cannot be joined on source with any other SAP BW objects.
OpenHub datastores cannot be joined on source with any other SAP BW objects.
Only column RSHIENM can be filtered on using a constant string value, for example HIER_0GL_ACCOUNT.RSHIENM = 'MYHIER1'
