Kettle example to connect to SAP (with plugin ProERPConn)

In a previous post of blog we saw that we could connect Talend Sap without buying any additional plugin or add-Open Studio version. Just had to have the Java plug sapjco.jar, which SAP offers its customers free, and use components and tSapOutput tSapInput Talend. Communication with Sap went both ways, allowing both reading and writing in way of the function modules (RFC calls). Moreover, in the paid versions of Talend, thereadditional features include wizards, direct reading of the Sap metadata (data dictionary, function modules and BAPIs, retrieval of the RFC and online test) and therefore greater ease of use, since in the Open version is very difficult to configure the component and their documentation is conspicuous by its absence.


Navigation RFC function modules of SAP from Talend

If we are to Pentaho Data Integration (Kettle), there is a connector available in the free version.But if we have a payment option through the plugin ProERPConn , Proratio company at a cost of 2,900 Euros per seat. They have a trial version 30 days, which is what I used to draw from this example. We will explain how to configure and use an example.

Plugin Settings (Windows).

Once we checked into the website of the manufacturer, will receive an email with the documentation and a compressed archive of files to add new "step" or component to our settings Kettle.The steps are:

  • Unzip the downloaded file in the [Kettle-Directory] \ plugins \ steps. This is the place from which to read the application to have available the plugin and use it as a step or further step in the transformation.
  • Installing the connector sapjco of Sap: It'll drop to sapjco.jar file in the same directory as before. In addition, it will copy the file to the directory sapjcorfc.dll [Kettle-Directory] \ libswt \ win32. Finally, if you have a previous version of the dll librfc32.dll in the [Windows] \ System32, replacing it with which we have downloaded from Sap (you remember to download the sapjco have to be customers or have user Sap your portal).JCO versions that we use are 2.1.6 or 2.1.8.

At this point, we can start Kettle and creating a new transformation, we appear in the Input group ProERPconn step - PRORATIO SAP Connector. When placed in the design window when you double click on will request the activation key ( send us the manufacturer to send the corresponding Product Key).


Another step available in the Input section for the new plugin

Once activated, the component is available to start making reading tables from SAP, all in an integrated manner with the data dictionary.To better understand how the plugin, we know that behind this using the Remote Function Call to SAP RFC_READ_TABLE call that allows us to perform both read the dictionary of data for these tables, as well as its content. The tool, in the event that the purchase offers a customized RFC addition to the standard that offers performance improvements, security access controls, allowing the use of floating point fields and solves some problems that exist with version 4.7 of Sap.In our case, as the demo, this RFC have not improved, since we are in version 4.7, we encountered some problems of malfunctioning of the plugin to take readings from Sap.

Let's see a complete example of reading data. We will remove the Material Master Sap doing a lookup against additional tables.

Plugin use to retrieve data from material master.

First, let's define the database connection that will allow us to connect to SAP. By creating a new one, after installing the Plugin, we see a new type called SAP R / 3 System. In connection definition indicate the host where it was installed our Sap (Host Name), the number of system (System Number.Usually 00, unless you have multiple servers), the principal (Sap Client), language (Language. Important because it determines the language in which subsequently recover data dictionary definitions) and finally the user and password to be used (it used the permissions you have this user. It is important that the user has authorization to execute the function module RFC_READ_TABLE and permissions to read at different tables).


Sap Plugin - Definition Connection

A definite connection, we can use the step for reading data.In our example, let's read the material master records (Table MARA). With the data read, will make a lookup to complete the description of the material (Table Matka) and Material Group (Family).


Sap Plugin - Configuration Step

In the picture we see the different elements that can be configured in step.Let's see a bit more in detail each of them:

  • Sap Connection: Sap is the connection that you previously defined in the database connection.
  • Tablename: name of the table where we read the data. With the Find button table has a wizard to find the tables in the SAP Data Dictionary. The search can do for the name of the table, for its description, the fields included in it, the primary keys or foreign.In the search we can specify the language that retrieves the descriptions of objects.

Sap Plugin - Selection Tables

  • Selected Fields: once listed in the table, select the names of the fields you want to retrieve. We have a wizard that we recovered from existing fields in the table with their descriptions.

Sap Plugin - Selection Fields

  • Where Clause: restrictions on information retrieval.When adding fields to the wizard in this section, a reference condition AutoFill taking into account the type of field data. Here we indicate the conditions that would restrict retrieval.

These are the basic parameters of Step. In addition, we identify other values, such as the number of records to retrieve or ignore, the date type conversion to char, etc.With this in mind, let's make our example. The complete graph would look like this:


Sap Plugin - Example Transformation

We performed 3 reading tables from Sap. In step SAP - Materials, MARA table read all records that meet certain conditions. Then, in step BUSCA_DESCRIP (Stream Lookup type), to search for the description of the material in the records read in step SAP - Description Material (reading from the table descriptions MATK). Then, the data stream is passed to step SAP - Description Family, where we do a lookup, but in a straightforward way for each of the flow values. You can see in the picture below as we have added conditions Where to retrieve the values ​​from table T023T (description of the family of material), the flow field, using the notation [field].

"Sap Plugin - Lookup" alt = "" src = "/ files/uploads/32image/Respinosamilla_bi/plugin-sap-lookup.jpg" />

Sap Plugin - Lookup the flow-field condition

Finally rename the field names (in step type RENOMBRA_CAMPOS Select / Rename Values) and pass the results to an Excel spreadsheet (we had we been able to perform more processing on the data or save them to a destination table BD).

Conclusions.

Proratio plugin only allows the reading tables, and always point out (extraction), although strong point we emphasize that is fully integrated with the SAP data dictionary and is very easy dive tables and fields. When compared with Talend and Sap component, this communication allows both input and output. Also allows to work with any RFC (not just as Proratio RFC_READ_TABLE), which gives us plenty of opportunities for real integration of SAP with foreign and reuse of standard components designed for specific operations (sales order creation, execution of movements posting material or processes). Even we could create our own RFC in ABAP and call for specific tasks from Talend, with all the possibilities it offers. The most basic of payment,om / products-data-integration / matrix.php "> Talend Integration Suite, has a cost per job of $ 4000, but includes technical support, access to the attendees (like SAP) and advanced features (such as shared repository for Workgroups).