CDC data extraction Lessons Learned, Tips & Tricks – Part 4
Introduction
This blog series is about warnings and error messages that you may encounter with using CDC delta, even though pretty good documentation, and how to work around them. We share these experiences with you to save your valuable time.
In Part 1 of the series we looked at a warning you might run into when using automatic CDC.
Part 2 of the series was about some warnings and errors messages you might face when using CDC with manual mapping.
Part 3 of the series we looked at an issue you might get when using CDC in combination with joining tables.
This blog is about the use of where clauses in combination with the automatic CDC mechanism.
Scenario 1
A CDS view was created to combine data from the VBAP table as the main table (sales order items) with the VBAK table (sales order header) as an inner join. The link was created via the field ‘vbeln’ of both tables. As only records with VBAP-PSTYV = ‘TAN’ are to be extracted, a corresponding where clause was added.
When using an inner join with where clauses you will get a warning and automatic CDC extraction will not work.
Scenario 2
Once again, a CDS view was created based on the VBAK and VBAP tables. This time a left outer join is used instead of an inner join. The data is to be restricted to a value of a field in the second header table of the join ( VBAK-bukrs_vf = ‘1710’ ).
Again where clauses in this case at “right” table of the join leads to warning and automatic CDC extraction will not work.
Result
Delta extraction does not work in both scenarios due to the combination of join and where clauses in the view definition. No delta package can be created in BW, and direct delta extraction via DTP fails. See part 1 of the series.
Explanation and solution
As is so often the case, there is not one but several solutions to reach your destination. Here are 3 tried and tested solutions that lead to the desired result: use the automatic CDC delta mechanism + joined tables + filtering.
As a first simple solution, you can consume both tables as a separate CDS view and merge them in the following BW data flow and add the necessary logic and filters. Of course, you must be aware that more than the required data will be extracted, transferred, and then filtered out again in BW.
As a second option, CDC filtering can be defined using additional filter mapping annotations. See the following SAP-Blog. The relevant annotation is:
As a third option, you can consume the data with automatic CDC and a left outer join and apply the required filters in the subsequent BW data flow. However, you must be aware of the differences between a left outer join and an inner join and know what data you expect as a result.
Required filters can again also be moved into the BW data flow, knowing that in this case more data is extracted and transferred than actually required in the BW application.
As last approach you can also try to use manual CDC annotations with a mapping information. Manual CDC annotations with a mapping shall be descript in separate blog post.
Interested in more possible sources of error, pitfalls and points to be considered when using CDC delta extraction? Stay tuned, next blog post with more hints, tips and tricks will follow…