One Star

SAP Deltas with Talend SAP Connectors + SAP Native BW Extractor

Hi All,
We're fairly new to SAP and have been using Talend to pull down all data from tables in our SAP hosted solution. We are having trouble figuring out how to pull just the deltas on tables that dont have change dates for needed fields. We are using the SAP connector for Talend, we also have native BW extractors (but not SAP BW Suite, didnt implement) -- can someone guide us on how to use the native extractors to get the deltas or some other way to get just the deltas when no obvious change date on a record? Our ultimate goal is to pull delta data from hosted SAP, to our in house SQL Server and then push to a hosted MicroStrategy solution.
Thanks for any guidance.
Missy
2 REPLIES
Moderator

Re: SAP Deltas with Talend SAP Connectors + SAP Native BW Extractor

Hi,
Are you looking for SAP ERP Extractors with Data Services https://jira.talendforge.org/browse/TDI-27359? Is there any more info/doc for your "SAP Deltas with Talend SAP Connectors + SAP Native BW Extractor".
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: SAP Deltas with Talend SAP Connectors + SAP Native BW Extractor

Hello,
In this tutorial we'll use our talend extension which can be downloaded from here:
http://www.talendforge.org/exchange/?eid=463&product=tos&action=view&nav=1,1,1%22
I assume that you're familiar with the followings:
-How to query tables
-How to create basic flows
-Java programming
You want to Query a table which don't have any date field about the creation / modification.
SAP has 2 tables for this:
-CDHDR
-CDPOS
Basically, the CDHDR contains the "transactions", while the CDPOS contains the actual changes.
The problem is that you can't join these tables with themselves or with other tables.
So the steps of how you can do delta loads:
Get out the changes for the time interval you have from the CDHDR, use the transaction keys you get from CDHDR to query the CDPOS table. Use the Tablename + Keyfield from the CDPOS to query the table.
You should get the following columns from CDPOS:
-TABNAME
-TABKEY
-CHNGIND
The table name, the Keys concatenated together for that particular table, and an indicator which shows that it was an Update (U) or Insert (I), if you want to go with Upserts in the target DB then you don't need this CHNGIND field. if you need historical data then you might want to use it.
So after this we have the tables and the the filters for it, all we need to do is query the table(s) and get the rows.
If you want to do this row-by-row, it will be really-really slow.
To overcome on this you need to create filters which returns more than 1 row.
Here's what I did:
See Image 1
So we get the rows from CDHDR filtered by OBJECTCLAS and Date. The result is stored in a java String List
the tJava_1 iterates on this list and generates the WHERE for the CDHDR, into another list. So we can limit the WHERE results. There may be a limit on the where clause, either because of its lenght, or it would make the query run longer than expected.
tLoop iterates on this list, and the output is then added to another String Lists based on the tables.
From these lists we can generate additional Filters to be used ON the TABLES we wanted to query.
See Image 2
My example is an upsert load.
Here are the details:
Globalvariables:
See Image 3
Example of the tJavaRows to populate the Lists:
See Image 4

Generating filter for the CDPOS table:
See Image 5
I'd like to upload the export of the job, but sadly I can't.
If you have any questions feel free to ask.
Cheers,
Balazs