Make the Most out of SAP Delta Extractors - High Performing SAP ETL to Cloud

/

Background

SAP Extractor, or “BW Extractor” in the old terminology, is one of the valuable data management features from SAP, which is used to retrieve chunky data in SAP source systems to feed downstream data warehouse and business intelligence systems. The origination of SAP extractors was to feed SAP BW applications. To those SAP customers who do not use BW, being able to integrate with SAP extractors is a highly sought after feature, yet there lacks satisfactory solutions in marketplace.

With AecorSoft Integration Service, it is now possible to allow SQL Server Integration Services to seamlessly integrate with SAP extractors, and send both full and delta data to either on-premise database or cloud data platforms. AecorSoft Integration Service is able to intelligently detect the delta capability of extractors, to allow data extraction activities like init with data transfer, init without data transfer, and delta data recovery.

A sample SSIS data flow with SAP extractor source is like this:

Full Data Extraction

First, not all SAP extractors are delta enabled. In fact, a large number of master data extractors and some transactional data extractors are full-only. For example, Company Code extractor 0COMP_CODE_ATTR and General Ledger Transaction Figures extractor 0FI_GL_1 are both full-only.

Second, for delta extractors, full loads are required to retrieve historical records or obtain a specific segment of data.

Setting up full-pull is straightforward: in the dialog box of AecorSoft Extractor Source component, pick the extractor and set the extraction mode to “F”:

full only example.png

Then, the data is ready to be extracted in full mode in the data flow.

When the base table is large, it may not be practical to do full load every time. A common practice to reduce routine batch full load volume is to apply filters to keep historical stale data and recent active data with different loading frequencies.

Applying filters is also important to conduct partial full load of historical data. One unique feature of AecorSoft Integration Service is the ability to allow potentially more filterable fields, and such feature does not exist in traditional BW ETL. For example, a customer wants to apply filter on OrderNumber field for Plant Maintenance Order Process standard extractor 2LIS_17_I3OPER, but the extractor only allows the filter on OrderType and CreateDate:

The traditional approach to enable more filter fields is to enhance the extract structure to make OrderNumber as a selectable field. This incurs additional configuration on SAP, and it alters the standard structure, which will generate a new transport request. In consequence, there is going to be more support effort.

With AecorSoft Integration Service, the problem is eased. The extractor is analyzed to allow the most filters when possible. Please notice that there is still limitation at SAP metadata stack that certain fields cannot be used as filters no matter what.

Now, in this example, the majority of the fields in 2LIS_17_I3OPER are found to be filterable, thus they are displayed in green color. Notice the ROCANCEL field is in normal black color, hinting that it is not filterable.

Right click on the OrderNumber field to bring up the dialog

Don’t forget to change the Extraction Mode to FULL. Now we get:

Execute the job, and only the records in this order number range will be fetched.

Delta Data Extraction

All delta-enabled extractors offer the capability of initialization with data transfer. Some are able to offer initialization without data transfer. The intention of init-without-transfer is to set the delta pointer with minimal impact to system downtime, and do necessary full historical data load when SAP system is up.

Considerations of Init-without-Transfer feature at Design Time and Run Time

Because setting the delta pointer is a special activity in delta processing, obviously it does not incur data extraction, thus, the “Init without Transfer” is offered as a button in the AecorSoft Integration Service Extractor source component’s design time user interface:

The common use case is that the user can manually reset the delta pointer at design time, and leave the delta data flow running at run time.

Less commonly, if the “init-without-transfer” activity is considered like a task which should appear in the larger ETL work flow at run time, then there is also the “Init without Data Transfer” option offered in the Extraction Mode:

If this option is chosen, there is no need to connect this source component to any other destination. When the single-node data flow component is executed, the init-without-transfer will be performed.

Initial Load

For initial load, if the extractor allows init-without-transfer, then users can choose to do either init-with-transfer, or init-without-transfer followed by full historical load.

If the extractor does not support init-without-transfer, then obviously init-with-transfer is the only option for initial load.

Delta and Delta Recovery

The Delta option is chosen for routine incremental data load. If an extractor supports init-without-transfer, then after the delta pointer is reset, all future delta loads will go from that point.

Various types of delta flags are offered to help users determine the appropriate merge action:

  • New-Image (i.e. After-Image without a preceding Before-Image)
  • Before-Image (sum-able non-key components need to have reversed sign)
  • After-Image
  • Reverse-Image (i.e. Before-Image without a subsequent After-Image)
  • Incomplete Delete-Image (i.e. only key components need to be specified)
  • Additive Image (Aggregation of Before- and After-Image for sum-able non-key components; After-Image for other non-key components)

AecorSoft Integration Service offers the feature to recover the most recent delta. If for any reason, e.g. the delta data package is corrupt at destination, then the most recent delta can be repeated through the option of “Recovery of Most Recent Delta”.

When this option is chosen, the data flow can be executed again and again to repeat extracting the same delta package.

Conclusion

Understanding the complicated SAP data models is difficult. Dealing with the powerful SAP extractors is even more difficult. In the past, even for experienced SAP BW professionals who deal with SAP extractors all the time, it requires premium SAP skills to proficiently configure and handle extractors.

AecorSoft’s mission is to use advanced data algorithms to improve the productivity of enterprise software, and further help improve the productivity of businesses. With AecorSoft Integration Service, BI experts are empowered with the ability to utilize both full and delta capable SAP extractors very efficiently, which allows them to focus more on the overall business problem and enterprise architecture.

AecorSoft Integration Service makes SSIS architects and developers the winners of SAP ETL solutions!