Skip to Content
Technical Articles
Author's profile photo Simon Kranig

CDS based data extraction – Part II Delta Handling

As promised in part one, let’s have a closer look at the delta handling in CDS based extraction.

In cases, in which big data volumes with a frequent changes are expected, e.g. transaction data like sales orders or similar, the application needs to provide delta records. In most of the cases daily (nightly) full uploads are not what you want, as time windows for data extractions are limited. Extraction of transaction data should always be delta enabled as this allows a seamless loading and reporting scenario.

The good news, the ODP framework for CDS extraction provides delta capabilities. You have actually two options for delta handling that you can chose from:

  • Generic Timestamp / Date based Delta: This requires date/time information being available in the relevant application tables that is updated based on changes of the application data.
  • Change Data Capture (CDC) Delta: This delta option captures changes to the application tables based on data base triggers and enables the ODP framework to just provision these newly created/changed/deleted records to the consumers.

 

Let’s check the two delta mechanisms in detail below, from which you can infer which option suites your data model and logic best.

Generic Date/Timestamp Delta

The generic delta based on date / timestamp has been around since release SAP S/4HANA 1809 on-premise and relies on a date/time element being present in the CDS view reflecting the changes of the underlying records.

You can use the following field types as delta criterion:

  • UTC timestamp
  • date field (ABAP type DATS)

Using a UTC time stamp is the preferred way for delta extraction. If no appropriate UTC time stamp is available in the application tables/CDS view, a date field can be used as well.

The following annotation identifies the relevant time/date field to be used by the ODP framework:

@Analytics:{
  dataCategory: #FACT,
  dataExtraction: {
	enabled: true
	delta.byElement: {
	   name: 'LastChangeDateTime'
	   }
    }
  }

 

Using this field, the ODP framework determines up to which record a data consumer has extracted records already. On a subsequent extraction request, only records with a higher time stamp/date value are extracted.

In case of a real-time delta subscription by a streaming process chain, the real-time daemon checks for new records in the CDS views every 15 seconds by default.

For non-real-time delta subscriptions new records according to the delta criterion are directly pulled from the CDS view during extraction.

As safeguarding measure, a safety interval can be specified. This can accommodate technical delays like waiting for a database commit on the application system side.

You can specify the safety interval using this annotation
@Analytics.dataExtraction.delta.byElement.maxDelayInSeconds
If you do not add this annotation, a default delay of 1800 seconds, i.e. half an hour, is applied.

@Analytics:{
  dataCategory: #FACT,
  dataExtraction: {
	enabled: true,
	delta.byElement : {
	  name: 'LastChangeDateTime',
	  maxDelayInSeconds : 1800
	  }
	}
}

A record with a time stamp falling in this time safety interval will be selected twice from the CDS view. Once with extraction run 1 and once with extraction run 2. The ODP frame work stores key data and hashes of all records belonging to the safety interval for finding changed records.

In the extraction run 1 records belonging to the time interval Start Timestamp of Extraction run 1 – maxDelayInseconds will be stored for later comparison.

In subsequent extraction run 2, records belonging to this time interval will be selected again and compared against the formerly saved records/hashes of extraction run 1.

Only records with changed hashes belonging to the safety interval will be extracted “again” to reflect the changes in the target system. Previously extracted records that have not changed within the safety interval, will not be extracted again.

Detecting deletion records using the generic delta

With the annotations so far, you will get newly created records as well as updates to existing records, but no deletions. The annotation

@Analytics.dataExtraction.delta.byElement.detectDeletions

enables the view to detect deleted records as part of the generic delta mechanism.

@Analytics:{
  dataCategory: #DIMENSION,
  dataExtraction: {
	enabled: true,
	delta.byElement : {
	  name: 'LastChangeDateTime',
	  detectDeletions : true
	  }
	}
}

Including this annotation will store all record key combinations being extracted in a separate data storage.

To identify deletions all records of this data storage are compared against all records still available in the CDS view during each extraction run. Records not available in the view anymore are sent to the consuming clients as deletions.

Needless to mention that this concept is only feasible for low level volumes of data (~< 1.000.000 data records) and should not be used for high volume data applications. Hence this mechanism is mainly applicable for small master data and text extractions.

The annotation
@Analytics.dataExtraction.delta.byElement.ignoreDeletionAfterDays
can be used to reduce the time frame of which records are considered for the deletion comparison. This means you have a trailing limit and only the set of extracted records falling into this time frame are compared against the currently available records in the CDS view.

 

@Analytics:{
  dataCategory: #DIMENSION,
  dataExtraction: {
	enabled: true,
	delta.byElement : {
	  name: 'LastChangeDateTime',
	  detectDeletions : true,
          ignoreDeletionAfterDays : '365'
	  }
	}
}

 

Examples:

  • Archiving: If data base records in your source system were archived after one year, you could define a value of 365. In this case, these “deletions” due to archiving in the data base tables will be ignored. In data warehouse scenarios you usually would not want these archiving deletions on source system side propagated as deletions in your data warehouse.
  • The application can guarantee a fixed time frame in which records can be deleted, preventing records to be deleted after this time frame; then only this interval needs to be taken into account for the deletion detection comparison.

