Universal Delta Solution for SAP extractions and reports

Its very common for SAP consultants (BW consultants especially) to be stymied by the issue with delta extraction. Some of the concerns we commonly share are:

1. No timestamp, no date or numeric pointer available.
2. The extractor logic itself is so complex that we can only do a (current date – 1) and execute the delta update everyday. That”s not true delta anyways.
3. More often than not we end up doing a full update bringing in millions of records to BW and then applying delta logic and if you are on ODP Framework then you must have definitely encountered the infamous “Ping Time Exceeded Error”. More often than not its observed where you extract heavy loads from the source which takes time to transfer.
4. Re-running those heavy full loads will definitely hit your SLAs and yet again you get that ping time error. List is endless, I know, but lets see if we have a solution to that.

The pain point with delta related problems and Ping Time Exceeded Error can mostly be attributed to huge loads(Full Loads) and corresponding execution time taken for extraction.

The solution is simple convert Full to Delta.

I encountered a similar dead-end on a Full Load extractor and started off to create a delta but the issue was that this extractor was view based extractor on SAP Tables and extracting 30M+ records with no scope for timestamp or numeric pointer and DELTA Only via FULL Upload (ODS or InfoPackage Selection).

OBJECTIVE: To detail the logic to achieve delta in the scenario wherein no timestamp/date/numeric pointer is there in-scope and the load is only full based on a view here.

Currently, we are on HANA, i.e. we have BSoH available in our system and that is why I went on to convert this particular extractor to Delta with a very simple logic and with pushdown to HANA achieved great results. The algorithm for the same is as follows:

1. Extract the data from the extractor and save it in an image table i.e. a table which will hold entire data for the extractor output(yes I passed 30M+ records into this table). [This table serves you as the last image of extraction hence its updated everytime I extract the data]
2. After entering some changes into the system you use the magic logic of delta via an FM – which is pretty simple in fact and goes like this: (A-B) UNION ALL (B-A) in SQL where A is the extractor output and B is the image table, please read ‘–’ as minus which is a keyword in SQL. The first part of the equation brings you all the incremental delta from the system or extractor and the next part of the equation i.e. B MINUS A gives you all the changes and deletion required from the system and this delta is meant to add and delete the records in the target.
3. Post execution of this system you need to execute the update program which truncates and updates the image table with the current output of the extractor and you are ready for another delta extraction there.

With HANA, I achieved Pushdown and heavy improvement on performance without having a great impact on memory footprint compared to original extractor using SQL classes but that doesn’t mean this logic can’t be applied to non-HANA environments to achieve Delta.

Memory management is a part of coding that we did when we used to work on languages like C so why not optimize the extractor program to work with the key of the extractor to achieve the MINUS function and the requisite output.

Apparent Pros/Cons:

DRAWBACKS:

1. This will still work as Full extractor delivering Delta Load however in case you need Full load, all you need to do is truncate the image table and execute the extractor normally we don’t have access for this in production environment.
2. Memory consumption and Redundancy in Operational System, its true we are truncating and storing the image in ECC itself every time we run the load and that is why the consultants and architects should weigh the available options here and then use it.
3. Safety Interval concept doesn’t apply here, so what do you do to get the missed out records ?You run the extractor again 🙂

FURTHER MORE:

1. One can also pushdown the logic straight to CDS views and use that to extract data to achieve maximum HANA leverage.
2. If the records extracted are not huge but the extractor logic is complex and SDA is available then the last image can be obtained directly from BW system and you can pull in exact delta records from source system and maintain the latest image in BW with overwrite/Full update logic.Come to think of it this logic can be system independent too 🙂

Step By Step Flow:

