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.
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.
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+
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
3. 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”.
No comments:
Post a Comment