Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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

former_member187192
Participant
‎08-03-2020 9:56 AM

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 Source


 

  • ABAP Adapter connection -



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 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 Graph Creation




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


 


Data Source in Flow Graph - Virtual Table Selection


 

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



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.



Flow Graph


 

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



Target Table Selection



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




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



Target Table Entries




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



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 Order - 4500000001




  • Check the PO in ME23n for Order qty.



ME23n - PO Qty




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



Change PO Qty




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



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 Table - PO Qty Delta Change




  • Check ODQMON for delta data



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.

https://www.youtube.com/watch?v=zzHPMLyTFV0&feature=youtu.be

 

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


Mayank Jaiswal & Hemant Patidar

(Authors)
2 Comments
Labels in this area
Popular Blog Posts