Skip to Content
Technical Articles
Author's profile photo Sefan Linders

Extraction from SAP S/4HANA and other ABAP sources into HANA SQL DW with Smart Data Integration

Have you ever tried to stage data from an ABAP source system into native SAP HANA or the SAP HANA SQL Data Warehousing solution? Have you tried leveraging BW extractors or CDS Views to do this? With the Feature Pack 1 update of SAP Web IDE for HANA, launched end of October 2018, extraction from ABAP source systems like SAP S/4HANA has become a lot easier. Flowgraphs in the SAP Web IDE now support ODP, complete with delta load support. The same goes for the Full Stack Web IDE on the SAP Cloud Platform. This blog describes how you can use these features.

When and why would you extract from ABAP to the SQL DW?

First, let’s make clear the use case. The SAP HANA SQL DW  provides you with a complete DW toolset in line with the SQL approach. SAP positions the SQL DW next to SAP BW/4HANA, allowing you to run them individually or together as one data warehouse. Where the SAP HANA SQL DW is a relative new offering, SAP BW has a long and known history. That also goes for the extraction of data from ABAP based SAP source systems. If you are running that scenario with SAP BW, and see no need to change this, then you are perfectly fine. The functions described here are for the customers that have a use case for direct extraction of ABAP sources to their SAP HANA SQL DW or other use cases where the data needs to land in SAP HANA.

How did people load from ABAP to SQL DW before?

The need for loading from ABAP based source systems to non-ABAP systems is not a new requirement at all, and SAP supports this for quite some time already. You had the following options:

  1. Direct extraction from the database source tables: can be the fastest data transfer option, but you are then bypassing extractor logic, and as you directly access the source database, you might violate database license restrictions.
  2. Using SAP Data Services, which supports the ODP protocol to load via the ABAP application layer.
  3. Smart Data Integration (SDI), which also supports the ODP protocol, but which is built straight into SAP HANA. This is where the functionality has been extended.

Using SDI, you can create virtual tables pointing to your ABAP source object, like a CDS View or BW extractor. You can fire regular SQL statements on that table and supply the ODP parameters with it. For example, the following statement would create a delta queue with name “Q123” in the ABAP source system for a BW extractor and initialize the data load. The next time you would run it, it would fetch the delta’s (see the SAP SDI help page for the full syntax).

