Learn how you can easily extract SAP General Ledger accounting data from SAP ECC & SAP S/4HANA using Alteryx & the DVW Alteryx 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 Alteryx with the DVW Alteryx Connector for SAP.
If you would like to follow the steps described in this blog you will need:
If you have access to Alteryx Gallery and would like to download the workflow presented in the blog, click here.
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 Alteryx 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 most important SAP Finance and Controlling (FI/CO) tables for Alteryx users. 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 Alteryx.
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 Alteryx and the DVW ACS for Alteryx Designer
So, we’ve established that we need to extract data from two SAP tables:
To achieve this, we are going to use 2 workflows, a “Caller” workflow and a “Callee” workflow.
The “Caller” workflow will be used to extract data from the Header table, BKPF.
Key fields from BKPF will be passed to the “Callee” workflow to support the extract of data from the Segment table, BSEG.
The “Callee” workflow will pass the data extracted from BSEG back to the “Caller” workflow, where it will be combined with the data extracted from BKPF to provide us with a complete picture of a financial transaction.
Let’s take a look at how the two workflows are defined.
The SAP Logon tool passes your SAP system details & user credentials to the ACS for Alteryx Designer tools that connect to it.
In this workflow, the details are passed to the SAP Table Data tool.
SAP table, BKPF (Accounting Document Header) is selected in the SAP Table Data tool along with the fields that will (a) be used to select BSEG (Accounting Document Segment) data and (b) included in the workflow output
This branch of the workflow passes the data extracted from the BKPF table to the Join tool. This step ensures that the data extracted from the BKPF table will be incorporated into the final workflow output.
The Filter tool controls which fields from table BKPF will be used to select data from table BSEG. In this workflow the following BKPF fields are used to select the required data from BSEG:
The Filter tool also controls the number of BKPF records that will be passed to the “Callee” workflow to select BSEG data, at a time. In our example we will pass records in batches of 8,000 records.
The outputs from the Filter tool are passed to a macro which handles the extract of data from table BSEG. The macro calls the “Callee” workflow.
This workflow uses “GroupId” as the Control Parameter.
The Macro Input tool macro receives data from SAP table BKPF as inputs from the “Caller” workflow.
In order to pass the BKPF table data correctly to the SAP Table Data tool a Text Input Template is defined.
BKPF table data is passed as a dynamic input selection into the SAP Table Data tool.
SAP table, BSEG (Accounting Document Segment) is selected in the SAP Table Data tool along with the fields that will be included in the workflow output.
The Macro Output tools enables the data output from the SAP Table Data tool to be passed back to the "Caller" workflow.
The results of the “Callee” workflow can be viewed by clicking the output port of the Macro.
The data extracted from table BKPF and BSEG are joined using the Join tool.
The join is created on common fields:
This branch of the workflow displays the combined BKPF/BSEG output using SAP table Technical Field Names as column headers.
The branch of the workflow enables the display of SAP table Field Descriptions rather than Technical Names as column headers in the output.
The Select tool is used to define which fields will be displayed in the output.
The Field Info tool provides the Field Descriptions for the selected fields.
The Dynamic Rename tool is used to ensure that the Description of the field rather than the Name of the field is used in the column headers.
The Browse tools shows the final combined BKPF/BSEG output with the descriptive column headers.
ALTERYX CONNECTOR FOR SAP
ALTERYX CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)
ALTERYX CONNECTOR FOR BUSINESSOBJECTS
ALTERYX CONNECTOR FOR SUCCESSFACTORS
ALTERYX CONNECTOR FOR SAP IBP (COMING SOON)
SAP METADATA CONNECTOR FOR ALTERYX CONNECT
KNIME CONNECTOR FOR SAP
EXCEL CONNECTOR FOR SAP
POWER BI CONNECTOR FOR SAP
TABLEAU CONNECTOR FOR SAP
FREE TRIAL OF ALTERYX CONNECTOR FOR SAP
FREE TRIAL OF ALTERYX CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)
FREE TRIAL OF ALTERYX CONNECTOR FOR BUSINESSOBJECTS
FREE TRIAL OF ALTERYX CONNECTOR FOR SUCCESSFACTORS
FREE TRIAL OF ALTERYX CONNECTOR FOR SAP IBP (COMING SOON)
FREE TRIAL OF KNIME CONNECTOR FOR SAP
FREE TRIAL OF EXCEL CONNECTOR FOR SAP
FREE TRIAL OF POWER BI CONNECTOR FOR SAP
FREE TRIAL OF TABLEAU CONNECTOR FOR SAP
DVW Analytics, from De Villiers Walton, is dedicated to bringing the power of SAP to users of Alteryx, KNIME, Excel, Power BI, Tableau & other global leaders in BI and analytics.
© 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.