1. Create an FM with the following logic for encapsulating the Delta, I have assumed an extractor ZCLASS_OBJECT which will yield only delta records; The changed and added records will come with flag ‘ A’ and the ones to be deleted with ‘D’. Program construct:CONCATENATE ‘(SELECT MANDT, CUOBJ, CINT, OBTAB, OBJEK,KLART, CLASS, ”A” as DFLAG from ((select * from “SAPECC”.”ZCLASS_OBJECT”) minus (select * from “SAPECC”.”YCLASS_OBJECT”)))’
       ‘UNION ALL (SELECT MANDT, CUOBJ, CINT, OBTAB, OBJEK,KLART, CLASS, ”D” as DFLAG from ((select * from “SAPECC”.”YCLASS_OBJECT”) minus (select * from “SAPECC”.”ZCLASS_OBJECT”)))’ ‘;’ INTO query_ddl.     TRY.
           lr_psql = NEW #( query_ddl ) .
           lr_result = lr_psql->execute_query( query_ddl ).
           CHECK sy-subrc = 0.
           lr_result->set_param_table( REF #( it_zclsobj ) ).
           lr_result->next_package( ).
           lr_result->clear_parameters( ).
           lr_result->close( ).
           lr_psql->close( ).       CATCH cx_sql_exception INTO lref_sql_exception.
           DATA(ev_message1) = lref_sql_exception->sql_message.
           WRITE:/ ev_message1.
       ENDTRY.     e_t_data[] = it_zclsobj[].
2. Create another program or include in the same program the code to truncate the image table(here YCLASS_OBJECT) and then update with latest output.TRY.
   CONCATENATE ‘TRUNCATE TABLE “SAPECC”.”YCLASS_OBJECT”‘ ‘;’ INTO query_truncate SEPARATED BY SPACE.
   CONCATENATE ‘UPSERT “SAPECC”.”YCLASS_OBJECT” select * from “SAPECC”.”ZCLASS_OBJECT”‘ ‘;’ INTO query_dml SEPARATED BY SPACE.lr_psql = NEW #( query_TRUNCATE ) .
   WRITE:/ lr_psql->execute_update( query_DML ).
   CHECK sy-subrc = 0.
   lr_psql->close( ).lr_psql = NEW #( query_dml ) .
   WRITE:/ lr_psql->execute_update( query_DML ).
   CHECK sy-subrc = 0.CATCH cx_sql_exception INTO lref_sql_exception.
   DATA(ev_message1) = lref_sql_exception->sql_message.
   WRITE:/ ev_message1.
   ENDTRY.
3. Maintain the structure with same format as the extractor and add a flag for delta record handling.
4. If you need, you can write/execute the code to pushdown the table into the drive using the unload SQL option thus offloading the image table from RAM. The system will call it or load it into RAM automatically when you execute the extractor program next time. All the steps can be taken care of in a single FM but I have them currently separated them for demo purposes.
5. Finally the output you get is with A and D flags for update into target in BW.

LOGIC CHECK: In the figure above, you can see the records with DFLAG as ‘A’ are resultant of ‘A-B’ implying the latest image increments and changes to be added for the delta and the DFLAG as ‘D’ signify the records which need to be deleted from the previous image i.e ‘B-A’ to achieve the overall final delta image for the extractor.

In my case where I have ECC on HANA the 30M+ record handling took 48 seconds to generate the Delta and truncate and update to image table took less than 3 mins which is any day far better than the 1260+ seconds or 21+mins required for full load extraction in the same system on SAPI(ABAP).The memory consumed for this operation and for the time period it held the data is again more efficient comparatively.

In SAP, we have various different kinds of delta methods to meet different kinds of delta requirements and all of which have not been met in this scenario. This document is meant to serve a very simple purpose that any Full Load can be converted to Delta at extract structure level itself even if no option of delta is available. The intent is to share the idea/logic and not just the exact code for achieving delta in my case there is only addition and deletion which needs to be captured and this code serves its purpose well it can be further modified to meet your requirements but do weigh in the resource consumed and memory handling aspects before using this mode.

In case you have the requirement to track each and every change one may want to look on the option of Change Pointers or use Trigger Based update mechanism to track changes directly into the Shadow Tables of the original tables – Again its up to the consultants and architects to think it through for allowing trigger on application tables is never a good idea however necessity is the mother of invention and I am sure somewhere someone will have a great thought to cater to that too.

My PoC scope was limited and with so many options from SAP for Delta available we will not always fall short on Delta Extraction however any inputs on this document is always welcome.

Thanks,

Abhishek