Pentaho with SAP Connectivity using RFC and BAPI
When it comes to ERP, many great companies have committed to SAP system.
SAP system consists of a number of fully integrated modules, which covers virtually every aspect of the business management.
So the complete enterprise data is stored in SAP and now companies need to translate data into information to plan for future business strategies and decisions.
Combining SAP with business intelligence tools like Pentaho can create real added value for client business.
So, is it possible to connect Pentaho with SAP system to extract data using ETL process?
We can use Pentaho Data Integration ETL tool to connect to SAP systems and extract data using RFC call or BAPI functions.
A user can thereby integrate the BAPI into Pentaho’s extract-transform-load (ETL) process to get to the SAP data.
ETL is a method to transform extracted data of various formats to fit operational needs on a target database or data warehouse.
SAP Input Step
This SAP Input step in Pentaho Data Integration calls functions on SAP systems and can load tables via the function RFC_READ_TABLE or customized functions like BAPI.
We need to get sapjco3.jar and sapjco3.dll from the marketplace and copy these files into the lib folder of data-integration.
Below mentioned is the description of each field in SAP Input step
Step name The name of this step as it appears in the transformation workspace.
Connection The SAP system properties need to be defined in a connection with the SAP Database.
Function ThDefine the function name to call on the SAP system.
Input Define the Input parameters of the SAP function.
Output Define the Output parameters of the SAP function.
Sample RFC/ZRFC Function Usage In SAP Input Step
Remote Function Call (RFC) is the standard SAP interface for communication between SAP systems. Data transactions are not limited to getting data from the server, but can insert data into server records as well. SAP can act as the Client or Server in an RFC call.
Here ZRFC_READ_TABLE function is used to return data from table which we provide as input.
Here field denotes the input parameter name while SAP Parameter Name is the name of the parameters used by default in ZRFC SAP function.
In the input section we need to provide inputs such as
DELIMITER - The output data will be seperated based on the delimiter we provide as input. Eg : ";" NO_DATA - If the output data has null values then it will be replaced with the value provided in NO_DATA. Eg : "NA" QUERY_TABLE - This parameter defines the name of the SAP table. Eg : "BSEG ROWCOUNT - This parameter is limit the number to rows to be returned in output ROWSKIPS - By default set as 0 FIELDNAME - We need to provided the list of columns and its data that needs to be returned from the SAP table. FILTER - We can define the Filter conditions to limit the data retrieval. Eg : PERIOD EQ '2013'
WA is the output field which returns the table data in single column seperated by the delimiter provided in input parameter
Sample BAPI Function Usage In SAP Input StepM
BAPI (Business Application Programming Interface) is a set of interfaces to object-oriented programming methods that enable a programmer to integrate third-party software into the proprietary R/3 product from SAP.
In the input section we need to provide input conditions to restrict the output data that matches to input conditions.
Based on the conditions provided in Input, the output section will retrieve the list of columns and its respective data.
Most of the times when ETL runs with SAP system, we would face communication link failure. So make sure the connectivity between SAP and Pentaho ETL system is good.
Sometimes due to high data volume in SAP system, Pentaho ETL process would consume longer duration to fetch data from SAP system. Make sure to increase the memory heap space of Pentaho PDI and also use better hardware infrastructure.
The user password expires often at regular intervals. Make sure there is provision for external configuration of user credentials so that we do open the ETL code often to change the credentials.