ALTERYX CONNECTORS

ALTERYX CONNECTOR FOR SAP
ALTERYX CONNECTOR FOR SAP CX
ALTERYX CONNECTOR FOR BUSINESSOBJECTS
ALTERYX CONNECTOR FOR SUCCESSFACTORS
ALTERYX CONNECTOR FOR SAP RPA
SAP METADATA CONNECTOR FOR ALTERYX CONNECT

KNIME CONNECTORS

KNIME CONNECTOR FOR SAP
KNIME CONNECTOR FOR SUCCESSFACTORS

TABLEAU CONNECTORS

TABLEAU CONNECTOR FOR SAP

POWER BI CONNECTORS

POWER BI CONNECTOR FOR SAP
FREE TRIAL
DVW Analytics
  • PRODUCTS
    • ALTERYX CONNECTORS >
      • ALTERYX CONNECTOR FOR SAP
      • ALTERYX RPA TOOLS
      • ALTERYX CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)
      • ALTERYX CONNECTOR FOR SUCCESSFACTORS
      • ALTERYX CONNECTOR FOR BUSINESSOBJECTS
    • KNIME CONNECTORS >
      • KNIME CONNECTOR FOR SAP
      • KNIME CONNECTOR FOR SUCCESSFACTORS
    • POWER BI CONNECTORS >
      • POWER BI CONNECTOR FOR SAP
    • TABLEAU CONNECTORS >
      • TABLEAU CONNECTOR FOR SAP
    • DATAIKU CONNECTORS
    • SNOWFLAKE CONNECTORS
  • SERVICES
  • DEMOS
    • ALTERYX CONNECTOR DEMOS
    • KNIME CONNECTOR DEMOS
    • POWER BI CONNECTOR DEMOS
    • TABLEAU CONNECTOR DEMOS
    • SNOWFLAKE CONNECTOR DEMOS
  • FREE TRIAL
  • PRICING
    • ALTERYX CONNECTOR PRICING
    • KNIME CONNECTOR PRICING
    • POWER BI CONNECTOR PRICING
    • TABLEAU CONNECTOR PRICING
    • DATAIKU CONNECTOR PRICING
    • SNOWFLAKE CONNECTOR PRICING
  • RESOURCES
    • WEBINARS >
      • ALTERYX CONNECTOR WEBINARS
      • KNIME CONNECTOR WEBINARS
      • POWER BI CONNECTOR WEBINARS
      • TABLEAU CONNECTOR WEBINARS
      • SNOWFLAKE CONNECTOR WEBINARS
    • EVENTS >
      • ALTERYX EVENTS
      • KNIME EVENTS
    • SOFTWARE DOWNLOADS
    • INSTALLATION GUIDES
    • TUTORIALS
    • SAMPLE WORKFLOWS
    • FAQ
    • LICENSE
    • RESELLER RESOURCES
  • HELP CENTRE
  • BLOG
  • ABOUT
    • ABOUT US
    • CONTACT US
    • CUSTOMERS
    • PARTNERS
    • RESELLERS

BLOG

Extract Sales & Purchasing Tax Data from SAP ECC & SAP S/4HANA using KNIME & the DVW KNIME Connector for SAP

22/12/2020

 
Picture
In this blog we will demonstrate how you can easily extract Sales and Purchasing Tax data (Output Tax and Input Tax) from an SAP ECC or SAP S/4HANA system directly into KNIME.
If you would like to follow the steps described in this blog you will need:
  • KNIME Analytics Platform - Download here
  • DVW KCS for KNIME Analytics Platform - Request a trial here
  • Access to an SAP ECC or SAP S/4HANA system
​If you are more interested in using your SAP G/L accounting data in KNIME, take a look at, "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"

Identifying the required SAP Tables

For our purposes there are two tables that hold the data we need:
  • Accounting Document Header – BKPF
  • Tax Data Document Segment - BSET

Both tables share common fields:
  • Company Code - BUKRS
  • Accounting Document Number - BELNR
  • Fiscal Year – GJAHR

In our workflow we will use these fields in the BKPF table to find the corresponding entries in the BSET table.

You may also find our blog, the most important SAP Finance and Controlling (FI/CO) tables for KNIME users, helpful. 

