CDS based data extraction – Part III Miscellaneous

[SimonKranig]

Back to the CDS view extraction..

After the Overview in part I and Delta deep dive in part II, I would like to spend the final blog on some miscellaneous points regarding CDS view based extraction.

 

Hierarchy extraction

The topic of hierarchy extraction deserves a small section, as some things have changed here in contrast to the classical extractors. You will need to make some changes to your hierachy bearing characteristics in order to work with the hierarchy CDS views. This is due to the fact that the node texts are not delivered by the actual hiercharchy CDS view, but a separate one.

We'll use the Cost Center hierarchy as example. You will need the following three CDS views for getting the complete picture for a cost center hierarchy across:

• Cost Center Hierarchy Node (I_COSTCENTERHIERARCHYNODE)


• Cost Center Hierarchy Node Text (I_COSTCENTERHIERARCHYNODET)


• Cost Center Text (I_COSTCENTERTEXT)

You will need to equip a characteristic Cost Center, ZCOSTCTR in our case, with two additional external characteristics in the hierarchy:



One characteristic for the Cost Center Hierarchy ID (ZCOCTRHID) type CHAR40.



One characteristic for the Cost Center Hierarchy Node Text (ZCOCTRHNT) type CHAR50, compounded to the Cost Center Hierarchy ID



 

The hierarchy transformation between a DataSource based on CDS view Cost Center Hierarchy Node (I_COSTCENTERHIERARCHYNODE) and the cost center characteristic needs to be mapped as follows (Segment 0003):



 

The hierarchy node texts will be delivered via a DataSource based on CDS view Cost Center Hierarchy Node - Text (I_COSTCENTERHIERARCHYNODET) and loaded into the characteristic Cost Center Hierarchy Node Text (ZCOCTRHNT)



The cost center texts will be delivered via a DataSource based on CDS view Cost Center Text (I_COSTCENTERTEXT) directly into the cost center characteristic as usual:



Just for completenes, please note that the two additional characteristics needed as external hierarchy characteristics can have different lengths according to a particular hierarchy characteristic (e.g. GL Accounts, Profit Centers etc.). Please make sure to check them in the respective hierarchy node CDS view first.

 

Testing CDS view extraction

With classic extractors the Extractor Checker S-API (transaction RSA3) is your friend on source system side. No need replicate a DataSource to BW side etc. Bad news, you cannot use RSA3 to test your extraction enabled CDS views.



 

How can you test CDS view extraction? The good news, you can use report RODPS_REPL_TEST.

Before sharing the details, please consider the warning on top (Warning: Not Simulated): Initializing a delta and/or running a delta extraction with the report will trigger a real subscription in the ODQ frame work. This is no dummy run.

When starting the report RODPS_REPL_TEST you will need to fill the fields as shown in the screen shot below.

The ODP Name equals the @AbapCatalog.sqlViewName again and is suffixed by the data category classification in the CDS view:

• $P for master data attributes


• $T for texts


• $H for hierarchies


• $F for facts/transaction data


• $E for other/without dataCategory

After you are done with testing please make sure to press Reset Delta so your ODQ subscription can be cleaned.



One speciality for the hierarchy extraction test. For being able to see the extracted hierarchy data you will need to check the hierarchy segement that you would like to see, i.e. use F4 value help for Projections



 

While in RSA3 you can navigate to the different segements via a drop-down



in RODPS_REPL_TEST you can double-click on the figures to jump to these segment entries.



 

Units, Currencies, Fiscal Year Variants via CDS

In SAP BW and SAP BW/4HANA you will most likely also want to work with the units of measurement, currencies, currency conversion rates and fiscal year variants as they are maintained in your SAP S/4HANA system. In the on-premise world, this means a right click on your source system, choosing options

• Transfer Exchange Rates


• Transfer Global Settings

This is the same for a SAP S/4HANA Cloud source sytem in the ODP_CDS context:



In case you do not see these menu entries for your SAP S/4HANA Cloud source system, please check SAP note 2792996.

