Loading Data Using ABAP CDS Views

In SAP S/4HANA, a Virtual Data Model is built with ABAP CDS views, which can be used to extract data to SAP BW/4HANA using Operational Data Provisioning (ODP). If the ABAP CDS View contains the required annotations, you can use the view for full and delta extraction.

There are some prerequisites for extraction:

• Source Requirements

  • Full extraction or direct-access scenarios require a minimum of SAP NetWeaver 7.5 SP04 or equivalent.

  • Delta extraction scenarios (see next lesson) require a minimum of SAP NetWeaver 7.5 SP05 or SAP BW/4HANA SP01.

• Target Requirements

  All ODP CDS extraction scenarios require a minimum of SAP NetWeaver 7.3 SP08 or equivalent (ODP recommendation). SAP BW/4HANA is also an allowed target, even though technically it does not run on the full SAP NetWeaver Stack.

To use data provisioning using ODP based on ABAP CDS Views, a source system must be defined (ODP_CDS) with ODP Context ABAP_CDS.

The ODP context for ABAP CDS views (ABAP_CDS) provides the options to connect to SAP BW/4HANA through RFC or HTTP-based connection. Starting with SAP NetWeaver 7.5 SP08, new annotations were provided to define the extraction properties to ensure all required services for setting up and managing the delta extraction. For system requirements and the required corresponding annotations, see also SAP Note 2361926.

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

1. Annotations @Analytics.dataCategory and @ObjectModel.dataCategory. These two annotations are available to define whether the objects represents transactional data, attributes, texts, or hierarchies.
2. : Annotation @Analytics.DataExtraction.Enabled. This indicates that the ABAP CDS view can be used as operational data provider to enable modeling of ODP_CDS DataSources for SAP BW/4HANA. Additional annotations are required to specify a delta mechanism if necessary: otherwise, only full extraction is supported.

When creating a DataSource based on an ABAP CDS view, the SQL view name of the ABAP CDS view must be selected.

Let's start with full loads using ABAP CDS views. In this scenario, an ABAP CDS view, representing transactional data of the SAP BW/4HANA OLAP statistics, has the following annotations:

• @Analytics.dataCategory: #CUBE
• @Analytics.dataExtraction.enabled:true

Based on these properties, a full extraction is available towards an SAP BW/4HANA system. In the DataSource properties, the Delta Process contains the value "No delta, only Full."

This is suitable for the virtual scenario with Open ODS view and for the data load scenario with standard InfoObjects as a target. When you load data to a DataStore Object (advanced), the disadvantage is that already existing records are loaded again, causing long loading times and high resource consumption. You then need a Standard DataStore Object as a target (or a Staging DataStore Object with compression) to eliminate the repeated records.

There are different options related to delta loads with ABAP CDS Views. Let's explore the following scenarios:

• BW-local managed Delta.
• CDS Extraction with Generic Delta.
• CDS Delta Extraction Based on Change Data Capture (CDC.)

In this scenario, no delta-related annotations are defined in the source's CDS object.

However, there is an option to set up a delta-mechanism, which is managed by the SAP BW/4HANA application by using the Extraction tab of the BW DataSource. Similar to a filter in the DTP, to select the time interval following the last extraction, SAP BW/4HANA implements a local WHERE condition when processing the CDS view in the source system. If you define a date as a delta field in the SAP BW/4HANA DataSource (other options are time stamp or numeric pointer), a given day can only be extracted once. During regular loads, you only collect closed days (for example, current day -1, as of yesterday). To achieve this, set the Upper Interval (Days) to 1.

Note that the options for this mechanism are limited. You can only use numeric pointer, time stamp, or date fields. The mechanism does not support deletion detection. In general, 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, if in your organization you are not authorized to change any existing CDS entities or create your own ones. The preferred way is to apply 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 to 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: It 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

