Technical Articles
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.
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 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 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.
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
Hi Simon,
Very detailed blog. But thanks for taking time in writing it, looking forward to read your last one 🙂
Regards,
Harish
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
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
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
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
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
I would assume that the trigger will not be dropped in this case. It would be great if someone can confirm it.
Regards,
Raj.
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
Hi Matthias
Did you get an answer on this one?
I also wonder if there is a requirement of a SLT license when using CDC based extraction using ODP.
If additional license is not required (i.e. it is already covered by the S4 license), it also means that 3rd party applications that can read ODP, now can start using "SLT" to replicate data.
Besides BW and BODS, I know that also Informatica and Azure Data Factory can extract using the ODP framework.
I am pretty sure SAP must have an opinion on this.
Regards,
Leo
Hi Leo,
unfortunately I did not get an answer on this one so far.
We tried to get the information from other channels, but did not get an explicit statement.
Regards, Matthias
Very Informative blog Simon Kranig.
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.
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
Thanks Simon.
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
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
Hi Simon,
I have a similar case....need to use a timestamp that is stored as CHAR14 - bad design.
I have defined a safety interval of 2 days with this annotation:
@Analytics:{
dataCategory: #FACT,
dataExtraction: { enabled: true,
delta.byElement : { name: 'date_d',
maxDelayInSeconds : 172800 } } }
and for the "Delta-column": cast( left(ym.timestamp,8) as abap.dats ) as date_d,
As the table in source system is only interfaced by IDOCs it is hard to test the Delta Extraction - or create data changes.
Is there any tool to check on the actual executed selection, so that I would see that the safety intervall is really taken into account?
Thanks and regards,
Uli
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.
@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
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
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 job
. (
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
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
Hi Simon,
Just for benefit of readers of comments, I am sharing what we figured out.
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
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
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
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
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.
I have the same problem. Did you find a solution to this?
Yes omer, you need add an additional annotation above
the time stamp field @Semantics.systemDate.lastChangedAt: true. Then it works
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.
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.
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.
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.
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.
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
Hi Simon, We have a requirement on extraction. Extract data from SAP standard Parameterized CDS View to HANA Cloud using SDI.
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
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
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
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
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
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
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:
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.
You should not use ACDOCA lastchangedatetime for delta.
It is not a real last changed date time, but it's actually equal to the creationdatetime, and is not updated when changes occur.
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
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
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
I found we can see the subscription of CDC delta in t-cd: DHCDCMON
Super informative ....Thank you
Hi Simon,
Thank you for the detail blog. I am getting one issue while trying to implement filter.
Filter
Error says Invalid Filter.
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
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 :)?
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
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
Hi Tobias Trapp,
if you are referring to CDC delta, you will need to split your view into multiple views.
best regards,
Simon
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
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:
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.
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
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!
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
Hi Simon,
Getting everything to work with S4 Cloud is quite easy.
For on-prem however, it's really unclear. Is this the correct approach to use the standard S4 CDC enabled views in something like SDI, DWC?
In postman, this gives:
Or is there a different way? Most information I can find is talking about BW/ODP, but most of our customers require an SDI connection. Didn't get it to work in DWC yet.
Thanks a lot,
Louis
Hi Louis,
you can leverage a remote connection of type ABAP in SAP Data Warehouse Cloud for the SAP S/4HANA onprem (going via SDI DP Agent) and then -> Extractors -> ABAP_CDS.
best regards,
Simon
Hi Simon,
Well that was way simpler than I thought. Tested and confirmed working, looks great.
Thanks a lot!
Hi Simon,
we have been looking at exactly this type of scenario and have enabled real-time-replicated views in DWC. Any idea how deleted records would be handled? The standard CDS Consumption Views like C_SALESDOCUMENTITEMDEX_1 don't seem to have any type of deletion indicator. Is the deletion mechanism you mentioned only available for BW extraction?
Best Regards,
Irvin.
Dear Simon Kranig,
I believe the highlighted line must come for the previous record 4713.
Hi Lakshminarasimhan Narasimhamurthy
yes, you are absolutely right, I have updated the picture, thanks for letting me know!
best regards,
Simon
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.
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
Hello Simon Kranig,
Thanks for a wonderful blog . Our BW4 team is looking for a training on CDS views from a BW4 data source perspective.
Currently the training that SAP offers (S4D430) doesn't cover extensively the CDS views from a BW data source replacement perspective, it is purely designed for ABAP developers not for BW developers.
Please let me know if there is any such training is available or coming soon.
Thanks
Saila
Hi Saila,
I recommend trainings DBW462 or BW450 which cover CDS from BW/4HANA point of view.
Regards - Frank
Hi Simon,
thank you for this blog.
I have tried to do some prototype for a S4HANA migration project (S4 2020) in order to support the extraction of "old" tables like BSIK, BSAK, MSEG, MARD,... not yet existing (as table) in S4.
I found some limitations, like the activation of the automatic CDC (
@Analytics.dataExtraction.delta.changeDataCapture.automatic:) is not working for any tables (is it true?) as example I tried to create a simple view from MATDOC to extract the data of the old MSEG. It seems not working. Is it possible?
Do you have notice about any other limitations?
Regards,
Roberto
Hi Simon,
I am facing the same issue. Unfortunately, I am failing to get clarification. Have you managed to find the way forward?
Regards,
Denis
Hi Roberto,
i think you have to use the replacement object.
Do not use MSEG. Use NSDM_E_MSEG.
Replacement
BR
Daniel
Hi, great Blog Post.
The Question we have is: If i have a view on a subset of fields (lets say ID & Name of Business Partner) with CDC Delta enabled, we also get a delta record if fields in the basis Table change, which are not in the view.
Is there a way to define, on which fields the CDC Mechanism should trigger the Delta?
Thanks & Regards
Felix
There is not as triggers are table based.
Hi Simon,
thank you very much for this comprehensive and very informative blog about the delta handling of extraction-enabled CDS views.
I have one question about CDC enabled delta extraction in conjunction with SLT:
How could you set up the extraction that archived and deleted records should not be collected and no deletion be triggered on the target DB side of SLT?
Thanks and best Regards,
Peter
This is a good question which I hope to get an answer for as well.
With normal SLT replication, which I have worked with since 2011, we can today identify deletions from archive runs pretty simple as part of the trigger code thart is added to the main table.
I am not able to see at the moment how we can include this here, but I assume it must be possible.
Hope for a quick answer.
Hello Simon,
thank you for the informative blog.
Is there a possibility to identify the deletion timestamp for records with ODQ_CHANGEMODE = [D], Maybe over the ODQ framework?
In general, is it possible to have an access on deleted documents over CDS view?
Thanks & best regards,
Roland & Artur
Simon Kranig
We have created a CDS view with cross schema database tables. CDC-based delta is not working in that case. Do, you have any suggestions on this?
Many thanks!
BR.
Pabitra Acharjee
Hi Simon I hope you are well,
we are trying to implement the CDC logic based in the slides that you have attached but we are little bit confused .
our version is a S4_2020 SP2 <SAP S/4HANA FOUNDATION 2020 02 (05/2021)> .
We created a CDS view Z_BW_AUSP based on 2 tables and we are trying to apply the CDC concept. The extractor is able to extract fully all data but we get error once we try to extract delta:
Source <Z_BW_AUSP$P does not support deltas. We followed also SAP note 3195275 - ABAP CDS CDC: does not support deltas - SAP ONE Support Launchpad instructions but again extractor is failing.
As per the instructions, we have mapped all keys btw MAIN and joined table. Any hint to help us to find the cause?
@Analytics: {
dataCategory: #DIMENSION,
dataExtraction: { enabled: true,
delta.changeDataCapture: {
mapping: [
{
table: 'AUSP',
role: #MAIN,
viewElement: ['Mandt', 'Objek', 'Atinn', 'Atzhl', 'Mafid', 'Klart', 'Adzhl'],
tableElement: ['mandt', 'objek', 'atinn', 'atzhl', 'mafid', 'klart', 'adzhl']
},
{
table: 'CABN',
role: #LEFT_OUTER_TO_ONE_JOIN,
viewElement: ['Mandt', 'Atinn', 'Adzhl'],
tableElement: ['mandt', 'atinn', 'adzhl' ]
}
]
}
}
We are joined 2 std SAP tables: AUSP and CABN.
Best Regards,
Manos
Hi,
If I cannot find the standard datasource C_SALESDOCUMENTITEMDEX_1 in the business content, how can I activate it? We are using SAP BW/4 HANA 2.0
Or do I need to create the datasource myself by using the SQL view of the said CDS view?
sql view = CSDSLSDOCITMDX1
Thanks.
Loed
Just got to know 2 new annotations:
SEMANTICS.SYSTEMDATE.CREATEDAT
SEMANTICS.SYSTEMDATETIME.CREATEDAT
The 2 old ones are:
SEMANTICS.SYSTEMDATE.LASTCHANGEDAT
SEMANTICS.SYSTEMDATETIME.LASTCHANGEDAT
Hi Simon,
Please see my question here, if you can help:
https://answers.sap.com/questions/13733302/cds-delta-handling-odp-underlying-tables.html
Hi Simon,
we have issues while importing CDS views through ABAP_CDS of ODP Framework in Data Services. As mentioned in Note 2616307, we modified parameter for SAP Applications Datastore and trying to import our CDS view, but we have following error.
I tried with CDS DDL and SQL view as ODP objects but neither of them got import.
Could you please help us with this?
thanks in advance.
Hi Koushik Gangipelly ,
would like to encourage you to post your question in the SAP community, so it does not get lost under the blog here (which I can't monitor every day).
Regarding your question, just checking,
best regards,
Simon
P.S. if you want to blur out the view name, there is one occurrence of your CDS view name in the screen shot still unblurred.
Hi Simon Kranig, thank you for suggestion.
For readers to follow, here is the link to this question.
Issue while importing CDS view as ODP object into SAP BODS? | SAP Community
Thank you,
Koushik G
I have noticed a few comments / questions related to the "delete" operation in the thread, but I have had no luck finding any annotations in CDS to help. Is it possible to disable the delete trigger or to add a "soft delete" annotation to a CDS view? Ideal solution is one where records deleted in the source are not deleted in the target but flagged for deletion in a special column added to the target table DDL.
Simon Kranig
Gday! Requesting you to please check on the below Question and revert
https://answers.sap.com/questions/13795123/call-abap-proxy-from-sap-di.html
Thankyou in advances! Appreciate your Valuable Inputs on above question
Hi Simon,
I see the ignoreDeletionAfterDays annotation in the timestamp-based extraction method.
Is there any similar annotation in the CDC method? I want to avoid the archiving operation to prevent data loss
Look forward to your reply! Thanks!
Hi Jinchi,
To prevent data loss which's caused by archiving, a config table DHCDC_ARCH_USERS is introduced by note 3290438 - Handling of Archiving Actions in the Change Data Capture Mechanism (/1DH/* Triggers)
Regards,
Maggie
Hi Maggie.
Thanks for your reply!I will config and test recently.
Thank you,
Jinchi Li.
Hi Simon,
Is it possible to capture Delta based on 2 tables.
I have implemented following logic to capture deltas based on both tables - CATSDB and PA0001. CATSDB has COUNTER as primary key, whereas PA0001 has PERNR as primary key.
Following logic is not able to capture delta's from both tables - CATSDB and PA0001.
@Analytics:
{
dataCategory:#FACT,
dataExtraction:
{
enabled: true,
delta.changeDataCapture:
{
mapping:
[
{ table: 'CATSDB',
role: #MAIN,
viewElement: ['COUNTER'],
tableElement: ['COUNTER']
}
, { table: 'PA0001',
role: #LEFT_OUTER_TO_ONE_JOIN,
viewElement: ['PERNR'],
tableElement: ['PERNR']
}
]
}
}
}
Please suggest.
Thanks,
Vijay
Hi Simon,
For CDC based delta, since it is real time replication and using SAP Data Intelligence to replicate the data. If due to any reason package/records are not transferred to target system but already read from S/4 HANA system, then how we deal with handling the data loss? Any way to reset the trigger pointers?
Please suggest
Thanks,
Armaan