Alternatively after implementing the SAP note you can call up the reports RSIMPCUST (currencies, units of measurement and fiscal year variants) and RSIMPCURR (exchange rates) and trigger the replication manually or scheduled via process chain.

 

BW/4HANA Content based on CDS extraction

As mentioned in the last blog already, SAP has started delivering content based on CDS view extraction.

So far the Sales and Distribution (Sales Documents, Billing Documents, Conditions) and Plant Maintenance areas have been covered. You can find them in the documentation.

For the upcoming areas please check the road map slide in our BW/4HANA Content presentation here.

As usual taking over the content is done via transaction RSORBCT, selecting the data flows or objects that you require. As it sometimes leads to confusion about the content being missing, please make sure to check the steps described in the following SAP note, especially point 4 and 5

2433354 - Missing Business Content DataSources or Transformations when using ODP framework

 

FAQ

Q: Is there a Mapping of classic extractors to CDS views?

A: Unfortunately you will not find a mapping between classic extractors and CDS views provided by SAP; mainly due to the fact that data structures in the SAP S/4HANA have and keep changing as well as the extractor logic that can be expressed in a classical ABAP based extractor vs a CDS view.

-

Q: Where can I find a list of available extraction enabled CDS views delivered by SAP?

A: Please have a look the blog of my colleague Ranjitha Balaraman here

Additionally starting from SAP S/4HANA 2020 as well as in the current SAP S/4HANA Cloud Edition you can use the CDS view I_DataExtractionEnabledView to identify CDS views available for data extraction. The view also allows for identifying CDD delta enabled DataSources selecting 'X' for field DELTACHGDATACAPTUREISSUPPORTED.

-

Q: I a missing a certain CDS view for extraction, where can I provide feedback to SAP to request it?

A: Please go to the customer influence site and provide your request here.

-

Q: What about global field names in CDS views and BW naming?

A: You can find the details on the CDS naming conventions in the SAP help documentation. Due to their descriptive nature, CDS view field names are sometimes quite lengthy clashing with the SAP BW limits, like DataSource Field names, Unit field names in field-based DataStoreObjects (CHAR 25). So please keep your eyes open to not lose the semantics of a field.

-

Q: Which delta method should I use?

A: hm, it depends. If possible from a CDS point of view (projection or a left_outer_to_one_join) go for the Change Data Capture (CDC), escpecially if you are after deletions as well.

-

Q: Formerly an application has worked with before- and after-images in the classic extractor. Is this still supported in the CDS view based extraction?

A: CDS view based extraction will only deliver after-images, i.e. the status of the record at the point of time it was added to the ODQ. This results in mode Overwrite (MOV) for key figures on BW side. Hence if you are working with InfoCubes (in SAP BW) or Data Mart DataStore Object (SAP BW/4HANA) you will need a Standard DataStore Object as first target in your data flow before.

-

Q: Can I switch between full/delta methods?

A: Changing a C1 released CDS view from full data extraction to the CDC delta extraction is considered a compatible change. Hence adding the annotation for CDC delta into an already C1 released full-extraction CDS view is possible.

Changing the delta method from generic to CDC delta is considered an incompatible change as you will be forced to re-initialize your data load. If reloading the data is not an issue for you, you can neglect this point.

-

Q: How do classic extractors from the Logistic cockpit compare against CDS views?

A: The good news, with CDS view extraction you can skip the whole process of Set-up tables, system lock, but start loading data right away. Also SAP is focusing on delivering the data via  denormalised header-item CDS views, so no more header-item consolidation on BW side.

-

Q: What about filters on non-key fields using the CDC delta?

A: Please do not use any filters on non-key fields (i.e. as part of the WHERE condition of a joined view). An update on the table level might lead to a change of the cardinality of the join.

-

Q: Using the CDC delta I receive delta records for records that supposedly do not have changed.

A: Due to the nature of the CDC delta you will always receive a delta record in case some field value has changed in the underlying tables of your CDS view, no matter if this field is exposed as a view element in the CDS view. In case the field in question is not exposed as view element, your delta record will look the same as the one you already have. As the records are all after-images, no harm to your existing data, but good to keep this in mind in case you wonder.

