Working with ODP Context - CDS view based extraction

The new SAP standard method for extraction of data from SAP S/4HANA to SAP BW/4HANA is based on the SAP ABAP Core Data Services (CDS) technology. This replaces the S-API technology.

The first CDS-based DataSources were shipped with the SAP S/4HANA 1809 on-premise release.

SAP S/4HANA Cloud leverages a HTTP/SOAP-based connection instead of an RFC-based connection to SAP BW/4HANA. For this reason, ODP_SAP is not available for this scenario and ODP_CDS is the general strategic core interface for SAP BW/4HANA extraction from SAP S/4HANA Cloud. The technical framework and connectivity (HTTP / SOAP based) between SAP BW/4HANA and S/4HANA Cloud (the so-called communication scenario) is available with S/4HANA Cloud 1808. A first wave of ABAP CDS Content for extraction and the first version of key user tools for extensibility with S/4HANA Cloud exists since the 1811 release. Starting with SAP S/4HANA Cloud 1905, SAP has started rolling out extraction of enabled standard CDS views and has been extending the coverage since.

The ODP context for ABAP CDS views (ABAP_CDS) provides the options to connect to SAP BW/4HANA via RFC or HTTP-based connection. SAP sources providing CDS extractors via the ODP Framework must have at least one of the following release levels: SAP NW 7.4 SP08, SAP NW 7.5 SP02, SAP BW/4HANA 1.0.

There are two characteristics to be defined by annotations to release a CDS entity for extraction:

• Type of CDS entity: There are two annotations available to define whether the objects represents transactional data, attributes, texts or hierarchies. Those are ANALYTICS.DATACATEGORY and OBJECTMODEL.DATACATEGORY.
• Type of Extraction: Annotation ANALYTICS.DATAEXTRACTION.ENABLED provides the required property and tells the system to generate a so-called operational data provider to enable modeling of ODP_CDS DataSources for SAP BW/4HANA. Additional annotations are available to specify a delta mechanism if required, unless only full extraction is supported.

The following annotations enable an ABAP CDS view for FULL extraction (and for virtual access via Open ODS View):

