Skip to Content
Technical Articles

BW Extractors, CDS Views Extraction into Native HANA via SDI – Part I

Hi All,

In this blog we are going to cover 2 scenarios of data extraction from S/4HANA to Native HANA system using SDI.

  1. Data Extraction from BW Extractors to Native HANA system
  2. Data Extraction from CDS views to Native HANA system (Blog Part 2)

In Part of the blog we are going to cover Data Extraction from BW Extractors to Native HANA system using SDI.

Requirement – To Extract BW Extractors Data from S/4HANA to Native HANA system.

Pre-requisite

  • Setup Data Provisioning Agent and register the same in Native HANA system
  • Connecting Native HANA sytem to S/4HANA system using SDI with ABAP Adapter.
  • BW Extractors should be ODP Enabled as ABAP Adapter supports the ODP Framework.

Detail Steps

We will be connecting HANA system (HW4) with S/4HANA system (PLS).

SDI & DP Agent

Once the SDI and DP agent setup is done you would see your ABAP Adapter based connection s(PLS100) under Provisioning –> Remote Source

  • Remote Source PLS 100 – We can see that it supports extraction from ABAP_CDS and SAPI (BW extractors)

Remote%20Source

Remote Source

 

  • ABAP Adapter connection –

ABAP%20Adapter%20settings

ABAP Adapter settings

 

  • Now we have to create a Virtual Table (VT_2LIS_02_ITM) on extractor 2LIS_02_ITM using context menu, which will act as a source for loading data into my target table. It was created under schema INF627126. Virtual table shows you the extractor data virtually in Native HANA system.

Virtual%20Table%20on%20Extractor

Virtual Table on Extractor

  • Create Target table where you want to persist the data.¬† There are multiple ways of creating a column table. For POC we have used Virtual table as template and used the SQL console to create table. Create column table “02_ITM” as (select * from “INF627126″.”VT_2LIS_02_ITM”). You can see the table in the above screen shot. You would¬† use HDBCDS/HDBCDS to create table to transport to other environments.
  • Now we have to create Flow Graph, it’s like creating a Process Chain in BW. This will load the data from source to target and you can apply transformation using different options available in the pallete. You should login to WEB IDE Editor using the link http://<WebServerHost>:80<SAPHANAInstance>/sap/hana/ide/editor

Flow%20Graph%20Creation

Flow Graph Creation

  • In Flow Graph Use Data Source and choose the Virtual Table as shown in the below Pic.

 

Data%20Source%20in%20Flow%20Graph%20-%20Virtual%20Table%20Selection

Data Source in Flow Graph – Virtual Table Selection

 

  • We have to maintain the settings in the Data Source as shown below –

Data%20Source%20Settings

Data Source Settings

Note we have chosen Delta Extraction, Extraction Name is important as this name will be shown in ODQMON as subscriber name.

  • Drag a filter on canvas, Flow Graph will look like as below at the end. Filter is in the middle section. You can think of Filter as the projection node in HANA View, you can apply filter or create calculated columns in it.

Filter%20Selection

Flow Graph

 

  • Choose Output Type – And select the Target table which we created earlier.

Target%20Table%20Selection

Target Table Selection

Target%20Table%20-%20Update%20Type%20-%20Insert

Target Table – Writer Type – Insert

  • Save the Flow Graph.
  • Now Fill the Setup table for Purchasing (02) Application component and check the number of records in RSA3.

RSA3_Setup_Table_Entries

RSA3_Setup_Table_Entries

  • Execute the flow graph and check the entries in the target table.

Target%20Table%20Entries

Target Table Entries

  • Check the ODQMON transaction for Extractor entry, subscriber name, init with data transfer and the number of records.

ODQMON%20-%202lis_02_itm%20-%20Init%20with%20data%20transfer

ODQMON – 2lis_02_itm – Init with data transfer

  • Let’s check the delta flow – Example with Purchase order 4500000001
  • Purchase Order Quantity in Target table – using data preview