Miscellaneous facts for Generic Delta

Please note the extraction example in the following figure, uses a date (ChangeDate), as the delta identifier.

 

  • Data records with an empty ChangeDate are only extracted during a “Delta Init with data”.
  • After the “Delta Init with data” has run, newly created records having an empty ChangeDate field will not be extracted anymore during further delta requests. (Record 4714).
  • Intraday changes will be reflected during intraday delta requests (cf. record 4712 on 27. Oct 2019) and corresponding records will be extracted again.
  • Timestamp based delta extraction only supports one time field (time stamp or date). This means only one field can be identified in the @Analytics.dataExtraction.delta.byElement.name annotation. You cannot assign two fields, e.g. creation date CREADAT and change date CHANGEDAT of an application table as delta determinants. You will need to make sure, that the change date field is equally filled at creation time and not only after a first change to the record. Otherwise these records will not be considered during delta updates.
  • Use only persisted time-stamp or date fields and refrain from using virtually derived or calculated fields in CDS views. This can lead to severe performance penalties.

 

Change Data Capture Delta

Starting with SAP S/4HANA Cloud 1905 and SAP S/4HANA 1909 FPS01 (on-premise) an additional delta capability facilitating database triggers can be used.

The second delta method goes by the name of Change Data Capture (CDC). For a CDS view using this delta method, changes in tables belonging to this view are recorded by the Change Data Capture mechanism. From a technology point of view this delta method makes use of real-time database triggers on table level based on SLT technology.

INSERT, UPDATE and DELETE operations can be recorded by the framework.

To function properly, the key fields of all underlying tables need to be exposed as elements of the CDS view and be mapped accordingly. The framework records the key field values of the changed table rows.

In case of an INSERT or UPDATE operation a scheduled job is selecting the records based on these key combinations from the CDS view and pushes them as complete records into the Operational Delta Queue (ODQ).

In case of a DELETE operation the job generates an empty record with just the key field(s) filled, sets the deletion indicator and hands them over to ODQ.

Let’s go into the detailed annotations needed for the CDC delta method.

The main task for exposing a CDS view with CDC delta method is to provide the mapping information between the fields of a CDS view and the key fields of the underlying tables. The mapping is necessary to enable a comprehensive logging for each of the underlying tables and subsequently a consistent selection/(re-)construction of records to be provided for extraction. This means the framework needs to know which tables to log, i.e. monitor for record changes.

Given one record changes in possibly only one of the underlying tables, the framework needs to determine which record/s are affected by this change in all other underlying tables and need to provide as consistent set of delta records to the ODQ.

Projections

The easy case is a projection CDS view on a table.

In this case the frame work can derive the relation between the fields of the CDS view and key fields of the underlying table itself. Whenever a record is inserted, updated or deleted in the underlying table, a record with the respective table key is stored in a generated logging table. Based on this info the scheduled job selects the data record from the CDS view and pushes it into the ODQ.

To specify the CDC delta for simple projections, you can use the
@Analytics.dataExtraction.delta.changeDataCapture.automatic

annotation:

@Analytics:{
    dataCategory: #DIMENSION
    dataExtraction: {
       enabled: true,
       delta.changeDataCapture: {
	 automatic : true
         }
      }
}

This is all you need and off you go with your CDC delta enabled extractor.

As an example for this, you can have a look at the CDS view for Business Area (I_BUSINESSAREA), which is a projection on table TGSB. In this case you only need the @ @Analytics.dataExtraction.changeDataCapture.automatic annotation.

Joins

In other cases, like joins, the developer must explicitly provide the mapping to the frame work. In general this means to expose the key fields of the main table as well as the foreign key fields in the on-condition(s) of the involved join(s) (for which you want to have changes tracked and delta records delivered) as elements in the CDS view.

The first step is to identify the tables participating in the join and its roles in the join. Currently only Left-outer-to-One joins are supported by the CDC framework. These are the common CDS views with one or more joins based on one main table. Columns from other (outer) tables are added as left outer to one join, e.g. join from an item to a header table.

Given there are no restrictions applied to the CDS view, the number of records of the CDS view constitute the number of records of the main table. All records from the main table are visible in the CDS view. Deletions of a record with regards to this CDS view only happen, if the record in the main table is deleted.

Secondly the developer needs to provide the mapping between the key fields of the underlying tables and their exposure as elements in the CDS view. Please check the following figure in which you see the representation of all underlying key fields surfacing in the CDS view.

 

The annotations relevant for this mapping task are subsumed under
@Analytics.dataExtraction.delta.changeDataCapture.mapping

@Analytics:{
    dataCategory: #DIMENSION
    dataExtraction: {
       enabled: true,
       delta.changeDataCapture: {
         mapping : [ {
           table : 'name of table',
           role : #MAIN|#LEFT_OUTER_TO_ONE,
           viewElement : ['list of CDS view elements'],
           tableElement: ['list of table fields']
         }, ... 
        ]
      }
   }
}

 

