Skip to Content
Author's profile photo Vijay Sharma

Delta Extraction and Real Time Streaming ODP-CDS to BW

There has been a shift in the way business applications are developed with the availability of SAP HANA. Idea is to move logic (code) as much as possible to the Database so as to get performance benefits. To simplify this code push down to database, SAP has provided new data modelling technique called Core Data Services (CDS).

SQL based data definition is used to define CDS views with additional concepts of associations (similar to joins) and annotations. To know more about CDS annotations click here.

CDS has become the keystone for application development as this can be used to build core transactional functionality as well as analytical content for reporting.

In this Document we will see how CDS views can be used for Delta Data Extraction or Real Time Streaming to BW

Pre-requisites

BW data extraction is not supported by S4HANA 1511. Minimum system requirement is S4HANA 1610 or Higher. With 1610 SAP has introduced some new annotations to facilitate data extraction to BW using CDS views.

Source Requirements

Full Data Extraction or Direct Access: Minimum of NetWeaver 7.5 SP04 or equivalent.

Delta Extraction: Minimum of NetWeaver 7.5 SP05 or SAP BW/4HANA SP01

Target Requirements

Minimum of NetWeaver 7.3 SP08 or equivalent (ODP recommendation)

For the extraction of data into BW, ensure an ODP source system of type ABAP_CDS is available to connect to the source system.

Extraction shown in this document is done on system on S4HANA 1610 with embedded BW components.

Delta Extraction of CDS-ODP source to BW

In this example Delta extraction will be shown for changes in the FI documents. CDS view to fetch the delta records for any changes in the FI document with object class ‘BELEG’ or ‘BELEGMASS’ or ‘BELEGR’ will be created and used for the same.

Create Delta enabled CDS View

  • Go to the Eclipse editor and create a CDS View.

  • Provide CDS View details

  • Select The Transport number

  • CDS view with blank template will be generated

  • Provide the SQL View name (with this name view will be created in back-end system) and write the view query logic. Make sure to use the Analytics annotations for the element/field to be considered for delta identification (it could be Date or timestamp type filed)

  • As we need timestamp field (for multiple time execution in a day) for delta extraction and we don’t have the same in CDHDR table, convert the change date and change time into time stamp and cast it to timestamp format (By default the converted field is created of type Char). Also use Semantics annotation to enable the filed for delta extraction as shown below.

  • Activate the CDS View. It will generate the View in backend system with the name provide in step 5.

  • The timestamp field we created in our view will be created with data type DEC. But nothing to worry, it will be work as timestamp only.
  • Execute the View to see the content and verify the generated timestamp field

        

  • With this, we have created our delta enabled CDS view.

 

Create a BW data source

  • Open the BW perspective in eclipse and create a BW Project. Under the Data Sources tab in left hand side window, you should see your source Logical system under ODP-ABAP CDS views node

  • Under the Logic system you will see the node for different application areas. Select the node under which you want to create your Data Source. Right click on the node->New->Data Source

  • You will get the below pop up window with default values for BW project and Source System. Click Next.

  • In the next popup window select the “Proposal from operation Data Provider” radio button and click Next.

  • In the next popup window, select the CDS View created in earlier steps and Click Next.

  • Enter the Data Source description and click Finish

  • Data Source created as shown below. Click on Activate Icon to activate the Data Source.         

 

  • Once Activated, click on “Extraction” tab highlighted in above step. You can the Data Source is created with Delta Process “After Images via Delta Queue (FI-AP/AR for example).

  • Right click on the Data Source in the light side window and click Replicate.

  • Select your Data Source and click Next

  • Click Finish

  • Job with Successful replication will be displayed. Refresh the screen if job is not yet finished.

  • We have successfully created a DataSource for our CDS View.

Create Advanced Data Store Object

  • GoTo BW Repository Node in the Left side window. Select a Repository and right click on it to create New Data Store Object

  • Enter the Details as shown below and Click Finish

  • Advanced Data Store Object is created. Click on Active to activate the same.

Create Transformation

  • Right Click on Data Store Object and click on create Transformation

  • Provide the details of the Data Source in the next window

  • Transformation is created with Proposed mapping between Data Source and DSO. Activate the Transformation by clicking on the Activate Icon.

  • Transformation is activated Successfully as is shown in Version

Create Data Transfer Process (DTP)

  • Right Click on DSO and click on New->Data Transfer Process

  • On the next screen details are be filled by default. Click Continue.

  • DTP is created. Activate the same.

  • DTP must be activated Successfully.

