CDC data extraction Lessons Learned, Tips & Tricks – Part 3
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.
This blog post is about a specific issue with CDC when joining tables.
A CDS View was created to combine data from the table VBAP as main table (Sales Order Items) with the table PRPS (WBS Master Data) as left outer join. The join was done on the fields ‘ps_psp_pnr’ from the table VBAP and the field ‘objnr’ from the table PRPS.
It is important to know the underlying table keys, which for the main table VBAP are the fields „vbeln“ and „posnr“ and for the joined table PRPS the field „pspnr“. This means that the left outer join was performed on fields that are not part of a table key. The capture of data changes (Change Data Capture) should be done with an automatic CDC annotation (delta.changeDataCapture.automatic: true).
Delta extraction does not work due to the incorrect CDC definition. No delta package can be created in BW, and direct delta extraction via DTP fails. See Part 1 of this series.
Explanation and solution
In such a scenario, it is necessary to also include the key field of the joined table PRPS in the CDS definition. It must not be a CDS view key field – this would lead to another warning „Definition of the view is too complex for automatic CDC“ and CDC would again not work (see Part 1 of this series) – but it must be present as a field in the view.
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 come soon.