Learn how you can easily extract SAP General Ledger accounting data from SAP ECC & SAP S/4HANA using KNIME & the DVW KNIME Connector for SAP
In this blog we will show you how you can easily extract SAP General Ledger Accounting Data from SAP ECC & SAP S/4HANA into KNIME with the DVW KNIME Connector for SAP.
If you would like to follow the steps described in this blog you will need:
The SAP General Ledger Document
SAP uses a document concept for business transactions in which there is Header data and Item or Segment data.
In order to get the full picture for an SAP financial transaction in KNIME you will need to extract both Header data and Item or Segment data.
The “Big Two” SAP Financial Accounting Tables
There are thousands of data tables in an SAP system. So how do you identify which tables you need to access? Unless you have developer level access, it can be difficult, but we have already done the heavy lifting for you with our blog on, the SAP Finance and Controlling (FI/CO) tables every KNIME user should know. Barring this, Google is a great source for SAP table data information.
For SAP General Ledger accounting data there are two important tables that hold the Header data and Item or Segment data for all Financial transactions in SAP. By combining data from the two tables we can effectively recreate SAP Financial Accounting documents within KNIME.
The tables are:
Both tables share common fields:
In our workflow we will use these fields in the BKPF table to find the corresponding entries in the BSEG table.
How to extract General Ledger accounting data using KNIME and the DVW KCS for KNIME Analytics Platform
So, we’ve established that we need to extract data from two SAP tables:
We are going to build a KNIME workflow that:
The DVW KNIME Connector for SAP comprises the KCS SAP Executor Node.
The KCS SAP Executor Node is used to:
The final workflow looks like this.
Let's take a look at how the workflow is defined.
On the Basic tab of the KCS SAP Executor Node configuration screen we select the required:
In our example, we are going to extract SAP Table Data from an SAP S/4HANA system, but the process is exactly the same for SAP ECC.
On the Parameters tab of the KCS SAP Executor Node configuration screen we:
In our example, we are going to extract data from SAP Table, BKPF where the Fiscal Year is, 2019 and Company Code is, 1710.
At this point in the workflow design we can execute the KCS SAP Executor Node and verify the output from SAP Table, BKPF.
Steps 2 and 3
In Step 2 we are simply connecting the output from the KCS SAP Executor Node which extracts data from SAP Table, BKPF to a KNIME Joiner Node. We will return to the KNIME Joiner Node in Step 5.
In Step 3 we need to prepare the data extracted from SAP Table, BKPF so that it can be used to dynamically extract the related entries from SAP Table, BSEG.
We won't go into a lot of detail here and suspect that a few KNIME experts will be laughing at the number of Nodes we've used to prepare the data!
In summary, the dynamic input into the next KCS SAP Designer Node and KCS SAP Executor Node combination needs to be in the following format:
The output from the Column Filter Node illustrates this.
In this step, the data extracted from SAP Table, BKPF and subsequently prepared for the dynamic selection of related entries in SAP Table, BSEG is passed from the Column Filter Node to the KCS SAP Executor Node.
On the Basic tab of the KCS SAP Executor configuration screen we select the required:
On the Parameters tab of the KCS SAP Executor configuration screen we:
In our example, we are now going to extract data from SAP Table, BSEG where the filter criteria are provided dynamically from the previous step in the workflow.
At this point in the workflow design we can execute the KCS SAP Executor Node and verify the output from SAP Table, BSEG.
We now have the required data from SAP Tables, BKPF and BSEG. The KNIME Joiner Node is used to combine the data from both tables to recreate the SAP Financial Accounting documents.
The KNIME Joiner Node is configured to join the BKPF and BSEG output on the shared keys between both tables, specifically:
At this point in the workflow design we can execute the KCS SAP Executor Node and verify the output from the KNIME Joiner Node.
By looking at the Financial Document in SAP (T-Code FB03) we can confirm that the data extracted into KNIME aligns with what is available in SAP.
In the final step of the workflow, the KNIME Joiner Node output is passed to a KNIME Excel Write Node and the final output written to an Excel file.
© COPYRIGHT 2003 - 2021 DE VILLIERS WALTON LIMITED. ALL RIGHTS RESERVED.
SAP® Business Suite, SAP® BusinessObjects™, SAP® BW, SAP® SuccessFactors are the trademarks or registered trademarks of SAP SE in Germany and in several other countries.