Create Infopackage

  • Open TCODE RSA1 in backend system and under the Data Source Node select the Data Source “ZDELTA_ACDOCA” we created.

  • Right click on the Data Source and Click Create Infopackage

  • Enter the InfoPackage description, select the Data Source and click Save.

  • Open The Update Tab and select the Full Load Radio Button and start the extraction from schedule tab.

  • Go to the update tab and select Initialize the delta process

  • GoTo Schedule tab and start the extraction

  • Monitor the extraction. All the records are fetched and the delta processed is analyzed. This means now with Delta run only delta records should be fetched.

  • Now let’s make a change in one the document. Doc Header text is changes as shown below.

  • Go to the Update tab in the infopackage and select the Delta radio button

  • GoTo Schedule Tab and start the extraction.

  • Monitor the Extraction. We can see there are 21 records are extracted.

  • GoTo TCODE ODQMON and see the Documents which are changes to verify if the extract is corresponding to the changes we made. As seen in screen shot below all the 21 records are for the documents we changed.

Enabling Real-Time Streaming

  • Go to SAP Gui and open TCODE RSPC

  • Go to Process chain, right click on the top node and click create Process chain

  • Provide the Process chain name and long description

  • Click on Create

  • Provide Process variant name and description

  • Maintain Start process screen will appear. Click on Save.

  • Add the DTP and aDSO activation processes from the Left side window to the Process Chain by dragging and dropping to the process chain highlighted below. The connect the Variant to DTP by dragging Variant to DTP

  • Click on streaming button.

  • Select the ‘Process Chain Runs in Streaming Mode’ check box. Set the Error threshold and Log retention according to requirements. Click OK

  • Activate the process chain by clicking on Activate icon

  • Process chain is activated successfully and the Events are created for the same as highlighted below

  • With this we Real time streaming is enabled for the Data flow and we see a Data extraction job is triggered and waiting for any changes. Now let’s check if data changes are getting reflected at real time.

  • Let’s make the changes in same Document header text again and save.

  • Check in tcode ODQMON. As we can see these is one Real time extraction request. Double click on the same

  • There are 105 rows extracted. Double click on the row.

  • We can see the entries for the change we made are there in the extracted records

