S4 TABLE MERGE

Rohit Kulkarni 676

Hello Team,

We have implemented the S4 Tables data into sql server tables .As of now we are truncating the data in SQL server and we are doing full load data from S4 to SQL Server. At the later stage the data in S4 will increase in millions of records at that stage the loading of Full Data into SQL server will be time consuming. So is there any method we can get only latest data from S4 and load only latest data into SQL server Tables. So that we can reduce the time consuming of the pipeline running.

Please advise

HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2022-04-21T21:14:51.403+00:00

<< Reply 1/2>>
Hello @Rohit Kulkarni ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is hyow to do an incrementalk load vs a full load , please do let us know if its not accurate.
When you refer S4 , I am assuming that the S/4 HANA please correct me if I am wrong .
<<contd below >>
HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2022-04-21T21:15:41.2+00:00
<< Reply 2/2>>
Looking that document I see that ADF do support query for SAP . https://learn.microsoft.com/en-us/azure/data-factory/connector-sap-hana?tabs=data-factory#sap-hana-as source , so i suggest this

Read the sink ( SQLin this case ) read the primary key and get the max value .You can get this using a Lookup activity

Create a query using a dynamic content with a filter > then the max value which you got from Step 1 . We will use this in the Source of copy activity .

Thanks
Himanshu
Rohit Kulkarni 676 Reputation points

2022-04-22T15:26:14.11+00:00

As of now we are using the same technic we are loading the data from S4 Tables to SQL server Tables

Example :

S4 TABLE: 21-04-2022 : 10 Records
SQL SERVER :21-04-2022 : 10 Records is loaded

S4 TABLE: 22-04-2022 : 4 Records
SQL SERVER :22-04-2022 : In Current scenario I am truncating the current table data and loading again then it will load : 14 records

But i require only

21-04-2022 :data(10 rows) will be present in the table.
22-04-2022 :Need to load only todays data ( 4 rows ). Without truncate the previous date data

Total : 10+ 4 : 14 rows

Day to Day need to upload only the current days data into the tables without truncate the previous date data

Thanks

RK
HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2022-05-03T21:46:46.5+00:00

Hello @Rohit Kulkarni ,
Apologies for the delay in reply from our side .

S4 TABLE: 21-04-2022 : 10 Records
SQL SERVER :21-04-2022 : 10 Records is loaded

S4 TABLE: 22-04-2022 : 4 Records
SQL SERVER :22-04-2022 : In Current scenario I am truncating the current table data and loading again then it will load : 14 records

But i require only

21-04-2022 :data(10 rows) will be present in the table.
22-04-2022 :Need to load only todays data ( 4 rows ). Without truncate the previous date data

Thanks for the putting the timeline for clarity as this helps a lot . The above suggested approach will sure work in this . Just read the max primary_key id on the second from the sink and query all the data from the source after that .

Total : 10+ 4 : 14 rows

Day to Day need to upload only the current days data into the tables without truncate the previous date data

With the logic which I suggested you will have to not truncate the table.

Thanks
Himanshu
Rohit Kulkarni 676 Reputation points

2022-05-05T17:11:43.03+00:00

But We are using the source as S4..We can't get Max value from the S4 tables
Rohit Kulkarni 676 Reputation points

2022-05-06T04:10:27.047+00:00

Please can you explain with screenshot.It will be helpfull.