Data Import Export in S&OP On-Premise Without Any ETL

[lalith_bundel1]

Table of Contents

1. Purpose. 2

Abstract 2

1. Description. 3

SAP Note. 3

Architecture 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

Purpose

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.

Abstract

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

Prerequisite:

·       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).

Description

SAP Note

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. 

Note 2045922 - S&OP on HANA On-Premise Data Integration using any ETL

This note is very import and you need to understand the concept in this note before proceeding further

Architecture              

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

Catalog->SAPSOPG->Tables

Staging Table

Core Table

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.

Selecting Data

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. 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. 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

     

Test Result

Load below data from ECC into HANA

Extractor ZREPORT

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.