-

Q: Are there any CDS annotations that do not go well together with extraction annotations?

A: Yes there are, please make sure to avoid the following annotations in a CDS view that you want to use for extraction:

• @Odata.publish : true


• @analytics:dataCategory : AGGREGATIONLEVEL


• @analytics.query


• @ObjectModel.transactionalProcessingEnabled : true


• @ObjectModel.transactionalProcessingDelegated : true


• @ObjectModel.virtualElementCalculatedBy




• @UI.*

-

Q: After having changed the CDC annotation I receive the following message during data loads "Change data capture of is broken".

A: To properly work with your updated CDC annotation you will need to reset/cancel all subscriptions for this particular view. From BW side this means deleting the respective data load requests. For other requests you can also directly cancel them in transaction ODQMON. You can find more details in note 2930269.

Getting help

Last but not least, if something goes wrong and the above answers do not help you ...

Should you experience any issues with CDS extraction to BW please open a ticket on the component Operational Data Provider for ABAP CDS, HANA & BW (BW-WHM-DBA-ODA).

Should you experience any issues with CDS extraction using the Change Data Capture (CDC) delta, please open a ticket on component ABAP Integration - Change Data Capture (CA-DI-IS-ABA-CDC).

 

The End

Well, I guess this is it... for now..., thanks for reading up until here. As things might change and new features appear, I will update this blog accordingly, so feel free to come back any time.

Any feedback, questions let me know; happy to answer your questions and to extend the Q&A part.

[Subramanian]

Thank you Simon for the detailed blog posts around CDS views as extractors.

I am currently trying to build a CDS view with CDC on the table JEST (Statuses).

The view looks ok, I see the right options while creating the datasource and DTP.

The initial load has worked. But when it comes to transferring delta, I am not sure what I am missing. I keep getting zero records in delta. Is there a job here which I need to run to make the changed records come through in the trigger tables come through?

 

Is this related to a table (like JEST)  not being compatible for database triggers?

[Subramanian]

Just an addendum - The case here is CDC - Automatic with no mappings. It is just based on projection selecting OBJNR and STATUS as key fields from JEST along with INACT field,

Just not sure what I am doing wrong.

 

[SimonKranig]

Hi Subramanian,

from what i see, the view looks good. Could you state your SAP S/4HANA version? CDC delta is available starting from SAP S/4HANA OP FP01...

best regards,

Simon

[former_member629211]

Simon,

Very well written blog . Thanks for all the hard work in putting together the process in details.

One question I still have. Though you specified FieldsGlass , Concur and Ariba in your Part-1 I do not see any details on how to extract the data from these sources to BW/4HANA or BW on-premise systems.

Can you though some light on this aspects too ?

Thanks

 

[Markus_Ganser]

Hi Simon,

excellent explanation!

Great stuff, thanks!

[former_member439779]

Hi Simon,

 

What is the alternative of RSA2 for CDS view based datasources? I understand RODPS_REPL_TEST replaces RSA3. What replaces RSA2 then?

The reason I ask is that I have such a datasource and can't find it in RSA2 of my source system.

 

Regards,

Sagar

[SimonKranig]

Hi Sagar,

there isn't really a UI for looking at the properties of it on SAP S/4HANA side, but you can open the respective CDS view in the ABAP Editor in Eclipse.

best regards,

Simon

 

[DirkScheller]

Hi Simon,

I learnt a lot reading your three blogs about CDS based data extraction. Thanky you for taking the time to compile this list. It was really helpful.

One question though. If I am only interested extracting data from C_SALESDOCUMENTITEMDEX for a fixed SALESGROUP (e.g. 001) will the CDC framework then take care that only deltas are tracked for records which have SALESGROUP 001?

If the data is changed to SALESGROUP 002 will e delete record be written?

RODPS_REPL_TEST allowes to specify selection criteria. So at least it seems like something like this is possible but if you could provide some details what happens behind the scene on a technical level this would be great.

Best Regards,

Dirk

 

[jonathan_viau]

Hi Simon,

 

Reading section "Testing CDS view extraction" shows still a lot of manual steps to test.