select * from RANDOM_VIRTUAL_TABLE T
with dataprovisioning parameters
('<PropertyGroup name="__DP_TABLE_OPTIONS__">
<PropertyGroup name="T">
<PropertyEntry name="extractionmode">D</PropertyEntry>
<PropertyEntry name="extractionname">Q123</PropertyEntry>
</PropertyGroup>
</PropertyGroup>');

Yes, you read that right. SDI already supported ODP, and it supported already handling deltas. But you usually do not just fire SQL statements to fetch data, you want to make the extraction part of the regular ETL tooling. And those are flowgraphs. Now, flowgraphs in XS classic supported the ODP parameters as already. But, XS classic is deprecated, and its successor Web IDE contains a flowgraph editor, but did not support those ODP parameters. So, in theory, you could use a flowgraph to load from a virtual table pointing to a BW extractor, but you wouldn’t be able to leverage the ODP settings like delta-enablement. Or worse, with bypassing that ODP framework, loading from ODP sources would not package the load, which wouldn’t work well with large data sets. You could again work around that by using the above SQL statements, but it wouldn’t be easy.

What is ODP?

SAP S/4HANA, or any other operational system based on ABAP technology, provide “CDS views” and  “Extractors” (also called “SAPI DataSources”), which allow for easy extraction of data meant for analysis. The extractors contain pre-delivered business logic that’s built into the source system, to fetch the right data and preprocess it, and many have a built-in delta mechanism to deliver only the changes. While this technology was originally targeted for use with SAP BW, since the arrival of Operational Data Provisioning (ODP), it has become pretty easy to load data from these extractors to other targets than SAP BW.

If you don’t know what ODP is and would like to know more, then read the FAQ and introduction on ODP: https://blogs.sap.com/2017/07/20/operational-data-provisioning-odp-faq/

It might be good to note that if you are searching just for extraction from ABAP tables, that ODP won’t help you there, unless you wrap an extractor around it. Extraction from ABAP tables is also supported by SDI, it also leverages the ABAP adapter to do it, but does not leverage ODP technology.

Software requirements

The Full-stack Web IDE on SAP Cloud platform would need Smart Data Integration turned on in SAP HANA Service. For an on-premise SAP HANA installation, the requirements are as follows:

  1. HANA SPS03 revision 33 or higher
  2. Web IDE SPS03 Feature Pack 1 (4.3.57) or higher.
  3. SDI Data Provisioning Agent SPS03+

The source system should adhere to the requirements posed in SAP Note 2232584 – Release of SAP extractors for operational data. Prerequisites for ODP are also described in the earlier mentioned FAQ: https://blogs.sap.com/2017/07/20/operational-data-provisioning-odp-faq/

Walk through

What follows is a walk through of configuring flowgraphs to load from an ODP source. The below is what I had already set up, so these steps will be skipped:

  1. HANA system with XS Advanced and Web IDE
  2. SDI data provisioning agent
  3. Remote source pointing to your SAP source system, using the SDI ABAP adapter
  4. In the Web IDE, a project containing a HANA database module, where the project has privileges on the remote source, for example through a grantor service.

Set up a source sample

SAPI / Generic extractor

I don’t have a “real” source system at my disposal, so instead I will use an SAP BW 7.50 system to set up a generic extractor. In SE11, I created the following table.

Then, I insert some data to test table. I used the SQL editor in ST04 for this.

TRUNCATE TABLE ZSOHEADER;

INSERT INTO ZSOHEADER VALUES(
	1 /*SalesOrderID */,
	'20180412'/*OrderDate */,
	'Muhammed MacIntyre'/*CustomerName */,
	'HT-1036'/*ProductName */,
	6/*OrderQuantity */,
	261.54/*Sales */,
	'20180412'/*ChangeDate */
);
INSERT INTO ZSOHEADER VALUES(
	2 /*SalesOrderID */,
	'20180413'/*OrderDate */,
	'Sheldon'/*CustomerName */,
	'HT-1037'/*ProductName */,
	13/*OrderQuantity */,
	34.00/*Sales */,
	'20180413'/*ChangeDate */
);
INSERT INTO ZSOHEADER VALUES(
	3 /*SalesOrderID */,
	'20180414'/*OrderDate */,
	'Marc'/*CustomerName */,
	'HT-1040'/*ProductName */,
	12/*OrderQuantity */,
	99.00/*Sales */,
	'20180415'/*ChangeDate */
);

Then I created a generic extractor ZDS_SALESORDERHEADER in transation RSO2, and put a numeric pointer on field SALESORDERID where the delta is then based on. I’m assuming an increasing SALESORDERID, so new records will always have a value higher than the ones already extracted.

CDS View

I also created a CDS view, but this one with a delta pointer at the CHDAT (change date) field.

@AbapCatalog.sqlViewName: 'CDSV_SOHEADER'
@AbapCatalog.compiler.compareFilter: false
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'CDS View SalesOrderHeader'
@Analytics:{dataCategory:#FACT ,
dataExtraction.enabled:true}
@Analytics.dataExtraction.delta.byElement.name:'CHDAT'
@Analytics.dataExtraction.delta.byElement.maxDelayInSeconds: 5

define view Z_CDSV_SALESORDERHEADER as select from zsoheader {
key salesorderid,
orderdate,
customername,
productname,
orderquantity,
sales,
@Semantics.systemDate.lastChangedAt: true
chdat
}

Browse remote sources in Web IDE / DB Explorer

When using the Database Explorer in the SAP Web IDE, we can see both the Extractor and the CDS View.

Create virtual tables in SAP Web IDE

Since the process for CDS views and Extractors from here is equal, I will only continue with the Extractor as an example to load data to HANA. In the SAP WebIDE project, I create a new design time virtual table that points to the source object.

After building the virtual table, we can see the data in the virtual table. You can also see that I made these screenshots a while ago ;-).

Create flowgraph

Let’s create a flowgraph that loads data from the virtual table, and that makes use of the delta options that are available now in SAP WebIDE flowgraphs.

Choose a source.

Then, navigate to the “Custom parameter” that pops up after selecting the virtual table as a source. Set the parameters as below. Of course, you can choose what Extraction name you want to use.

To have some proof at which times which records are loaded, I’ve added a Load Timestamp to a projection node, that writes the current_timestamp into the target table.

The flowgraph now looks as follows.

Run the flowgraph

Let’s run the flowgraph from the flowgraph editor.

We can see that all three records are retrieved.

Now I insert two more records into the source table, to test the delta mechanism.

INSERT INTO ZSOHEADER VALUES(
4 /*SalesOrderID */,
'20180415'/*OrderDate */,
'Esther'/*CustomerName */,
'HT-1060'/*ProductName */,
16/*OrderQuantity */,
299.00/*Sales */,
'20180418'/*ChangeDate */
);

INSERT INTO ZSOHEADER VALUES(
5 /*SalesOrderID */,
'20180416'/*OrderDate */,
'Dirk'/*CustomerName */,
'HT-1090'/*ProductName */,
340/*OrderQuantity */,
9.00/*Sales */,
'20180520'/*ChangeDate */
);

