Data Import Export in S&OP On-Premise Without Any ETL
Table of Contents
- Purpose. 2
- Description. 3
SAP Note. 3
Insert Data into Staging Table. 5
Selecting Data. 6
Starting the connection to HANA.. 6
Inserting data into staging table. 7
Data from Staging to Core Table. 8
Test Result 10
Insert data for Keyfigure. 13
Import data from HANA to ECC. 13
The idea to implement this scenario came up when we had a requirement to transfer data from ECC to SAP S&OP 3.1 on HANA which was on Premises system. The standard way or we call it traditional way to achieve this requirement is to use the ETL Tool ,I came up with scenario where we connect SAP ECC directly to HANA (SAP S&OP) to speed the process.
Please Note that the ECC System mentioned here was not on HANA and Current DB is Oracle,In Order to establish connectivity between ECC and HANA
· Communication User: We need an HANA DB user with CATALOG READ or DATA ADMIN assigned.
· Network connectivity: HANA system is reachable from ABAP servers.
· HANA Client: Compatible HANA client installed on all SAP ABAP application servers. Refer: How to install HANA client.
We won’t go in details in establishing connectivity between ECC and HANA as there are lot of blogs available on how to establish secondary database Creation in SAP ABAP (SAP Note: 1597627 – SAP HANA connection).
Same concept was use to pass data from SAP ECC to HANA as mentioned in below note, instead we did not use any ETL Tool.
This note is very import and you need to understand the concept in this note before proceeding further
Staging Table:- Temporary Holds the data
Core Table:- After the stage tables are loaded, Procedure reads the data loaded into the IBP staging tables, validates the data, and if data passes the validations, it writes them to the IBP core tables, which have a structure that’s different from th core tables
This scenario is particular to S&OP 3.1 On premises system, but the concept can be applied to any system.
We created a simple master data type in S&OP, with attribute sales org ID and description.
When we create this, two table will be created in backend on HANA .
You can login to HANA Studio and check
Insert Data into Staging Table
Now as we understood the logic behind staging and core table, let us write a simple ZREPORT
to pass data from ECC to HANA staging table.
Select sales org id from table tvko and its id from tvkot using inner join as shown below.
And put it into an internal table.
Starting the connection to HANA
You can follow the SAP HELP document for the above. https://help.sap.com/abapdocu_70/en/ABAPEXEC_CONNECTION.htm
- 1. Generating Batch Id
If you have read the sap Note( Note 2045922 – S&OP on HANA On-Premise Data Integration using any ETL) and seen the above screen shot for staging table you might have noted that batch id is the mandatory field to be provided for the staging table, so as mentioned in the note you can either use sequence delivered by sap or use in HANA function.
Instead we have are generating this batch id in ECC and passing it to the core table.
Or you can query in the HANA table mentioned in the note and get the batch id.
Inserting data into staging table.
Batch = Batch id that we got in above step.
Filename = sap Hana staging table name in this case SOPMD_STAG_IN3SALESORG
Data is inserted into HANA staging table now next step would be to move data from HANA staging to core table.
Data from Staging to Core Table
- 1. Please read the note ( Note 2045922 – S&OP on HANA On-Premise Data Integration using any ETL) also read the help document on how to consume SAP HANA Data procedure from ABAP https://help.sap.com/abapdocu_740/en/abendatabase_procedure_proxy.htm
Load below data from ECC into HANA
Data LOADED into Hana Staging table
Log in into S&OP Web UI and goto Data Integration TAB ,You can see after record are sucessfully inserted into staging table , a SAP HANA stored procedure has scheduled a Job to load data into core table.To view data loaded into core table you can loging into Excel UI and check the Master data.
Log in into Excel UI for respective planning area and
Goto-Dowload-choose Master data.
Insert data for Keyfigure
Keyfigure in S&OP and IBP term is nothing but transaction data from ECC that will be aggregated to planning level i.e. Product and Location or can be anything as configured by IBP consultant.
Keyfigure can be –Open Purchase Order, Open Purchase Req , Actual Sales, Inventory.
Same concept is applied for inserting data into Keyfigure.
As in case of master data it will be different table for different master data , such as product master ,customer master etc….,where as in case of Keyfigure a single table will be created for planning area and each Keyfigure will be a separate column.
Import data from HANA to ECC
Below is the code to pull the data back from HANA into ECC
In below code we tried to build a dashboard in ECC to view the status of the job scheduled through HANA Stored Procedure.