Skip to Content
avatar image
Former Member

Steps to create Generic datasource with delta using two date fields

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Feb 21, 2017 at 06:54 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Feb 28, 2017 at 03:32 PM

    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

    Add comment
    10|10000 characters needed characters exceeded