How to extract Sales and Purchasing Tax data using KNIME and the DVW KCS for KNIME Analytics Platform

We’ve established that we need to extract data from two SAP tables:
  • Accounting Document Header - BKPF
  • Tax Data Document Segment - BSET

We are going to build a KNIME workflow that:
  • Extracts the required data from the BKPF table
  • Dynamically extracts the related entries from the BSET table
  • Joins the output from both tables to reconstruct the Sales and Purchasing Tax Line Items

​The DVW KNIME Connector for SAP comprises two nodes which work together to enable the extract of SAP data:
  • KCS SAP Designer Node
  • KCS SAP Executor Node

The KCS SAP Designer Node is used to:
  • Identify the SAP system to be used
  • Select the required DVW KNIME Connector for SAP capability e.g. SAP Table Data Read, SAP BW Read etc.
  • Search for and select the specific SAP Table, SAP BW Query, SAP BW InfoProvider etc. to be used in the workflow
  • Configure the extract settings for the selected SAP object e.g. select the fields, apply filters etc.

The KCS SAP Executor Node is used to execute the SAP data extract based on the settings defined in the KCS SAP Designer Node.

The final workflow looks like this. 
Picture
Let's take a look at how the workflow is defined.

Step 1

Picture
On the Basic tab of the KCS SAP Designer configuration screen we select the required:
  • SAP system 
  • KCS SAP capability (SAP Tool)

In our example, we are going to extract SAP Table Data from an SAP ECC system, but the process is exactly the same for SAP S/4HANA.
Picture
On the Parameters tab of the KCS SAP Designer configuration screen we:
  • Search for and select the required SAP Table
  • Select the fields to be extracted from the SAP Table
  • Apply filters to the SAP Table fields

In our example, we are going to extract data from SAP Table, BKPF where the Fiscal Year is, 2002,  Company Code is, 1000 and the Posting Date in the Document is between 01 January 2002 and 31 December 2002.
Picture
The KCS SAP Designer Node is connected to the KCS SAP Executor Node. 

Once connected, we just need to verify that XCS Transmission Object is selected in the KCS SAP Executor configuration.
Picture
At this point in the workflow design we can execute the KCS SAP Executor Node and verify the output from SAP Table, BKPF.
Picture

Steps 2 and 3

Picture
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, BSET.

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:
  • Name
  • Selection
  • Operator
  • LowKey
  • HighKey
  • Split 

The output from the Column Filter Node illustrates this.​
Picture

Step 4

Picture
In this step, the data extracted from SAP Table, BKPF and subsequently prepared for the dynamic selection of related entries in SAP Table, BSET is passed from the Column Filter Node to both the KCS SAP Designer Node AND the KCS SAP Executor Node.

On the Basic tab of the KCS SAP Designer configuration screen we select the required:
  • SAP system 
  • KCS SAP capability (SAP Tool)
Picture
On the Parameters tab of the KCS SAP Designer configuration screen we:
  • Search for and select the required SAP Table
  • Select the fields to be extracted from the SAP Table
  • Apply filters to the SAP Table fields

In our example, we are now going to extract data from SAP Table, BSET where the filter criteria are provided dynamically from the previous step in the workflow.
Picture
The KCS SAP Designer Node is connected to the KCS SAP Executor Node. 

Once again, we need to verify that XCS Transmission Object is selected in the KCS SAP Executor configuration.
Picture
At this point in the workflow design we can execute the KCS SAP Executor Node and verify the output from SAP Table, BSET.
Picture

Step 5

Picture
We now have the required data from SAP Tables, BKPF and BSET. 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 BSET output on the shared keys between both tables, specifically:
  • Company Code - BUKRS
  • Accounting Document Number - BELNR
  • Fiscal Year - GJAHR
Picture
At this point in the workflow design we can execute the KCS SAP Executor Node and verify the output from the KNIME Joiner Node.
Picture

Step 6

Picture
The data is passed from the KNIME Joiner Node to a pair of KNIME Rule-based Row Filter Nodes which are used to prepare the final output:
  • Input Tax Line Items have a Transaction Key (KTOSL)  value of "VST"
  • Output Tax Lines Items have a Transaction Key (KTOSL) value of "MWS"

