Steps to create Generic datasource with delta using two date fields

[Former Member]

Hello,

We have a requirement to build reports based on custom tables.For this, we created a view on 2 custom tables with the required fields from both tables.

The problem we are facing is, it has two date fields (created on & changed on). Based on this 2 fields we need to fetch delta records.

The logic how data stored in tables is:

- for new record, the created on field will get value and changed on will be blank

- for updated record, the changed on field will get updated

This requirement cannot be fulfilled using a view, therefore we need to create custom extractor and FM for creating DS.

Please help us, on how to create the extractor and FM. Also on which field we need to define the generic delta.

[john_hawk]

Hi Ashish,

I can't give a function module, but I can suggest as solution - create a "faux" or false delta.

This solution assumes that your source table and landing DSO both us the same key.

1) Make Creation Date, Changed Date and the source table key as Selection item.

2) Create two infoPackages

a) Use an Offset Variable from today's date - 7 (or however long needed to cover time between extracts with confidence) based on Creation Date

b) Use an Offset Variable from today's date - 7 (or however long needed to cover time between extracts with confidence) based on Change Date

3) Create a DTP. Insure that you use Delta Mode, Get Request by Request, and Handle Duplicates are selected.

4) Schedule sequentially in a process chain Create Date InfoPackage, Change Date InfoPackage, DTP. (you only need one) Do NOT try to schedule concurrently/in parallel - you will cross lock to delay or abort run.

If the custom source table is large, consider adding an index to Creation Date and Changed Date fields.

Below is a link to a good "How to"

Generic Extraction

https://wiki.scn.sap.com/wiki/display/BI/Generic+Extraction

Good luck, and be sure to record your final answer.

John Hawk

[Former Member]

Hi John,

Do I need to create an infopackage with full load and then the two other with offset variables for delta loading to PSA.

Regards,

Ashish

[Former Member]

Hello John,

Setting the offset as 7 will not serve the purpose, as there are chances that older records might get updated then how to pick those records.

Regards,

Ashish

[john_hawk]

Hi Ashish,

The InfoPackages are "full" with selection on Create or Changed date. BOTH will be run each day.

The solution assumes that any change to an attribute will result in an update to the Changed date, but this is not necessarily true. Be sure to test! If changes to attributes don't always update the changed , you may need to perform a full load periodically. Consider using a larger offset -365 (or one year) for catch up loads.

The goal should be to minimize the the number of records extracted form the source. Apply your selections to the InfoPackage, not the DTP.

Let us know if this selection works for you.

John Hawk