When extracting data through this framework, the system stores the Transaction Sequence Numbers (TSN) of the time of extraction (not the highest value of the source). Only records for which the delta field contains a higher timestamp are delivered. You can check the tables ODQSSN or ODQQUEDES for the stored TSNs. Records may have an earlier timestamp, because they are updated to the leading table after the extraction with some delay. In this case you must specify a (lower) safety interval, similar to the generic datasources. Therefore, you add an annotation.

Applying 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.

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 to be used for filtering during generic delta extraction.
• @Analytics.dataExtraction.delta.byElement.maxDelayInSeconds: There is always a time delay between taking a UTC timestamp 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. This mean that only records with a timestamp older than 1800 seconds (= 30 minutes) are extracted in the current run. These ignored records will be extracted during the next delta run. This safety interval allows for the accurate capture of delta in a situation when records are stored with some delay. Make sure that the value is high enough. If you specify a delay of 10 seconds, but storage may be delayed by 60 seconds, the system would miss such records. In the current run the record is not yet stored, and in the following run, the system no longer checks for this timestamp.
• @Analytics.dataExtraction.delta.byElement.detectDeletedRecords: By using this annotation, the system will remember all key combinations of the view that were extracted in delta mode. If a key combination does not occur in the view anymore, this will automatically generate a delete image in the extracted data.

  However, if you archive the data, this might appear as a deletion to the CDS-based ODP extraction framework. In data warehouse scenarios, you usually do not want these archiving deletions on the source system side propagated as "deletions" in your data warehouse. If database records in your source system were archived after one year, you can specify an additional annotation.

• @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.

Using a delta field, the ODP framework determines up to which record a data consumer has already extracted records. On a subsequent extraction request, only records with a higher date/time stamp are collected for extraction. For 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\u0002 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 interval can accommodate technical delays like waiting for a database commit on the application system side. A record with a timestamp 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 framework 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 are stored for later comparison. In the subsequent extraction run 2, records belonging to this time interval are 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 are not extracted again. With the annotations so far, you get newly created records and 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. When you include this annotation, all record key combinations being extracted are stored 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.

Note that this concept is only feasible for low-level volumes of data (~ fewer than 1,000,000 data records) and must not be used for high-volume data applications. Hence, this mechanism is 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.

You can use Change Data Capture (CDC) method as another option for real-time delta extraction by adding the annotation @Analytics.dataExtraction.delta.changeDataCapture.automatic:true. Besides targets in SAP BW/4HANA with real-time requirements, this is especially useful if your target is an SDI or SAP Data Hub data pipeline.

The source system must be at least SAP S/4HANA Cloud 1905 or SAP S/4HANA 1909 FP01 (based on SAP NetWeaver 7.54) on Premise. From a technology point of view, this delta method uses real-time database triggers on table level to determine deleted, updated, or inserted records. Depending on settings, INSERT, UPDATE, and DELETE database operations can be recorded by this framework. To function properly, the key fields of all underlying tables must be exposed as elements of the CDS view and be mapped accordingly.

The framework records the key field values of the changed table rows. For an INSERT or UPDATE database operation, a scheduled job is selecting the records, based on these key combinations from the CDS view. It pushes the records as complete records into the Operational Delta Queue (ODQ). In the case of a DELETE database operation, the job generates an empty record with just the key fields filled, sets the deletion indicator, and hands them over to ODQ.

The task for exposing a CDS view with the CDC delta method is providing 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 then a consistent selection/reconstruction of records to be provided for extraction. This means that the framework must know which tables to log: that is, monitor for record changes. As one record changes in possibly only one of the underlying tables, the framework must determine which records are affected by this change in all other underlying tables. Also, the framework must provide a 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 database triggers is used.
• @Analytics.dataExtraction.delta.changeDataCapture.automatic: In the case of simple CDS views like projections, no mapping information needs to be provided, but the relevant information about key fields and so on are inferred by the system.
• @Analytics.dataExtraction.delta.changeDataCapture.mapping: In the 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.
• @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: 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.

You might only want to track certain values that you deem relevant for your extraction. In this case, 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.