How to Create Delta Extraction Based ABAP CDS Views

[xarodoplu]

It has been a serious issue to use 2LIS* delta extractors for a long time in SAP BW.  To fill a setup table was a nightmare for many consultants. SAP provided 2 methods for delta extraction via ABAP CDS. In this blog I’ll explain how to use these methods in sales data extraction step-by-step in parallel. We have 2 alternatives to use delta functionality.

Alternative – 1: Delta extraction with delta queue and CDS annotation

Alternative – 2: Delta extraction without delta queue and CDS annotation

Step 1: Create CDS view like provided below. You’ll see annotations for delta extraction in Alternative – 1.

• To define which field will trigger delta, you need to use annotation @analytics.dataExtraction.delta.byElement.name. LastChangedAt is the field which triggers delta in our example. This element can either be a date (ABAP type DATS) or a UTC time stamp.


• maxDelayInSeconds means maximum possible time delay between UTC time stamp and the database commit.

detectDeletedRecords means that if a key combination does not occur in the view anymore, this will automatically generate a delete image in the extracted data. In our example, if a combination of sales document number and sales document item will be deleted, this annotation will generate an empty data with zero values for the combination. It’s necessary to check your BW release and patch level on SAP Note 2529167 before using this annotation.



Step 2: Create datasource via ODP - ABAP CDS Views connection.

After creating CDS view, create datasource via CDS connection. For Alternative-1, you need to select After Images Deletion Flag via Delta Queue delta process. Since you have already assigned a field for delta trigger, you don’t need to assign any field anymore. For Alternative-2, you need to select Delta with After Images delta process and then you need to choose delta trigger field on delta properties. There are date, time and timestamp options here. We used timestamp for our example.



Step 3: Create datapackages for both datasources. Firstly we create datapackage for initilization.



Step 4: Change a sales order from VA02. We changed order quantity from 12 to 14.



Step 5: We enter SE16 t-code and see updated data on our CDS view, CDPOS and CDHDR tables.



Step 6: Run datapackage for delta extraction and see data on PSA for both alternatives. Please pay attention to change mod field in Alternative – 1.



Step 7: Delete this sales order on VA02 and see log in CDPOS and CDHDR tables.





Step 8: Run datapackage for delta extraction again. You will see that the deleted data will arrive on PSA for Alternative – 1. However no data arrives for Alternative – 2. This means that you can miss deleted record in Alternative – 2.



Step 9: You can also view delta queue for Alternative – 1 on t-code ODQMON.



Optional: You can use a completely different logic to supply LastChangedAt field. As provided below, CDPOS and CDHDR tables could be used or any other table date and time fields could be used to catch delta. See how CDHDR table can be used to catch change log. Because of the limitations in CDS views, we had to use table function to use subselect in our example.



Conclusion: ABAP CDS view provides a serious flexibility for delta extraction. The methods provided in this document could be used instead of most of extractors.

Additionally, you don’t need to use setup table for LIS extractors. Just pull init, and run delta. However, since inventory extractor has initial stock (e.g. 2lis_03_bx) and has BW-specific fields like BWCOUNTER, a different approach is necessary for inventory data loads.

Alternative – 2 method could only be used for tables that has no deletion issue, e.g. sales billing tables VBRP and VBRK. It may be necessary to consult a module consultant to find out change log fields before starting to use delta functionality of ABAP CDS view.

Any date and time field in any table can be used to trigger delta here. Even a change in master data is required to trigger delta. In optional step, I tried to explain this flexibility.

It’s possible to create a real-time hybrid scenario with addition of Open ODS View to models provided above.

Thanks..

[donnie_burhan]

Hi Altay,

Thank you for the new approach.

I remember that in the old methods, there is an issue on having delta pointer on Change Date/Time field because Create Date/Time also needs to be monitored at the same time, or we only getting changed records only, losing newly created records.

Does your approach already cover this scenario?

Or maybe we need to add additional field filled with formula calculating which field having the oldest value (Create Date or Change Date)?

Also, how is the performance for using CDHDR and CDPOS table?

Those are tables which could expand very quickly in size and make extraction process suffer greatly.

 

Regards,

Donnie

[xarodoplu]

Hi Donnie,

Yes, we get both newly created and changed records. Actually, I didn't have any chance to do performance test for CDHDR and CDPOS tables with large volume of data. This is just an example to show how to enhance your LastChangedAt field.

regards,

Altay

[former_member267922]

Hi xarodoplu

Thanks for preparing such a nice step by step approach with multiple alternative and comparison. It was helpful and learning for me.

 

 

[former_member187192]

Hi Altay,

Thanks for sharing the useful information in detail.

Regards,

Mayank

[renjithep]

Hello,

Could you please clarify the timezone of the timestamp filed that you used in the example for Alternate 1.

Do you know if it is mandatory to have a timestamp field in UTC timezone for delta to work ?

 

Regards

Renjith E P

[ravikiran_sap]

Great post and thanks. cds view have created in S/4 hana or BW side? am new to bw4hana.

[marco_sposa]

Hello Altay is it possible to send the delta extraction by odata? is there any particular extension in the url may be?

Thank you.

[pierre-yves_guillou]

Hi, Just to make sure I understand the way it works:

You can use a table function to create a delta mechanism with a timestamp or date, right ?

 

Thanks

PY

[yashjangwan]

Hi All,

I am facing issue in STEP 2 : Create datasource via ODP – ABAP CDS Views connection.

After creating a cds view with the annotations, how to proceed to next step ? is there any tcode for this or any option in eclipse to create datasource.

Thanks & Regards.

[AdemGuler]

Hi Altay,

Thanks for sharing the useful information.

Regards,

[falkste]

Hello Yash,

the Datasource is directly created with the Annotation

@Analytics.dataExtraction.enabled: true

To Test your Datasource you have to call the report RODPS_REPL_TEST in SE38.

From the ODP Context Dropdown Menu within that report select ABAP_CDS ABAP Core Data Services and then you can search for your Datasource within the ODP Name. Simply look for your SQL View Name.

Regards

Stephan