The first KNIME Rule-based Row Filter Node filters all the output records where the Transaction Key is "VST". ​
Picture
The output from the first KNIME Rule-based Row Filter Node can be verified to confirm that only Input Tax Line Items are included.
Picture
The second KNIME Rule-based Row Filter Node filters all the output records where the Transaction Key is "MWS". ​​
Picture
The output from the second KNIME Rule-based Row Filter Node can be verified to confirm that only Output Tax Line Items are included.
Picture
GET A FREE TRIAL TODAY

Comments are closed.

    RSS Feed

    Categories

    All
    ACS
    ACS BOP
    ACS CX
    ACS Metadata
    ACS SF
    Alteryx
    Alteryx Connector For BusinessObjects
    Alteryx Connector For SAP
    Alteryx Connector For SuccessFactors
    Alteryx Gallery
    Alteryx Inspire
    Alteryx RPA Tools
    Alteryx Server
    APA
    Audit
    BusinessObjects
    Data Migration
    DVW Integration Platform
    Finance
    KCS
    KNIME
    KNIME Connector For SAP
    Microsoft Azure
    Office Of Finance
    On Demand Webinar
    Power BI
    Power BI Connector For SAP
    Predictive Analytics
    Process Automation
    Professional Services
    Qlik
    Read Tools
    RPA
    Salesforce
    Salesforce Wave Analytics
    Sample Workflow
    SAP Business Suite
    SAP BW
    SAP C4C
    SAP ECC
    SAP ERP
    SAP HANA
    SAP S/4HANA
    SAP Tables
    SAP T Codes
    SAP T-Codes
    SAP Transaction Codes
    Snowflake
    Snowflake Connector For SAP
    SuccessFactors
    Tableau
    Tableau Connector For SAP
    Tableau Desktop
    Tableau Online
    Tableau Prep
    Tableau Server
    Tax
    TCS
    ThoughtSpot
    Tool: BOP Universe
    Tool: BOP Webi
    Tool: HTML RPA
    Tool: SAP BAPI Read
    Tool: SAP BAPI Write
    Tool: SAP BW Write
    Tool: SAP Document Read
    Tool: SAP IDoc
    Tool: SAP Query
    Tool: SAP Report
    Tool: SAP RPA
    Tool: SAP Spool Read
    Tool: SAP Table Data
    Tool: SuccessFactors Read
    Tool: SuccessFactors Write
    Top 10 Blog
    Webinar
    Write Tools

    Archives

    March 2023
    February 2023
    January 2023
    October 2022
    June 2022
    May 2022
    April 2022
    March 2022
    February 2022
    December 2021
    November 2021
    October 2021
    May 2021
    April 2021
    December 2020
    October 2020
    July 2020
    June 2020
    May 2020
    February 2020
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    September 2018
    August 2018
    June 2018
    April 2018
    February 2018
    July 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    November 2015

PRODUCTS

​ALTERYX CONNECTORS
​KNIME CONNECTORS
​POWER BI CONNECTORS
TABLEAU CONNECTORS
DATAIKU CONNECTORS
SNOWFLAKE CONNECTORS

FREE TRIAL

​ALTERYX CONNECTORS
​FREE TRIAL OF ALTERYX CONNECTOR FOR SAP
​FREE TRIAL OF ALTERYX RPA TOOLS
​FREE TRIAL OF ALTERYX CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)​
FREE TRIAL OF ​ALTERYX CONNECTOR FOR SAP SUCCESSFACTORS
FREE TRIAL OF ALTERYX CONNECTOR FOR SAP INTEGRATED BUSINESS PLANNING (IBP)​
FREE TRIAL OF ​ALTERYX CONNECTOR FOR SAP BUSINESSOBJECTS

​KNIME CONNECTORS
​FREE TRIAL OF KNIME CONNECTOR FOR SAP
FREE TRIAL OF KNIME CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)
FREE TRIAL OF ​KNIME CONNECTOR FOR SAP SUCCESSFACTORS
FREE TRIAL OF KNIME CONNECTOR FOR SAP INTEGRATED BUSINESS PLANNING (IBP)