Do you know a way to test the CDS View extraction with automated tests (e.g. ABAP Unit?)

 

Regards,
Jonathan

[SimonKranig]

Hi Dirk,

if you restrict it for example from BW side via a DTP filter (SalesOrg 0001), you will not get an updated record into your BW later, if the record is changed on source system side with regards to this field. So you will need to be aware which fields you use in your filter, and if they can be changed in the application.

best regards,

Simon

[DirkScheller]

Hi Simon,

Thx for the reply. Just to check if I understand this correctly:

Assumption: I have from BW side via a DTP filter (SalesOrg 0001)

Now in source system document A with SalesOrg 0001 is created

=> This document will be fetched with next delta and will then be visible in BW

Now in document A the SalesOrg is changed to e.g. (SalesOrg 0002)

=> This change will not be captured with next delta. So document still is visible in BW with the old state (SalesOrg 0001)

If this is the case then this means I can/should only define fields as filter criteria that are immutable in the source system (e.g. application guarantees that SalesOrg can no longer be changed once document is created => Then field SalesOrg could be used as a Filter Criteria without risking any data inconsistencies between source and BW system)

Best Regards,

Dirk

[SimonKranig]

Hi Dirk,

yes, this is what I meant in my admittedly very condensed answer.

best regards,

Simon

[ravilla]

Hi Simon,

 

My client is using 1909 but still am not able to achieve this delta through CDC. but generic delta is working.

i tried standard enabled  CDC but not working for delta. is it require any additional steps is need to take care.

Thanks

Ravilla

[Subramanian]

Thanks Simon,

Apologies, I missed replying to you (looks like notifications for replies is not working for me).

The version of S/4HANA is 1909.

I can see the GL Line Item Raw Data extractor uses the CDC method. But even that is not working.

[former_member321020]

Hi Simon,

Thanks alot for detailed explanation on CDS Extraction.

I followed as per the blogs and able to replicate the cdsview (C_SALESDOCUMENTITEMDEX) in b4hana system successfully. I see delta process as "Overwrite delta with deletions(delta queue)" for this cdsview in the data source level.

Now i have created a new custom cdsview using cdc and replicated to B4hana system, but i do not see this option "Overwrite delta with deletions(delta queue)". Instead i see "Overwrite delta without deletions(BW local)"  or  "No delta,only full".

So how to get this option "Overwrite delta with deletions(delta queue)"  for custom cdsview?

Thanks

sheshi

 

[SimonKranig]

Hi Sheshi,

hm, hard to judge from your writing, seems CDS view is not being regarded as delta-enabled for some reason. Could you check your view definition, CDC annotation again and/or try with another sample custom view?

best regards,

Simon

 

[former_member696209]

Hi Simon. Hope you're doing fine. I have tried all the steps to create the CDS but I'm having problems at the point of the delta. I run the RODPS_REPL_TEST program and the delta init is created on the ODQ monitor. But once I make changes in the view table and run the program again as delta the suscription is created in the ODQ monitor but it's empty so no delta is registered. Is it possible that I'm missing something in between? I followed every step in your article but the results are different from the ones expected.

Thanks in advance!

[SimonKranig]

Hi Matias,

(assuming your system is on the right SP level, i.e. at least 1909 FP01 for S/4HANA on premise and your CDS view definition and annotations are correctly set)

Having run the delta init with RODPS_REPL_TEST  and then having changed some records in the underlying table you should actually get them with the next run of the RODPS_REPL_TEST program as delta records.

best regards,

Simon

[johnny999]

I don’t understand why we need the BW extraction from CDS views to begin with. If you use BOBJ as the reporting tool, eg, it can report the CDS views directly. So what is the advantage of extracting the CDS data into BW, other than cases when you want to combine CDS data with another ADSO? And BOBJ can potentially combine the data without BW?

Thanks Jonathan.

[Phil_from_Madrid]