Purchase%20Order%20-%204500000001

Purchase Order – 4500000001

  • Check the PO in ME23n for Order qty.

ME23n%20-%20PO%20Qty

ME23n – PO Qty

  • Change the PO Qty from 32 to 40 and save the same.

Change%20PO%20Qty.jpg

Change PO Qty

  • Check the entry in LBWQ – Extraction Queue which will save the delta entries

LBWQ%20-%20Extraction%20Queue

LBWQ – Extraction Queue

  • Run the V3 Job to push the data from Extraction Queue to ODQMON. You can run the Program RMBWV302 for the same.
  • Execute the Flow Graph
  • Check the target table entry – PO Qty changed to 40.

Target%20Table%20-%20PO%20Qty%20Delta%20Change

Target Table – PO Qty Delta Change

  • Check ODQMON for delta data

Delta%20Records%20in%20ODQMON

Delta Records in ODQMON

 

How to automate the Flow Graph Job

Please follow the below HANA Academy’s youtube link which gives all the information you would need to automate the Flow Graph job.

 

What happens when you have to Enhance the Extractor

In our example if we have to enhance the extractor 2LIS_02_ITM – We found that the virtual table starts throwing error once the extractor is enhanced. We did not find any way to refresh the virtual table to reflect the new enhanced field.

Possible Workaround

  • Create new Virtual table (VT1)
  • Delete the Source (i.e Virtual table) in the Flow Graph
  • Select the source again with the new Virtual table (VT1)
  • Keep the Extraction name same in the Source properties of Virtual table in Flow Graph as it was earlier
  • Delete the filter used in the Flow graph
  • Create New Filter and remap source & target to automatically reflect the new enhanced field available in the source or manually add the new field in the old filter.
  • Modify target table for the new enhanced field of the Extractor.
  • Execute the Flow graph with delta settings as it was before.
  • Delta seems to work fine with the new enhanced field

Please note to transport the enhanced extractor to other environments i.e. from Dev -> Qua -> Prod you must follow the pre-requisite steps to the transport of enhanced extractor. One can google and find these steps easily if you don’t know.

 

Issue/Challenges with HANA Table Writer Type INSERT-

When we keep the Writer Type insert we preserve all the records sent by the extractor, but it has its own issues with it.

  1. With the delta entries target table will keep on growing (for the same key records you will have multiple entries in the target table, which when aggregated will show the correct result).
  2. Deleted records will still be there in the target table. You can identify these records with ROCANCEL – R

Alternative Solution with HANA Table Writer Type UPSERT

We can keep the writer type UPSERT which will update the existing records of the table based on the PRIMARY KEY and insert the new records.

Below are the observations when we keep the target table writer type as UPSERT.

  • One should understand the extractor and create the Primary key of the HANA table accordingly. For BW consultants, it should ideally be the Key of the ADSO. Note for the UPSERT to work target table must have the Primary key.
  • All the deletion records gets overwritten with ROCANCEL ‘R’ and it’s easy to filter the Deleted records.
  • Whenever their is change in any record by default before image (ROCANCEL as ‘X’) comes first followed with After Image (ROCANCEL as ‘blank’). After image record overwrites the before image in the table and the final changed value is persisted in the Target HANA table.

In this way using Write type UPSERT reduces the total number of records in the target table compared to Writer type INSERT. For BW consultants (It almost behaves like ADSO with only active table).

All the suggestions are most welcome, request all to share their experience.

References

  1. https://blogs.sap.com/2020/03/16/access-sap-erp-data-from-sap-hana-through-sdi-abap-adapter-2/
  2. https://blogs.sap.com/2019/03/25/extraction-from-sap-s4hana-and-other-abap-sources-into-hana-sql-dw-with-smart-data-integration/

Mayank Jaiswal & Hemant Patidar

(Authors)

1 Comment
You must be Logged on to comment or reply to a post.