POWER BI CONNECTORS
FREE TRIAL OF POWER BI CONNECTOR FOR SAP
​FREE TRIAL OF POWER BI CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)​
FREE TRIAL OF POWER BI CONNECTOR FOR SAP SUCCESSFACTORS
FREE TRIAL OF POWER BI CONNECTOR FOR SAP INTEGRATED BUSINESS PLANNING (IBP)

TABLEAU CONNECTORS
FREE TRIAL OF TABLEAU CONNECTOR FOR SAP
​​FREE TRIAL OF TABLEAU CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)​
FREE TRIAL OF TABLEAU CONNECTOR FOR SAP SUCCESSFACTORS
FREE TRIAL OF TABLEAU CONNECTOR FOR SAP INTEGRATED BUSINESS PLANNING (IBP)

DATAIKU CONNECTORS
FREE TRIAL OF DATAIKU CONNECTOR FOR SAP
FREE TRIAL OF DATAIKU CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)
FREE TRIAL OF DATAIKU CONNECTOR FOR SAP SUCCESSFACTORS
FREE TRIAL OF DATAIKU CONNECTOR FOR SAP INTEGRATED BUSINESS PLANNING (IBP)


SNOWFLAKE CONNECTORS
FREE TRIAL OF SNOWFLAKE CONNECTOR FOR SAP
FREE TRIAL OF SNOWFLAKE CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)

FREE TRIAL OF SNOWFLAKE CONNECTOR FOR SAP SUCCESSFACTORS
FREE TRIAL OF SNOWFLAKE CONNECTOR FOR SAP INTEGRATED BUSINESS PLANNING (IBP)

SERVICES

PRICING

DEMOS

BLOG

HELP CENTRE

ABOUT US

CONTACT US

The DVW Integration Platform from DVW Analytics, brings the power of SAP to users of Alteryx, KNIME, Power BI, Tableau and other global software leaders for Analytics & Process Automation

© COPYRIGHT 2003 - 2023 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.
PRIVACY POLICY
  • PRODUCTS
    • ALTERYX CONNECTORS >
      • ALTERYX CONNECTOR FOR SAP
      • ALTERYX RPA TOOLS
      • ALTERYX CONNECTOR FOR SAP CUSTOMER EXPERIENCE (CX)
      • ALTERYX CONNECTOR FOR SUCCESSFACTORS
      • ALTERYX CONNECTOR FOR BUSINESSOBJECTS
    • KNIME CONNECTORS >
      • KNIME CONNECTOR FOR SAP
      • KNIME CONNECTOR FOR SUCCESSFACTORS
    • POWER BI CONNECTORS >
      • POWER BI CONNECTOR FOR SAP
    • TABLEAU CONNECTORS >
      • TABLEAU CONNECTOR FOR SAP
    • DATAIKU CONNECTORS
    • SNOWFLAKE CONNECTORS
  • SERVICES
  • DEMOS
    • ALTERYX CONNECTOR DEMOS
    • KNIME CONNECTOR DEMOS
    • POWER BI CONNECTOR DEMOS
    • TABLEAU CONNECTOR DEMOS
    • SNOWFLAKE CONNECTOR DEMOS
  • FREE TRIAL
  • PRICING
    • ALTERYX CONNECTOR PRICING
    • KNIME CONNECTOR PRICING
    • POWER BI CONNECTOR PRICING
    • TABLEAU CONNECTOR PRICING
    • DATAIKU CONNECTOR PRICING
    • SNOWFLAKE CONNECTOR PRICING
  • RESOURCES
    • WEBINARS >
      • ALTERYX CONNECTOR WEBINARS
      • KNIME CONNECTOR WEBINARS
      • POWER BI CONNECTOR WEBINARS
      • TABLEAU CONNECTOR WEBINARS
      • SNOWFLAKE CONNECTOR WEBINARS
    • EVENTS >
      • ALTERYX EVENTS
      • KNIME EVENTS
    • SOFTWARE DOWNLOADS
    • INSTALLATION GUIDES
    • TUTORIALS
    • SAMPLE WORKFLOWS
    • FAQ
    • LICENSE
    • RESELLER RESOURCES
  • HELP CENTRE
  • BLOG
  • ABOUT
    • ABOUT US
    • CONTACT US
    • CUSTOMERS
    • PARTNERS
    • RESELLERS