Hi Jonathan, Certain type of reports might use data from different systems in a consolidated output. Another reasons is that the overall data transformation logic is too complex or the overall amount of data to be analysed is too big for an acceptable runtime. Extracting data to BW(/4) gives you the possibility to consolidate data and to create additional persistencies which can be understood as intermediate results of the overall data transformation logic/function. I still see a reason for existence of a DWH. For other scenarios a direct access to S/4 will make a lot of sense (Operational Analytics which is the "functional" scope of Embedded analytics). Hope this helps. Philipp

[johnny999]

Philipp, thank you for your feedback. I agree with your points on data cleansing and data volume consolidation. With the introduction of CDS VDM, things get rather confusing on which approach to go. The VDM is a competitor of BW and it is surprising SAP introduces a competitor against its own product.

[former_member132950]

Thank you for these excellent blog posts 🙂

 

There is one point I dind't understand. What is the benefit we get, when using foreign key associations (@ObjectModel.foreignKey.association) in cds views for data extraction? The cds views delivered by SAP for data extraction make extensive use of these foreign key associations.

But these associations do not seem to add benefit. For example there is no value help for filtering in DTPs and so on.

[former_member725373]

ternis

Hi Simon,

For the First time, Initiliztaion from the CDS view is possible to extract data by filtering based on Year filter?

Basically, we are extracting data from CDS view streaming in DI and getting loaded in Teradata.But for the first time load can we load data year based and then the delta should keep flowing in .

eg: Full Load -2019, then 2020 then 2021 then set init without Data (something similar to BW)

Regards,

Anita

[ranastasescu]

Hi Simon,

@simon.kranig Thanks for the great post, it did help a lot.

The hierarchy extraction worked great with the texts and all.

However after marking the main object (in your case the ZCOSTCTR) as authorization relevant, the texts would no longer be displayed.

Have you or your colleagues encountered the same behavior? Do you have any recommendations?

Looking forward to your answer,

Razvan

[marco_sposa]

Hello and thank you for the superb blog.

Is it possible to require the delta in the url query of an odataservice?

Thank you.

[falke]

Hi simon.kranig.

Thank you for sharing your knowledge. One question regarding CDS Views in SAP NetWeaver 7.50 based SAP ERP Systems.

Will there be or is there be the CDC capability also available? Currently I see only the date/timestamp based delta available.

Thank you for your hint.

BR Falk

[mondfalter]

Hi Simon,

How is the procedure if the CDC Delta tables are changed after delta init(in my case another table was necessary for the extractor, which I added to the CDC Delta Tables).
If I delete the abonnement in ODQMON is end up with inconsistent tables (ODQSSN is no longer filled with my extractor, but table RODPS_REPL_SUBSC still has an entry)

I used the following note to delete this entry: https://launchpad.support.sap.com/#/notes/2437185.
In DHCDCMON I see that there is another table for my extractor, but there is no trigger and no protocol tables.
Now my delta which I start in BW/4 is crashing because this is missing.

This note sent me here:
https://launchpad.support.sap.com/#/notes/2930269

What is the proper way to reinitialize a delta after the CDC Delta fields/tables were changed?

Best regards,
Alex

[JaySchwendemann]

Hi Simon,

refering to your " Are there any CDS annotations that do not go well together with extraction annotations"... It seems virtual elements do not play with CDS based data extraction.

1. Am I right in this assumption?


2. If so, is it a known limitation?


3. If so, will it be lifted at a future version?

 

:) Thanks and cheers

jens

[SimonKranig]

Hi Jens,

yes, you are right the virtual elements are not supported for the extraction annotation. Currently I am not aware of any plans to change this. I will add it to the section.

thank you for pointing this out.

best regards,

Simon

[simadireddy]

Hi Simon,

We are using CDS view based delta data sources.

Our source system in S4, our target is BW4HANA.

For Delta datasources, which have CDC enabled a set of background jobs keep running every 10mins.

For one particular datasource that is based on ADRC table, there are error logs generated in SLG1 that refer to the CDS View for this datasource.

We have decided to make the datasource as FULL and remove the delta annotation.

Despite removing the annotations for CDC based Delta in the CDS view and making FULL only the error logs are getting generated.