After running the flowgraph again, you can see the new records being loaded, with a different Load Timestamp than the previously loaded records. That shows our delta load ran successfully.

Task run overview

In the Task Overview monitor in the Database Explorer, we can see the details of the two different loads.

In the source system, we can navigate to the ODQ (Operational Delta Queue, transaction ODQMON) monitor. Here we can see the two extractions as well.

Zooming in, you can see that the first load is marked as Extraction Mode “Initial Data” load, and the subsequent load as “Data Changes”.

Conclusion

This post explained how you can load data from ABAP-based SAP source systems, and how the SAP Full-stack WebIDE and the SAP WebIDE for HANA have been extended with support for doing this using flowgraphs that support the ODP protocol. Please feel free to leave any questions or comments.

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Karen Avendano
      Karen Avendano

      Good afternoon Sefan.

      I am trying to do this same process in Sap Cloud Platform with SDI (Neo Environment) and we do the whole process that is seen in this blog to the ERP with the extractor 0MATERIAL_TEXT when we execute the flowgraph it comes out successful and loads the data but does not create the mark in The ODQMON Delta initialization only shows as if it were a full load.

       

       

       

       

       

      Author's profile photo Sefan Linders
      Sefan Linders
      Blog Post Author

      Is 0MATERIAL_TEXT a delta-enabled extractor?

      Author's profile photo Karen Avendano
      Karen Avendano

      Hi Sefan

       

      If the extractor of 0material_text is enabled for Delta and is ODP?
      Author's profile photo Sefan Linders
      Sefan Linders
      Blog Post Author

      In that case, this could be a bug and you should log an incident. To pinpoint the issue further, you could try the SQL code from the blog to see if the SDI and ABAP backend work properly. If that generates delta’s, the issue is likely with the SDI flowgraph or task framework.

      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally

      Another nice Blog Sefan, Thank you.  I got a question on what is currently possible in XSC Flowgraph Vs XSA Web IDE Flowgraph in terms of extracting from S/4 HANA Extractors/CDS views.  So with SDI ABAP adapter, we can extract the data from a BW datasource/CDS view into HANA even in XS classic Flowgraph, but Delta extraction is not possible. So with XSA Web IDE flowgraph it is possible now correct?

      Secondly, Can we do a real time(CDC) replication of data while extracting the data from S/4 HANA CDS view, instead of doing a batch delta processing?

      Author's profile photo Sefan Linders
      Sefan Linders
      Blog Post Author

      In XSC, flowgraphs also support delta's on ODP.

      Real-time replication from HANA CDS Views would be possible if you replicate the underlying tables and recreate the views, or leverage a solution that does this for you like described in the following blog for SCP: https://blogs.sap.com/2017/12/08/replicate-abap-cds-views-from-sap-s4hana-cloud-to-sap-cloud-platform/

      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally

      Thanks Sefan, So in this blog, you are providing an update that, delta's with ODP was not initially supported in XSA, but now it is, right?

      Secondly, I have gone through the blog, but describes about a scenario of S/4 HANA Cloud  -- > SCP HANA instance. But in my case it is S/4 HANA On Premise -- > HANA On premise instance, would that be possible?

      Author's profile photo Sefan Linders
      Sefan Linders
      Blog Post Author

      You're right, this blog describes an update on XSA.

      For your second question, it's probably better to ask the same question in that blog.

      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally

      Perfect, Thanks Sefan. I got it now.

      Author's profile photo Dharmateja Nandipati
      Dharmateja Nandipati

      Hello Stefan,

      Did you get a chance to try pulling data from ODP enabled standard transactional  extractor example 2LIS_11_VAHDR to HANA using SDI?

      We are on HANA 2.0 SP03 also we have HANA 2.0 SP02 will it work for both? We have the source system ECC on Oracle DB can we pull the data from the said source system to HANA DB using SDI?

      Author's profile photo Sreekanth Surampally
      Sreekanth Surampally

      Hi Sefan, I have got another question., when I got 2 options to pull data either from Business content Extractors or directly from ABAP tables. If my reporting requirement is more standard and it is fulfilled by Extractor logic, then ODP-extractor option is more feasible one than other right?

      Or if there is going to be any change in the future on the way extractors work., it is better to extract the data from ABAP tables to HANA and then implement logics in HANA?  Can you suggest on this please?

      Thanks

      Sreekanth

      Author's profile photo Sefan Linders
      Sefan Linders
      Blog Post Author

      Extractors can offer delta load functionality and additional business logic that you might need. Table extraction does not provide delta loads out of the box. No reason to use table extraction if extracties already fulfill your need and I’m not aware of a change that would alter that advice.