With this we have completed the Real Time Data Streaming to BW using the CDS view.

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mark Thienpont
      Mark Thienpont

      Hi Vijay,

      Great blog !! Tx.

      Any idea where to find business content CDS-based extractors, with the above described possibilities enabled ?

      We're now still using 2LIS-extractors, but might want to go for a more modern approach :-).

      Many thanks in advance,

      Mark

      Author's profile photo Vijay Sharma
      Vijay Sharma
      Blog Post Author

      Thanks Mark!

      I don't know if SAP has provided any standard CDS based extractors. I believe, we need to continue using 2LIS-extractors and for cases where 2LIS-extractors have been belittled or can’t be used due to modifications in the S/4HANA system,we can use the above explained method.

      Thanks

      Vijay

       

      Author's profile photo Former Member
      Former Member

      Good One Vijay and it took long time to capture the screen shts

      Author's profile photo Anuradha Patil
      Anuradha Patil

      will it work for Delete/Cancel postings also for delta extraction?

      Author's profile photo Vijay Sharma
      Vijay Sharma
      Blog Post Author

      Hi Anuradha,

       

      You need to use additional annotation to capture or ignore deleted records

      Analytics.dataExtraction.delta.byElement.detectDeletedRecords 
      It can be have value true or false depending on if you want to include or ignore deleted records.

       

      Author's profile photo Jitin Khanna
      Jitin Khanna

      Nicely depicted Vijay. Keep Sharing more insight on new features.

      Author's profile photo André Gentilini
      André Gentilini

      Hello Vijay,

      thx for the nice document.

      I have a question further to CDS Views vs. Classic extraktors (API).

      Do you think that we should not use CDS Views instead of Standard Extraktors for new implementations(FI/SCM and so on)?

      I ask because I thougth, that CDS Views were now the future of Extracting Data to the BW, I can not find a Document from SAP what the best practises are (S/4 HANA with stand alone BW).

       

      thx for your Answer.

       

      Cheers, André

      Author's profile photo Vijay Sharma
      Vijay Sharma
      Blog Post Author

      Hi Andre,

      You should go for CDS based extraction only as that is how it is going to be. No classic extractors anymore.

       

      Regards

      Vijay

       

      Author's profile photo Abhijit Nilegaonkar
      Abhijit Nilegaonkar

      Nice write up Vijay. Thanks for taking time to work on it. It was hard to see the image and code. I did code however the LastChange Field is showing up as 0 in my case. What am I missing? Or would it be possible for your to update the text version of your CDS?

       

       

      Here is my complete text.

      @AbapCatalog.sqlViewName: ‘ZDELTAACDOCA’
      @AbapCatalog.compiler.compareFilter: true
      @AccessControl.authorizationCheck: #CHECK
      @EndUserText.label: ‘Delta CDS View for ACDOCA Abhijit’
      @Analytics.dataExtraction.enabled: true
      @Analytics.dataExtraction.delta.byElement.name: ‘lastChangedAt’
      @Analytics.dataExtraction.delta.byElement.maxdelayInseconds: 300

      define view ZDELTA_ACDOCA as
      select from (

      ( cdhdr
      inner join fiscds_trk_doc_01 on cdhdr.objectid = fiscds_trk_doc_01.objectid
      )
      inner join cdpos on cdpos.objectclas = cdhdr.objectclas
      and cdpos.objectid = cdhdr.objectid
      and cdpos.changenr = cdhdr.changenr
      )
      {
      key CompanyCode,
      key Ledger,
      key AccountingDocument,
      key cdpos.changenr,
      key cdpos.tabname as TableName,
      key cdpos.tabkey,
      key fname,
      FiscalYear,
      PostingDate,
      DocumentDate,
      udate as ChangedOn,
      username as ChangedBy,
      utime as ChangedAt,
      value_old as OldValue,
      value_new as NewValue,
      CreationDate,
      CreationTime,
      @Semantics.systemDate.lastChangedAt: true
      cast( dats_tims_to_tstmp( udate,
      utime,
      abap_system_timezone( $session.client, ‘NULL’),
      $session.client,
      ‘NULL’ ) as rvlsttstmp)as lastChangedAT
      }
      where
      (
      cdhdr.objectclas = ‘BELEG’
      or cdhdr.objectclas = ‘BELEGMASS’
      or cdhdr.objectclas = ‘BELEGR’
      )

      and cdpos.fname != ‘AEDAT’
      and cdpos.tabname != ‘ACDOCA’
      and cdpos.tabname != ‘BKPF’
      ;

       

      Author's profile photo Satyam Kala
      Satyam Kala

      Change the below lines as mentioned:

      cast( dats_tims_to_tstmp( udate,
      utime,

      ======>>

      cast( dats_tims_to_tstmp( CreationDate,
      CreationTime,

      Author's profile photo Juan Manuel Grilo
      Juan Manuel Grilo

      Hi Vijay,

      How Did you configure the Start process within the chain in this case?

      I mean, This is a push based scenario right? So I guess it is not necessary to schedule the start process every minute as We would do in a Pull based scenario.

      Thanks in advance,

      Juanma

       

       

      Author's profile photo Vijay Sharma
      Vijay Sharma
      Blog Post Author

      Hi Juanma,

      I have shown both the PUSH and pull scenario. In case of real time streaming (push), the start event is triggered by any change in data and is configured when i have activated the streaming.You are right , in case of pull, it is not required and as is shown in first section of the blog.

      Thanks

      Vijay

      Author's profile photo Praveen Vemuluri
      Praveen Vemuluri

      Hi Vijay,

      Nice Blog. As ACDOCP is having REQTSN instead of Timestamp, although we can calculate timestamp from reqtsn, it may result in data loss/duplicate. Is there a way we can make this possible for ACDOCP delta.

      Regards,

      Praveen

      Author's profile photo Vijay Sharma
      Vijay Sharma
      Blog Post Author

      Hi Praveen,

      As I showed in my scenario, use CDHDR date and time to create timestamp and use the same to fetch the delta records.

      thanks

      Vijay

      Author's profile photo Neville Woods
      Neville Woods

      Hi Vijay - thanks for sharing this very useful information.

      for the @AccessControl.authorizationCheck: #CHECK annotation did you need to create a DCL? is there any reason why this can't be set to #NONE?

      excellent blog

       

      Regards

      Nev

      Author's profile photo Venkata Subbareddy Sana
      Venkata Subbareddy Sana

      Hi vijay,

      I have created the CDS VIEW based our requirement but i am unable to replicate the CDS VIEW into eclipse BW.

      I have select OPD_ABAP cds source system .

       

      please check my CDS VIEW .

      i am using below annotations.

       

      @AbapCatalog.sqlViewName: 'zcds_bw'
      @AbapCatalog.compiler.compareFilter: true
      @AbapCatalog.preserveKey: true
      @AccessControl.authorizationCheck: #CHECK
      @ClientDependent: true
      @EndUserText.label: 'CDS view for service WIp report'

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

      define view zcds_bw_cds

      please suggest from where did i mustache.