For each of the underlying tables for which a change should trigger a delta record, the following four mapping annotations must be maintained:
1. @Analytics.dataExtraction.delta.changeDataCapture.mapping.table
This annotation serves to identify the name of the underlying table(s) to be logged.

2. @Analytics.dataExtraction.delta.changeDataCapture.mapping.role
This annotation is used for identifying the role of a participating table

The main table receives the value #MAIN. The key(s) of the CDS view correspond(s) exactly to the key(s) of the underlying main table to be logged. The outer table(s) receive(s) the value #LEFT_OUTER_TO_ONE_JOIN.

3. @Analytics.dataExtraction.delta.changeDataCapture.mapping.viewElement
In case of the main table, (identified by role: #MAIN), this list enumerates all exposed CDS view element names corresponding to the key fields of the main table

In case of an outer table, (identified by role: #LEFT_OUTER_TO_ONE_JOIN), this list enumerates all exposed CDS view element names that correspond to key fields in this underlying outer table; in other words that correspond to the foreign key fields used in the on-conditions of the joins.

4. @Analytics.dataExtraction.delta.changeDataCapture.mapping.tableElement
This list enumerates the respective key fields of the underlying tables to be logged.

Both lists, viewElement and tableElement, must contain an equal number of elements and must list them in the same order so that corresponding fields match.

Important for the tables for which you want to have delta records recorded: For technical reasons, all foreign key fields pointing to the respective outer tables which are used in joins need to be exposed as fields in the CDS view. If some of these fields are not meant to be consumed by the end user, they can be hidden by the annotation
@Consumption.hidden

The figure below illustrates these mappings based on CDS view Data Extraction for Sales Document Item (C_SALESDOCUMENTITEMDEX)  which you can find in any SAP S/4HANA system starting from release CE1905 /OP1909. Please note, not all annotations are listed here, but just the relevant ones for pointing out the mapping.

Update (July 2021): The figure below illustrates this mapping based on the new CDS view “Data Extraction for Sales Document Item” (C_SALESDOCUMENTITEMDEX_1). Please note, not all annotations are listed here, but just the relevant ones for pointing out the mapping.

Mapping%20for%20C_SALESDOCUMENTITEMDEX_1

In this context I would like to highlight the importance of only adding those tables in the CDC mapping annotation, that should trigger a delta. In C_SALESDOCUMENTITEMDEX_1 e.g. CDC mappings to the tables for Company Code (T001) as well as for the Sales Organization (TVKO) are not present, as you do not want changes in these tables trigger a delta for all of the sales orders related to a particular company code or sales organization.

Please check note 3070845 for further details.

Filters

You might only want to track certain values which you deem relevant for your extraction. For this purpose you can use the filter annotation
@Analytics.dataExtraction.delta.changeDataCapture.mapping.filter

It can be used to define filter values on the table to be logged. If you only have a single filter value for a field, It can also be used to replace a missing mapping between a viewElement and a tableElement (i.e. an on-condition for a join).

@Analytics:{
    dataCategory: #DIMENSION
    dataExtraction: {
       enabled: true,
       delta.changeDataCapture: {
         mapping : [ {
           table : 'name of table',
           role : #MAIN|#LEFT_OUTER_TO_ONE,
           viewElement : ['list of CDS view elements'],
           tableElement : ['list of table fields'],
           filter: [{ tableElement : 'table element to be filtered'
                      operator :  #EQ|#NOT_EQ|#GT|#GE|#LT|#LE|#BETWEEN|#NOT_BETWEEN;
                      value : 'filter value'
                      highValue : 'upper filter value in case of range' } ], ...
 
         }, ... 
        ]
      }
   }
}

 

In detail, this is
…delta.changeDataCapture.mapping.filter.tableElement
specifies the table field to be restricted.

…delta.changeDataCapture.mapping.filter.operator
Restriction operator, possible values are: #EQ, #NOT_EQ, #GT, #GE, #LT, #LE, #BETWEEN, #NOT_BETWEEN

…delta.changeDataCapture.mapping.filter.value
the actual filter value

…delta.changeDataCapture.mapping.filter.highValue
specifies the upper value in case of a range in conjunction with .operator #BETWEEN or #NOT_BETWEEN

In some cases, a field for an explicit on-condition might be missing in one table, i.e. an additional constant value is used for determination of a unique record in the joined outer table.

Update (July 2021):Since the time of writing the initial version of this blog, things have changed quite a bit, e.g. ShipToParty etc. have moved in the data model and going forward you should be using view C_SALESDOCUMENTITEMDEX_1 instead of C_SALESDOCUMENTITEMDEX. However I leave the filter example below as is as it demonstrates very well how to leverage the filter annotation.

This behavior is illustrated based on CDS view Data Extraction for Sales Document Item (C_SALESDOCUMENTITEMDEX). The value for Business Partner function ShipToParty for a sales order (item) is stored in table SalesDocument: Partner (VBPA) with document number, item number and constant value ‘WE’ in key field Partner Function (PARVW).

 

To complicate things the ShipToParty can either be maintained in the header document or individually directly on a sales order item level in the application.

Regarding the extraction, a ShipToParty maintained on the item overrules the one in the header document, evaluated by the COALESCE statement in the CDS view; similarly for the PayerParty and BillToParty.

If the ShipToParty is maintained on item level, the on-condition of the join between Sales Document: Item Data (VBAP) and SalesDocument: Partner (VBPA) consists of

  • ? Sales Document Number (VBELN)
  • ? Sales Document Item (POSNR)
  • ? Constant value ‘WE’ for Partner Function (PARVW)

In this case a filter value for Partner Function (PARVW) is needed to uniquely determine the matching record in table SalesDocument: Partner (VBPA).

If the ShipToParty is maintained on header level, the on-condition of the join between Sales Document: Header Data (VBAK) and SalesDocument: Partner (VBPA) includes

  • ? Sales Document Number (VBELN)
  • ? Constant value ‘000000’ for Sales Document Item (POSNR)
  • ? Constant value ‘WE’ for Partner Function (PARVW)

In this case a filter value for Sales Document Item (POSNR) AND Partner Function (PARVW) is needed to uniquely determine the matching record in table SalesDocument: Partner (VBPA).

 

If the ShipToParty is changed either on header or item level, a new delta record for this CDS view is generated.

 

Pheeew, that’s it…

What do we get on BW side now?

So let’s check this DataSource… Good news, SAP has started delivering out-of-the-box content for SAP BW/4HANA already. Let’s have a look at the delivered DataSource.

DataSource%20C_SALESDOCUMENTITEMDEX_1

DataSource C_SALESDOCUMENTITEMDEX_1

 

The interesting part is on tab Extraction 

Delta Process, the DataSource is delta enabled and provides (overwrite) after images, which makes it easy to handle on BW side. And under Operational Data Provider we also spot the name of the @@AbapCatalog.technicalSQLView CSDSLDOCITMDX1 again.

Extraction%20tab%20DataSource%20C_SALESDOCUMENTITEMDEX_1

Extraction tab DataSource C_SALESDOCUMENTITEMDEX_1

 

I’ll give you a rest now, so you can digest all the annotations. In the next and last blog part you will find miscellaneous topics like how to test CDS extraction, CDS hierarchy extraction and a FAQ part.

Assigned Tags

      60 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Philipp Nell
      Philipp Nell

      Hi Simon, as a reaction to the first part of this series I asked about the detection of deletions. Now I know. Very cool. Many thanks again for the efforts. Cheers Philipp

      Author's profile photo Harish Allachervu
      Harish Allachervu

      Hi Simon,

      Very detailed blog. But thanks for taking time in writing it, looking forward to read your last one 🙂

      Regards,

      Harish

      Author's profile photo Mayank Jaiswal
      Mayank Jaiswal

      Hi Simon,

      Thanks for all your time in putting this great blog series.

      While I completely understood what you intended to explain I just have the below doubt.

      "For CDC do we require SLT system to be setup or it was just used to make analogy for trigger based delta handling"

      Eagerly looking forward for your next blog.

      Regards,

      Mayank

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Mayank,

      no need for an SLT system, this comes out of the box; reference to SLT was just to give a little background.

      best regards,

      Simon

      Author's profile photo Viktor Vass
      Viktor Vass

      Hi Simon,

      Thanks for sharing, this is a very good series of articles on the new CDC mechanism. What's the impact on the delta process for ODQ/ODP, if the trigger is dropped in S/4HANA? Is it the same like in SLT, which requires a full reload of the data?

       

      Best regards,

      Viktor

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Viktor,

      the triggers are dropped, once there is no subscription left (be it by ODQ/ODP, Data Intelligence (formerly know as Data Hub) or SLT) that needs the change recording for a respective CDS view/table.
      Once the subscriber deletes its subscription, it will need to do a full reload, if it subscribes again.

      best regards,
      Simon

      Author's profile photo Hansen Chen
      Hansen Chen

      Hi Simon,

      Will the DB Trigger causing issue on future activation of the table. Whenever we setup some DB trigger on DB level. If we want to add a ZZ field to the table, the DB trigger need to be dropped before we activate the new field. Will this CDC automatically generated DB trigger also have this issue?

      Thanks

      Hansen Chen

      Author's profile photo raja sekhar katuri
      raja sekhar katuri

      I would assume that the trigger will not be dropped in this case. It would be great if someone can confirm it.

       

      Regards,

      Raj.

      Author's profile photo Matthias Fessele
      Matthias Fessele

      Hi Simon,

      thanks for this great blog!

      To be sure in one topic:

      As there is no need für a separate SLT System, is there any additional Licence to S/4HANA Licence required when customers use CDC?

      Best Regards,

      Matthias

      Author's profile photo Nagarjuna Kalavagunta
      Nagarjuna Kalavagunta

      Very Informative blog Simon Kranig.

      Author's profile photo Jino Jose
      Jino Jose

      Hi Simon,

      Thanks for the detailed blog,  I got question on CDC on the join context.

      Can we trigger a delta for the transaction data based on the master data attribute change. Assume the currency of the sales organisation – “SO” is changed from USD to GBP, Does it going to trigger a delta for all the sales orders which uses the sales organisation – “SO” ?   or the delta will be triggered only if we make any changes to transaction data, in this case if you made a change to  sales order using the transaction va02 it will trigger a delta otherwise won’t.

      Thanks,

      Jino.

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Jino,

      generally, a delta for a CDS view will be triggered for any changes in tables which are mentioned in the CDC mapping section of that view.

      best regards,

      Simon

      Author's profile photo Jino Jose
      Jino Jose

      Thanks Simon.

      Author's profile photo Galland Peng
      Galland Peng

      Hi Simon,

      It's a very nice blog. When I use a date field for delta extraction, which annotation I can use to set the max delay in days? or I have to convert date field to timestamp?

       

      Thanks,

      Galland

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Galland,

      thank you. Regarding your question, you would be using the same annotation, i.e.

      @Analytics.dataExtraction.delta.byElement.maxDelayInSeconds 

      and specifiy the safety interval that you would like to have in the past. How many days back would you require to use as a safety interval?

      best regards,

      Simon

       

      Author's profile photo Sankaranarayanan Hariharan
      Sankaranarayanan Hariharan

      Thank you for the excellent blog, Simon. In our requirement, I have a CDS view based on ACDOCA table joined with other custom tables and output this view is enabled as ODATA service using annotation @OData.publish: true. I have two questions.

      1. Will this delta mechanism work through OData service. This is because I am using this service to load the data in SAP Analytics Cloud. I don't know if I can use the BW mechanism of ODQ  and get that into SAC. I am using import connection to SAC.
      2. I am not using the all the key fields in the final CDS view from ACDOCA table, means, ACODCA has document level, but my view does not have document number field. When I write the annotation, I am getting an error "Count of elements does not match"

      @Analytics.dataExtraction.delta.changeDataCapture: {

      mapping : {
      {table : 'ACDOCA',
      role : #MAIN,
      viewElement: ['Ledger', 'LedgerFiscalYear','I_JournalEntryItemCube.CompanyCode'],
      tableElement: ['RLDNR','RBUKRS', 'GJAHR','BELNR', 'DOCLN']},

       

      As we are looking at aggregated data and my source view is to fetch ACDOCA table data using SAP std view I_JournalEntryItemCube, how can I go about this.

       

      Kindly advise these scenarios.

      Thank you so much for your help and support.

      Regards,

      Sankar

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Sankar,

      no, this is not supported to my knowledge. Wouldn't you use live connection and/or data aquisition mode in SAC based on an analytical query CDS view?

      Error message means, that the number of view fields and the number of table fields do not match in the mapping annotation.

      Best regards,

      Simon

      Author's profile photo Sankaranarayanan Hariharan
      Sankaranarayanan Hariharan

      Hello Simon,

      Thanks for your reply.

      I figured out the error and for now, I have pulled all the key fields from ACDOCA in the final CDS and managed it until we have a solution.

      On the delta set up again, we are using data acquisition mode (Import connection) and trying to store the data in SAC. Now ACDOCA being a huge table, unless we have something on delta mechanism, we will have to do full loads all the time and we can imagine the data volume maybe an year after. Hence we are trying to have a delta load for this.

      We tried something called incremental load in SAC import jobIncremental%20load. (

      Incremental load

       

      Now this setting is enabled only for very few standard CDS views like C_ProfitCenterHierNode and not for view having transactional data like A_JournalEntryItemBasic or I_JournalEntryItemCube. This setting is looking for a field like numeric pointer (ever increasing value like Timestamp). So I created a custom view, added those timestamp from ACDOCA  and used annotations below.

      At View Level

      @Analytics.dataExtraction.enabled: true
      @Analytics.dataExtraction.delta.byElement: { name : 'LastChangeDateTime' }
      @Analytics.dataCategory: #FACT
      @OData.publish: true
      @ObjectModel.entityChangeStateId: 'LastChangeDateTime'

       

      At field level

      @Semantics.systemDateTime.lastChangedAt: true
      LastChangeDateTime,

       

      Still this field is not picked up the SAC for activation of incremental update. Not sure if this setting can only work for standard CDS views.

      Client is not looking to use BW ODQ as it will lead to additional work.

      Any advice/ideas to resolve will help me further.

       

      Regards,

      Sankar

       

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Sankar,

      I am no expert at SAC data acquisition, sorry

      Probably good to post your question in the SAP community for SAC.

      SAP Community SAC

      best regards,

      Simon

      Author's profile photo Sankaranarayanan Hariharan
      Sankaranarayanan Hariharan

      Hi Simon,

      Just for benefit of readers of comments, I am sharing what we figured out.

      1. The incremental load setting gets enabled if we have date based field on CDS output.
      2. There is no additional delta setup (annotations) needed from CDS perspective.
      3. The delta pointer looks like to be controlled from SAC end.
      4. As of now, using Timestamp field for incremental update doesn't work, but documentation says it will work. We have raised a SAP message.
      Author's profile photo Lakshmaiah Ravilla
      Lakshmaiah Ravilla

      Hi Bro,

       

      am trying to achieve CDC not working for my client is 1909 bw4hana system. i tried standard CDS for CDC but no luck. please help me.

      Thanks

      Ravilla

      Author's profile photo Alex Slagter
      Alex Slagter

      Hi Simon,

      Awesome post!  What are your recommendations about customizing the standard CDS extractors like 0C_SALESDOCUMENTITEMEX? With the older extractors we can write a customer exit.  In almost every scenario we will need another field by joining tables.  Is the recommendation to copy the standard CDS view?  Or is there some sort of extend CDS view you recommend?

      Thanks,

      Alex

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Alex,

      you got me there, this point is still on my todo list and if time permits I will add it to the blog.

      You can have these views extended as explained in the documentation here . Please keep in mind that theses extended fields will not trigger a delta, only changes in the original CDS view will to my knowledge. Hope that helps.

      best regards,

      Simon

      Author's profile photo Laskshmi Saikumar
      Laskshmi Saikumar

      Hi Simon,

       

      Thanks for detailed blog.Can you please Confirm if Extending existing CDS view will effect existing Delta on extractor on main  CDS view.

       

      Regards

      Saikumar

      Author's profile photo Lakshminarasimhan Narasimhamurthy
      Lakshminarasimhan Narasimhamurthy

      Excellent Blog, Thanks, gave a deeper insight to me.

       

      I tried creating a CDS view with delta on "Timestamp" on my B4HANA system, but getting the below error.

      "Element timestamp is not a valid delta element"

      We are on HANA 2.0 BW4HANA 200 SP3 (11/2019) SAP BW/4HANA 2.0

      The below is the custom table created by me, which is the source of my CDS view, I have "Timestamp" fields in it.

       

       

       

       

      Author's profile photo Ömer Ülker
      Ömer Ülker

      I have the same problem. Did you find a solution to this?

      Author's profile photo Lakshminarasimhan Narasimhamurthy
      Lakshminarasimhan Narasimhamurthy

      Yes omer, you need add an additional annotation above

      the time stamp field @Semantics.systemDate.lastChangedAt: true. Then it works

      Author's profile photo Lakshminarasimhan Narasimhamurthy
      Lakshminarasimhan Narasimhamurthy

      i resovled it using Analytics Annotations - SAP Help Portal

      Analytics.dataExtraction.delta.byElement.name

      Application developers can enable the generic delta extraction with this annotation. This is the element that should be used for filtering during generic delta extraction. This element can either be a date (ABAP type DATS) or a UTC time stamp.

      Scope: #VIEW

      Evaluation Runtime (Engine): Specifies which view will be exposed in replication scenarios.

      Value Description
      elementName as String Name of an element that should be used for filtering during generic delta extraction.
      Author's profile photo Lakshminarasimhan Narasimhamurthy
      Lakshminarasimhan Narasimhamurthy

      one more question in the S4HANA still the ABAP based extraction valid?

      Suppose we are extracting data to BW4HANA from S4HANA system, where we need to use multiple Function module/Class to extract the data.

      Currently in ECC we can achieve the above using Generic datasource based on Function module(RSAX_BIW_GET_DATA/RSAX_BIW_GET_DATA_SIMPLE). Is the same type of extraction possible in S4HANA system.

      Author's profile photo raja sekhar katuri
      raja sekhar katuri

      The sap note 2884410 - CDS based data extraction: Delta capability, points to this blog for more information.

      It would be great if someone could share the details around achieving the similar functionality in CDS as that in Function module based data source used for extracting data.

      regards

      Raj.

      Author's profile photo Daniel Klein
      Daniel Klein

      Of course this should still be supported if you additionally connect your S4 system as source system of type ODP_SAP. The ODP-Framework does not change how Extractors work, so features like function module for data extraction or Customer Exits will still work like in legacy BWs.

      Author's profile photo Srinivasarao Pydisetti
      Srinivasarao Pydisetti

      Hi Simon,

       

      Its nice blog and very much informative, Thank you for your effort.

      I have a query, how do we handle the mandatory parameters in the BW side in case if the CDS view has with below annotation.

       

      define view <ZVIEW_NAME> with parameters

      P_StartDate : Event_startdate,

      P_EndDate : Event_enddate

       

      Whether BW supports if the CDS view has mandatory parameters?

       

      Thanks in advance.

       

      Thanks and Regards,

      Srinivas.

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Srinivas,

      parameters can be used and then have to be filled as DTP filters from BW side..

      Please be aware that CDS views with parameters only support full loads. (cf. blog part I )

      Best regards,

      Simon

      Author's profile photo Vijay Bodanapu
      Vijay Bodanapu

      Hi Simon, We have a requirement on extraction. Extract data from SAP standard Parameterized CDS View to HANA Cloud using SDI.

      1. We have created Virtual Table on Parameterized CDS View
      2. In Flowgraph - created a datasource consuming the above virtual table
      3. But, we do not see parameters available to pass to CDS view.

      Do we need to have any annotation in CDS view so that, the parameters would be exposed to SDI? Please suggest us.

      Thanks in advance,

      Vijay

      Author's profile photo Vijay Bodanapu
      Vijay Bodanapu

      Hi Simon,

       

      It is really very nice article and informative. We have an ETL requirement that needs to use I_GLACCOUNTLINEITEMRAWDATA which is CDC Delta Based on acdoca table.

      We are in S/4HANA 1909 FPS01. The delta records are not fetching in odqmon. I have tested delta functionality using RODPS_REPL_TEST. I dont see delta records in this case as well.

       

      Can you please share me data points/tables where I can locate and troubleshoot the reason for not picking delta records.

       

      Appreciate your help.

      Thanks,

      Vijay

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Vijay,

      in the meantime there are some notes regarding the CDS delta extraction, so I would kindly ask you to do a short review of the notes (mainly on components EIM-DH-ABA-CDC and BC-BW-ODP).

      If this does not help, I would ask you to open a ticket for your issue.

      best regards,

      Simon

      Author's profile photo Vijay Bodanapu
      Vijay Bodanapu

      Thank you Simon for your quick inputs.

      Yes, I have gone through oss notes around these components. Also tried to copy this standard cds view into Z view and adding few annotations etc. It didn't help

      Will raise a ticket to SAP.

       

      Thank you,

      Vijay

      Author's profile photo Daniel Klein
      Daniel Klein

      Hi Vijay,

      all readers here would appreciate if you could share the feedback/solution that SAP Support gave you within the ticket.

      Thanks and Regards Daniel

      Author's profile photo Vijay Bodanapu
      Vijay Bodanapu

      Hi,

      I couldn't recollect the program name which SAP had used to fix the issue. As far as I remember, the issue was around enabling delta on log tables. I am out of that project now, and no access to get ticket details. Really sorry!! If you are facing the same issue, please raise a ticket.

      Regards,

      Vijay

      Author's profile photo Angel Velasquez
      Angel Velasquez

      Hi Vijay Bodanapu.

      You can modify your CDS using TIMESTAMP field from ACDOCA configuring that like an element into delta attributes.

      Its sounds like that:

      ATTRIBUTES%20DELTA%20ACDOCA

       

       

       

       

       

       

       

      Then you must define this in your select expression:

      Is very important cast the field TIMESTAMP with preserving type like image above.

      When replicated the CDS from SDI or BW for example, the extractor appear active in ODQMON delta queue and delta image works after 1800 sec, because is default value for element delta.byElement.maxDelayInSeconds: 1800.

      If you want change this value, just put the value that you desire after attribute delta.byElement.name: 'lastChangedAt'.

      Well, here shown like delta image works fine.....

      I hope this works for you and all.

      Regards.

      Author's profile photo Mayank Jaiswal
      Mayank Jaiswal

      Thanks Simon for sharing this information.

      For using CDC based delta extraction do we need to setup SLT ? If yes what are the configuration steps.

      I am looking for CDC based ABAP CDS view data extraction using BODS to third party applications.

      Best Regards,

      Mayank

      Author's profile photo Daniel Klein
      Daniel Klein

      Hi Mayank, already answered here as "not necessary": https://blogs.sap.com/2019/12/16/cds-based-data-extraction-part-ii-delta-handling/comment-page-1/#comment-488782

      Author's profile photo Yuki Sakumoto
      Yuki Sakumoto

      Hi Simon,

      Thank you for the nice article.

      I have a question, how can we check subscription in ABAP side?

      I'm trying to consume CDC delta with Data Intelligence and the delta records are transferred correctly. However, I cannot see the subscription in ODQMON, even we can specify ABAP CDS CDC as subscriber type.

      Best regards,
      Yuki

       

       

      Author's profile photo Yuki Sakumoto
      Yuki Sakumoto

      I found we can see the subscription of CDC delta in t-cd: DHCDCMON

      Author's profile photo Viren Pravinchandra Devi
      Viren Pravinchandra Devi

      Super informative ....Thank you

      Author's profile photo Mohit Sarda
      Mohit Sarda

      Hi Simon,

       

      Thank you for the detail blog. I am getting one issue while trying to implement filter.

      Filter

      Filter

       

       

       

      Error says Invalid Filter.

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Mohit,

      sorry can't help here, hard for me to dive into the details of each view and filter constellation.

      From first impression what you are trying to achieve, you could get some inspiration from extraction enabled CDS view C_SERVICEORDERITEMDEX.

      There is also BW/4HANA Content available for the service orders, you can find the details here.

      regards,

      Simon

       

       

      Author's profile photo Sebastian Kolbeck
      Sebastian Kolbeck

      Hi Simon,

      I have a similar filter issue then in the post before. It seems, that it is not possible to implement a filter on the #MAIN entity, when such a field is not a key field. I have tried to add the non key-field to the annotation, but it can not map it (I think it is because as it is a non-key field).

      Field PRAT7 is a customer append field on table KNVV

      Syntax error

      So the general question is: it it possible to capture the delta only on a subset of the table content, like here only customers(table knvv), which are engineers (CustomerEngineeringFlag = 'X')?

      I tried other filter options like simply adding a WHERE clause and using  the annotation Analytics.dataExtraction.filter[{ viewElement: 'CustomerEngineeringFlag', operator: #EQ, value: 'X' }].

      But this does not help: when a customer outside the restriction(CustomerEngineeringFlag <> 'X') is modified, then a Delete Image (Record Mode = 'D') is  put in the delta queue. I hope this was understandable :)?

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Sebastian,

      unfortunately you will receive all deletion records with their respective keys (only), irrespective of any filters that you might have put on the CDS view via WHERE etc...

      Similar to delta records created for records with updated fields that might not be exposed in your CDS view.

      best regards,

      Simon

      Author's profile photo Tobias Trapp
      Tobias Trapp

      Hi Simon Kranig ,

      thanks for this interesting blog 🙂

      I have a question: Is there also a support vor UNIONs or do have to split my view into multiple views?

      Best Regards,
      Tobias

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Tobias Trapp,

      if you are referring to CDC delta, you will need to split your view into multiple views.

      best regards,

      Simon

      Author's profile photo Tobias Trapp
      Tobias Trapp

      Thanks. Is there any information about further restrictions? I found nothing in SAP Support and also note here.

      My question is: when I convert INNER JOINs to LEFT OUTER TO ONE JOINs usually I have to introduce WHERE conditions. Is this supported and if yes, have I also define additional FILTERs for each WHERE condition?

      Best Regards
      Tobias

      Author's profile photo Daniel Klein
      Daniel Klein

      Hi all, if you want to know which ABAP-CDS-Views are CDC-enabled in your S/4HANA system, execute the following SELECT either within DB50-SQL-Editor or via HANA Studio:

      SELECT strucobjn "CDS-Entity"
        FROM DDHEADANNO
       WHERE NAME = 'ANALYTICS.DATAEXTRACTION.DELTA.CHANGEDATACAPTURE'
       ORDER BY 1;

      This is returning 44 CDS-Views in our S4 1909 FPS1 system, of which only three are consumption views:

      C_BILLINGDOCUMENTITEMBASICDEX

      C_PURCHASEORDERITEMDEX

      C_SALESDOCUMENTITEMDEX

      Hence, we are not able to extract e.g. the 'Sales Document Item Condition Data' with CDS-View C_SALESDOCITMPRCGELMNTDEX as mentioned here: https://help.sap.com/viewer/06e872f914a44d77b6c692b0273ca400/2.0.3/en-US/531f28d8954f41dcb481ffd6e339d1e5.html

      Sadly, the documentation does not leave a word on which S/4 release and FPS is necessary to use this CDS-View, however, it was shipped with S/4HANA 2020.

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Daniel,

      as you are mentioning CDS views from the SD space, just wanted to point you to note 3070845 (had updated the blog to reflect this a while ago).

      best regards,

      Simon

      Author's profile photo Louis De Geest
      Louis De Geest

      Hi Simon,

       

      Thanks for the great blog, it's really insightfull.

       

      I was wondering if there's any information available on how to enable these views for odata/API.

      How does the CDC capture mechanism work in that case?

       

      Thanks!

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Louis,

      you can have a look here which describes the integration scenario based on Cloud Data Integration ( documentation ), also meaning that the receiving client needs to "speak" CDI (like SAP Data Intelligence, SAP Data Warehouse Cloud or others).

      Hope that helps.

      best regards,

      Simon

       

       

       

       

      Author's profile photo Lakshminarasimhan Narasimhamurthy
      Lakshminarasimhan Narasimhamurthy

      Dear Simon Kranig,

      I believe the highlighted line must come for the previous record 4713.

       

       

      Author's profile photo Simon Kranig
      Simon Kranig
      Blog Post Author

      Hi Lakshminarasimhan Narasimhamurthy

      yes, you are absolutely right, I have updated the picture, thanks for letting me know!

      best regards,

      Simon

      Author's profile photo Hirdesh Gupta
      Hirdesh Gupta

      Dear Simon,

      I have one question :  I have  delta enabled CDS view  which is directly used in ODP and further  BODS system is getting data.

      Delta  ( Timestamp field) is working fine and we are performing daily extraction.

      Now BODS want a new field/flag for every record which shows that this record is  Insert (New) OR  Update  record.

      How to enable this feature in CDS view ?  Or Is there any feature in ODQMON for this.

      CDS view is based on database table .

      Thanks.

      Author's profile photo Roger Loe
      Roger Loe

      Hi Simon

      With SLT the table triggers write the changes to a logging table - when the SLT replication process runs - SLT will extract an aggregated changed record from 1 or more logging tables records - so if there are 3 updates to a single row during a SLT replication cycle - SLT only replicates the final (single) row with all changes.

      so you loose the ability to see the single 3 events...

      Does the CDC process you describe provide for the extraction of every single "change event" on the base row - irrespective if the change is to a key field or not?

      Or does it behave in a similar manner to SLT?

      Many thanks