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.
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..
Okumaya devam et...
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..
Okumaya devam et...