• @Analytics.dataCategory(#DIMENSION, #FACT or #CUBE) OR @ObjectModel.dataCategory(#TEXT or #HIERARCHY) AND

• @Analytics.dataExtraction.enabled

For DELTA extraction the following additional sets of annotations are available:

• @Analytics.dataExtraction.delta.byElement.(…) for Date / Time Stamp-based Delta (available since SAP S/4HANA 2018)

• @Analytics.DataExtraction.Delta.ChangeDataCapture.(…) for CDC-based Delta (available since SAP S/4HANA 2019)

Note

We have a more detailed look at scenarios using full and delta extraction later in this lesson.

At runtime, a TransientProvider is generated for a each ABAP CDS View. A TransientProvider generates SAP BW/4HANA meta data from an ABAP CDS View so that it can be processed by SAP BW/4HANA.

The transaction RSRTS_ODP_DIS is used to display a TransientProvider preview for an Operational Data Provider.

This tool displays the meta data of the TransientProvider which is used at run time. Those CDS entities need to be specified as facts, master data, text or hierarchy in the corresponding Data Category annotation. You can check the meta data and preview the resulting data. Additionally, it is possible to receive an overview of all fields (characteristics and key figures) with all their properties. It is a helpful tool to analyze CDS views.

To retrieve the mapping of the SQL View name of the ABAP CDS View to the ABAP DDIC View you can use table RSODPABAPCDSVIEW.

Let's make a comparison between how customers worked with the classic ABAP function module based extractors that use the older S-API technology, and the new technology based on SAP ABAP Core Data Services.

As opposed to transaction RSA3 , which is not available to test CDS-based extractors, the report RODPS_REPL_TEST is used to test ODP replication.

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 not a dummy run.

The ODP Name is the @AbapCatalog.sqlViewName and is suffixed by the data category classification of the CDS entity the following way:

• $F for facts/transaction data
• $P for master data attributes
• $T for texts
• $H for hierarchies
• $E for other/without data category

CDS views use the Standard ODP Queue and related monitoring tools (ODQMON). If the delta is managed by CDC there is an additional CDC Monitor available (DHCDCMON).

Launch the next demo to explore an ABAP CDS View for extraction:

Note

For more details about the new CDS extractors in SAP S/4HANA refer to following blog post:

https://community.sap.com/t5/technology-blogs-by-sap/finding-the-right-cds-extractor-in-sap-s-4hana/ba-p/13521296

There are several scenarios where ODP_CDS can be used. Let's have a look at the following scenarios:

• CDS Extraction in FULL mode (without Delta Capability)

• CDS Delta Extraction (BW-managed) based on Date/Time Stamp

• CDS Delta Extraction (ODP-managed) based on Date/Time Stamp

• CDS Delta Extraction based on Change Data Capture (CDC)

In the figure above, the CDS entity is representing transactional data of the SAP BW/4HANA OLAP statistics as defined by @Analytics.dataCategory: #CUBE. In addition, the extraction is enabled by @Analytics.dataExtraction.enabled:true. Based on these properties, a full extraction is available towards an SAP BW/4HANA system.

Even if no delta-related annotations are defined in the CDS object, there is an option to set up a delta-mechanism which is managed by the SAP BW/4HANA application: Similar to a filter in the DTP, SAP BW/4HANA implements a local WHERE condition when processing the CDS view in the source system to select the time interval following the last extraction. If you define a date as delta field in the SAP BW/4HANA DataSource (other options are Time Stamp or Numeric Pointer), a given day can only be extracted once and during regular loads you should only collect closed days (for example, current day -1, as of yesterday).

In general, you should use this BW-managed delta carefully and only in cases where a delta is required but cannot be defined in the CDS entity itself (for example, in your organization you are not authorized to change any existing CDS entities or create your own ones). The preferred way is to leverage the delta implementations managed by ODP instead which are introduced in the remaining parts of this lesson.

The delta based on the date/time stamp is sometimes also referred as generic delta and the concept is similar to the classic ABAP extractors of the past. Generic delta in CDS has been available since the SAP S/4HANA 1809 on-premise release 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 criteria:

• Date (ABAP type DATS)
• UTC Time Stamp

Leveraging a time stamp is the preferred way for delta extraction. If no appropriate time stamp is available in the application tables/CDS view, a date field can be used as well as an alternative. Only persisted timestamps or date fields should be used, virtual derivation at runtime is not recommended due to performance concerns. Records with empty timestamps or date fields are only extracted during extraction in DTP mode "Delta Init with data".

The following annotations define the details of this delta approach:

• @Analytics.dataExtraction.delta.byElement.name: Application developers can enable the generic delta extraction with this annotation. This element defines the field be used for filtering during generic delta extraction.

• @Analytics.dataExtraction.delta.byElement.maxDelayInSeconds: There is always a time delay between taking a UTC time stamp and the database commitment. This annotation specifies the maximum possible delay in seconds. If you do not add this annotation, a default delay of 1800 seconds, that is, half an hour, is applied.

• @Analytics.dataExtraction.delta.byElement.detectDeletedRecords: By using this annotation, the system 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. In other words, if a key combination does not occur in the view anymore, this will automatically generate a delete image in the extracted data.

  Note

  Needless to say, 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. Another annotation is available 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.dataExtraction.delta.byElement.ignoreDeletionAfterDays: Referring to the annotation before, the extraction will ignore deleted records if they are older than the specified number of days (for example, for the purpose of archiving).

Using a delta 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 date/time stamp are collected for extraction.

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 approximately 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 a safeguarding measure, a safety interval can be specified. This can accommodate technical delays like waiting for a database commit on the application system side. 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 time stamp of Extraction run 1 – maxDelayInseconds will be stored for later comparison.

In the 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.

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. 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 that are not available anymore in the view are sent to the consuming clients as deletions.

Starting with SAP S/4HANA Cloud 1905 or SAP S/4HANA 1909 FP01 (based on SAP NetWeaver 7.54) on premise, SAP has introduced an additional delta capability facilitating database triggers. This 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 (= SAP LT Replication Server). SQL INSERT, UPDATE and DELETE operations on database level (not application !) are recorded by the CDC 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 it over to the ODQ.

The main task for exposing a CDS view with the 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, that is, 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.

The following annotations define the details of this delta approach:

• @Analytics.dataExtraction.delta.changeDataCapture: Defines that the Change Data Capture (CDC) delta mechanism based on data base triggers is used.

• @Analytics.dataExtraction.delta.changeDataCapture.automatic: The easy case of setting up CDC is a projection CDS view on one single table. In this case, the framework 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 information, the scheduled job selects the data record from the CDS view and pushes it into the ODQ.

• @Analytics.dataExtraction.delta.changeDataCapture.mapping: Setting up CDC is more complex when the underlying CDS model comprises more database tables. In case of more complex CDS views, for example, using joins, mapping information needs to be provided. With this annotation you define the mapping of exposed elements in the CDS view and the underlying table key fields.

  Note

  In general, only those tables should be added to the CDC mapping annotation, that should trigger a delta. For example, in the new CDS extractor to Sales orders ( C_SALESDOCUMENTITEMDEX_1), mappings to the tables for Company Code (T001) as well as for the Sales Organization (TVKO) are not present (any more, as compared to the prior C_SALESDOCUMENTITEMDEX), as you do not want changes in these tables to trigger a delta for all of the sales data related to a particular company code or sales organization (see also SAP note 3070845).

• @Analytics.dataExtraction.delta.changeDataCapture.mapping.role: The key of the extraction view corresponds exactly to the key of the underlying main table to be logged. The main table receives the value #MAIN. The keys of the CDS view correspond exactly to the keys of the underlying main table to be logged. The outer tables receive(s) the value #LEFT_OUTER_TO_ONE_JOIN . Currently only Left-outer-to-One joins with one main table are supported by CDC.

• @Analytics.dataExtraction.delta.changeDataCapture.mapping.table: Specifies the name of the table to be logged.

• @Analytics.dataExtraction.delta.changeDataCapture.mapping.viewElement: List of table key fields that map to CDS view key elements.

• @Analytics.dataExtraction.delta.changeDataCapture.mapping.tableElement: The key of the extraction view corresponds exactly to the key of the underlying physical main table to be logged.

• @Analytics.dataExtraction.delta.changeDataCapture.mapping.filter: 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. This annotation allows the definition of filters on the table to be logged for a CDS view, if the filter is one single value and it can replace a missing mapping between view-element and table-element.

When you consume an ODP_CDS DataSource which provides a CDC-delta, the interesting detail is on the Extraction / Delta Process tab of the DataSource definition in SAP BW/4HANA: it is delta-enabled and provides (overwrites) after images, which makes it easy to handle the data transfer on the SAP BW/4HANA side. Under Operational Data Provider, you can also see the name which is specified in the CDS view at annotation @AbapCatalog.technicalSQLView.

Deletions of a record with regards to this CDS view only happen if the record in the main table is deleted.

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 .

You can use transaction DHCDCMON to start CDC Monitoring. This tool provides an overview of delta processes registered for Change Data Capture (CDC), the internal SLT jobs and ODP jobs and the subscriptions and tables including some helpful expert functions (cleanup control tables, check or unsubscribe master/subscriber logging table and related database triggers, delete recording objects). You can also use report IUUC_LIST_TRIGGERS to list DB triggers; control table DHCDC_RECCONTROL stores the mapping of application tables to logging tables.

Hint

For more details on CDC delta refer to this SAP Blog series: Part II Delta Handling

https://community.sap.com/t5/enterprise-resource-planning-blogs-by-sap/cds-based-data-extraction-part-ii-delta-handling/ba-p/13425761