How to configure Talend SAP components: tSAPTableInput for SAP Application Tables
In this blog series we would like to give a short introduction to the Talend SAP Interfaces. This first publication will focus on Talend component tSAPTableInput which is designed to extract data from SAP Application Tables or Views.
Each Talend SAP Component offers a configuration dialog which allows to search by name or description for a SAP Object to be used and supports to import the meta information into Talend Repository. In case of SAP Table or View it includes an detailed overview of the field structure as well. Besides there is a preview option which gives an insight into table content for a few rows.
Figure 1: Table Import Wizard, SAP KNA1 Field Structure
Once the meta information is available into Talend Repository, a Table-Read Component can be added into Data Flow and so used like almost other database components. In the component configuration some additional customizing can be done. E.g. to limit the fields to the ones which are needed or to apply several filter conditions to reduce the results.
Such contraints must be set as key-value pairs where the value needs to surrounded by single quotes. Of course the filters can be retrieve values from Context Paramter or values provided by other Talend Components in the Data Flow. Multiple conditions are linked by AND/OR. The condition itself can be defined with typical comparison operator or set in ABAP Format (EQ, LT, LE, GT, GE).
Some aspects needs to be considered:
1. The data extraction might being cancelled in case of large amount of data.
Proposal: Partitionize the data by using filter functionality offered by tSAPTableInput and other Talend Standard Components available
The sample Talend Job shown below demonstrate the partitional load of customer master data from SAP Application Table KNA1 by using country keys which are defined in table T005. It’s divided into two parts. First the all country keys are retrieved from table T005 and temporary stored into a HashMap. In the second step there is an iteration over result set to load customer rows from KNA1 per country. Final results are written into Snowflake table.
Figure 2: Batch-Load, KNA1 Data by Country Key
The filter conditon is set dynamically per iteration and will be retrieved from tFlowToIterate Component. The configuration looks like:
Figure 3: tSAPTableInput Filter Condition
When a SAP Table contains to much rows to load the data by a single selection, the procedure described above gives a opportunity to process data yet. Of course this logic can be applied to load from SAP Application Tables having transactional data as well. Here in some cases the data is built by a header table and a position table where in the header table some timestamps are available which might be used to determine a delta.
Figure 4 shows the approach to load delta records for SAP Invoices from a conceptual point of view.
Figure 4: Concept to retrieve delta records, transactional figures – invoice line items
2. Some values in a table are saved slightly different than they are shown in a frontend like SAP GUI. That’s caused by so called conversion exits during the execution time. This behaviour becomes important when tables with currency relevant amounts are extracted.
Below a common example:
There is an Finance Document for the Leading Ledger created. For demonstration purposes the currency was set to Tunisian Dinar. This currency is defined with 3 decimal places as it's shown in the application.
Figure 5: FI-Document SAP FB03, currency TND
But at table level such values are saved with two decimal places only. So, in case of currencies having more or less decimal places, it needs to be converted to get the correct amount. Without that conversion extracted values look like it’s shown below and lead to wrong calculations.
Figure 6: Extract Fi-Document Positions by tSAPTableInput, filter on specific document with TND currency
Instead of calculating the value by own logic it’s strictly recommended to use SAP Standard Function Modules. A possible approach is to separate the dataflow in documents having a currency with two decimal places and the ones which needs to be converted. For the conversion the BAPI Interface can be used. It allows to call SAP Function Modules and can convert the values depending on the currency in the correct format
For the conversion the finance document positions (BSEG) being processed needs to be determined. That’s done by doing a lookup in the tables BKPF and TCURX. The header table BKPF contains the information of the document currency (field WAERS). In TCURX the number of decimal places per currency are specified. So, all document positions having a document currency with more or less two decimal place will be passed for conversion.
For the convertion the Standard Function Module BAPI_CURRENCY_CONV_TO_EXTERNAL is used. Because it isn’t remote enabled a custom BAPI acts as wrapper to pass the values. Such Function Modules can be used by Talend Component tSAPBapi which is also used in the Talend Job Flow shown below.
Figure 7: Talend Job Flow
For demonstration purposes the converted value (retrieved by the Function Module) is added as separate field WRBTR_CURR and shows now the correct amount like it’s displayed in the SAP GUI Frontend (see figure 8).
Figure 8: Excerpt result file
Quite often there a requirement to limit the access to table fields in advance (due legal or organizational requirements). That means, before the data will be processed by Talend. That can be simply realized by creating custom views excluding the fields the usage is forbidden for several reasons.
Next the authorization assigned to the SAP Account used by Talend will be restricted to use such views allowed. Authorization Objects S_TABU_DIS and S_TABU_NAM allows an detailed definition of the access at the table/view level. The sample shown below is based on SAP Application Table KNA1 which contains customer master data. Based on that a custom view ZVKNA1 with a subset of the orignal structure is created.
Figure 9: Custom SAP View with limited set of fields, structure is based on Application Table KNA1
The role used for the SAP Account the connection in Talend is made will be adjusted to allow read access from the custom views created. First, the view should be assigned to an authorization group forseen to be used for access by Talend (here, it’s named ZETL). Then this group is added to the authorization object S_TABU_DIS to permitt the read from this view. Besides that, some further individual access required can be managed by using S_TABU_NAM.
Figure 10: Authorization setup for access to custom view
Due the setup decribed above access to the custom view is allowed but for the SAP Application Table it's forbidden. Now, in Talend Studio the custom view ZVKNA1 is accessible but not the original it’s based on (KNA1).
wFigure 11: Original table not accessible due missing authorizations
About the author:
Markus is working as BI consultant with focus on SAP BI Product Portfolio as well as Talend at cimt ag. He’s part of the team in charge of development and support for the Talend SAP Components.
Cimt ag has been the only Talend SI Platinum and the first Cloud Expert Partner Partner in the DACH and Benelux region for more than 10 years and works with more than 200 consultants on your challenges in the BI world and beyond.
Cimt as a SAP Silver Partner is also development partner of Talend and able to provide you with full service regarding your SAP integration.