There is an entry in the transaction DHCDCMON, we would like to understand how to get rid of the entry for the datasource in the below screen.

We would also like to understand the impact of the "STOP LOGGING"  button.

Thanks in Advance,
Siddharth M

[JoergBoeke]

Hi Simon,

I've got a problem with delta recognition.

I try to extract data via CDS table function from an existing hana calculation view that contains CH_ON ( date field = last changed).

Now I try to extract data via ODP, but  my external tool as well as RODPS_REPL_TEST delivers init delta

BUT..

 

Second delta...even if there is no delta, the extraction delivers aprrox the same amount of records as init delta

 

Any idea what I did wrong?

See extraction of my CDS below.

Any help is appreciated

@AbapCatalog.sqlViewName: 'ZV_CP_REVI_EXTRACT'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Datsource CP Revision Export CDS'

@Analytics: {dataCategory: #FACT,dataExtraction.enabled: true}

@Analytics.dataExtraction.delta.byElement.name: 'LASTCHANGED_AT'

@Analytics.dataExtraction.delta.byElement.maxDelayInSeconds: 1800

@Analytics.dataExtraction.delta.byElement.detectDeletedRecords: true









define view ZDS_CP_REVISION_EXP_AZURE as select from zds_cp_tab_revison_exp(p_CLNT:'002') {

key '002' as MANDT,

...

VVCTYPE ,

VTYPE ,

...

AMOUNT ,CH_ON as LASTCHANGED_AT

}

[naseemvp007]

Hi Simon,
I have following questions regarding CDC based delta mechanism for CDS views.

1. Is it mandatory that the CDS view be C1 released?


2. I am getting a lot of data lock errors when trying to run DTP and eventually landing in time out for CDC based delta. Could you please suggest a solution or any thoughts on this.

Best regards,
Naseem

[albertosimeoni]

Does "CdC delta" work for Table Functions CDS Views?

[Phil_from_Madrid]

Hi, I think with my understanding of CDS CdC works, it should be possible , at least if you put a wrapper around the CDS view that is implemented as a table function. You have to include all CDC annotations that are relevant for you. You might want to have a look at the standard _DEX CDS views. Some of them include complex "CDC observation" instruction spanning across several tables.

[Phil_from_Madrid]

But, I never tried it, but I'm glad you asked about this. If you have a result please let me know if I was wrong or not. Thanks

[albertosimeoni]

Hello,

I didn't try it yet. I find that practically the way SAP / ODP reconstruct the delta rows is a mistery box for me, is all virtual / dynamic ABAP programming.

I was able to find at database level the trigger and sequences generated to register CdC when the queue is initialized.

then there are 2 jobs that reconstruct for every logged table the "new" rows and put the full column results of CDS in another table, and after it is consumed by Datasphere the data is compressed in ODQDATA tables.

The mistery is in how it build the new rows.

My "simplified" hypothesis is : for example  "table A join B":

select 

from A(full actual data) join B(delta record)

union

select

from A(delta record) join B(full actual data)

So it should work if you set correctly the annotations.

Another thing I was thinking: does it works with aggregations ?

for example:

Sales document item (VBAP) join Schedule lines (VBEP)

in order to retrieve ordered/confirmed qty, think about Schedule lines aggregated by Sales document and position before join.

In this case it shouldn't work as VBEP.ETENR is not present in the final CDS projection. So the solution could be to reverse the join (VBEP left join VBAP) I think.

Best Regards,

Alberto

[JustinYoung]

Very helpful documents!👍

I have one question about enhancing fields in a standard CDS View , just like datasource enhancement in BW datasource.

Can it only be enhanced using an extend view and then extract from the extend one?

[cristina_ungureanu]

Hi Simon, 

I’m encountering a strange issue when loading data for i_costcenter and all associated CDS views linked to cost center. The data source appears empty, even though I can confirm that there is data in the S/4HANA system.

Interestingly, for i_profitcenter, I am able to retrieve data without any issues.

I followed the steps and used the program from your post to perform the necessary checks.

Could you please provide any insights or guidance on what might be causing this discrepancy?

Thank you in advance for your help!

Cristina