Optimizing Data Loading

Since the introduction of SAP HANA as the database that powers SAP BW, transformation processing has increasingly been pushed down to SAP HANA for optimal SQL-based processing. Selected operations were pushed down whenever it was possible, but many operations that could not be converted to SQL had to stay in the ABAP run-time. But now, with SAP BW/4HANA, the transformations have reached a new level of push-down, with almost all of the basic operations now processed in the SAP HANA run-time.

Transformations can generally be executed either in SAP HANA runtime or ABAP runtime depending on the feature set used. By default, the SAP HANA runtime is the predefined one. During the activation process, the SAP BW/4HANA application pushes down all processing objects and generates the required SAP HANA artifacts. However, the following rule types prevent the SAP HANA runtime and enforce the ABAP runtime instead:

1. Customer-defined functions in the Transformation´s Formula Editor.
2. ABAP coding in Field routines, Start routines, or Expert routines. End routines represent an exception, because a Transformation can actually apply the SAP HANA runtime although having ABAP coding defined only in the End routine. Strictly speaking, this scenario may be referred to as Partial SAP HANA Runtime because both runtimes are combined: first all start and field logic is processed on SAP HANA side, before the result is transferred to the ABAP application server to apply the end routine logic, before the results are transferred back to the SAP HANA database.

If one of these two conditions apply, the Transformation is changed automatically to the ABAP Runtime and an SAP ABAP program is generated during activation.

In SAP BW/4HANA, you create and edit Transformations in the BW Modeling Tools. Maintenance in the Data Warehousing Workbench (transaction RSA1) is not available any more. The following adaptions were introduced:

• Runtime: SAP HANA Runtime and ABAP Runtime.

  Note

  The runtime for the execution can no longer be set in the Data Transfer Process as it was with SAP BW 7.5 powered by SAP HANA. Instead, it is a parameter of the Transformation.

• In the DTP, the feature with the former name Semantic Key for Grouping has been renamed to Extraction Grouped By: with that, you can select source fields that form a semantic key. All records for the specified keys are then combined into one data package and processed together.

• In the DTP, instead of the former Error Stack, the Data Transfer Intermediate Storage (DTIS) is now used to manage error handling. For example, you can call DTIS maintenance to correct the records there. For more details refer to SAP note 3037467 (DTIS overview and important notes).

SAP BW/4HANA Transformations in SAP ABAP Runtime load the data, package by package, from the SAP HANA database objects into the memory of the SAP BW/4HANA ABAP application layer for further processing. The Transformation logic is then executed inside the application server, and the transformed data packages are shipped back to the database server. The database server writes the resulting data packages into the target database object. During processing of an ABAP-based Transformation, the source data package is processed row by row. ABAP-based processing allows defining field-based rules, which are processed as sequential processing steps. In the end, the overall runtime is the combination of both data transfers between the database and application server. This processing approach results in a clear classic distinction between application logic and data management.

Using SAP HANA Runtime the data transformation logic is executed inside the SAP HANA database. Instead of reading the data from the database, processing the Transformation logic in the application server, and writing the transformed data back to the database, the logic of the Transformation is reflected in an SAP HANA CalcScenario. This is generated during the Transformation activation process on top of the source InfoProvider in the SAP ABAP layer with a strict naming convention: TR_<< Program ID for Transformation (Generated)>> which cannot have been explicitly created or modified.

The HANA CalcScenario is managed in table RSTRAN and can be displayed in transaction RSDHATR. In this UI, the CalculationScenario tab is only visible if the Expert Mode is switched on (choose Extras → Export Mode On/Off). The tab shows the technical definition of the corresponding HANA CalcScenario, which includes the Transformation logic and the SQLScript procedure.

The HANA CalcScenario is embedded into an SAP HANA Column View. When the DTP is executed, it creates an SQL SELECT statement based on this Column View and selects all required data from the source InfoProvider. During this process all defined Transformation logic defined in the CalcScenario is applied to the selected source data before the results are written to the target InfoProvider.

By shifting the Transformation logic into SAP HANA CalcScenarios, the data is transferred directly from the source object to the target object within a single processing step. Technically, this is implemented as an INSERT AS SELECT SQL statement that reads from the Column View and inserts the results into the Transformation´s target database object. This eliminates expensive data transfers between database server and application server (ABAP), as the complete processing takes place inside SAP HANA.

The benefit of this approach is twofold: First, the data does not have to be transferred back and forth between application server and database server. Second, the Transformation logic can leverage the full CPU power and parallelism of the SAP HANA database. The degree of performance improvement depends on several factors, such as the amount of data read and written, the nature of the required logic, and the possible parallelism. Results confirm performance improvements, but in some cases, the performance improvement may be negligible, especially when the data volume is rather small.

In SAP BW/4HANA, HANA SQL Script (ABAP Managed Database Procedures AMDP) can be used for field script, start script, end script or expert script. Native SAP HANA coding is generated for the data loading process and full integration into SAP BW/4HANA scheduling, monitoring, transport management, etc. is offered.

The setting for a Transformation's runtime is available on the General tab of the Transformation Modeling UI in the BW Modeling Tools. The SAP HANA runtime provides better performance than ABAP runtime, and mass data can be processed there very quickly. However, SAP HANA runtime is more complex to work with. There are essential differences between the two runtime modes, which can lead to different results. This means that when you use the SAP HANA runtime, you should carefully validate the results against expected results, especially if existing ABAP Transformations are to be replaced. Some typical differences between the runtimes are described below:

• In many cases, the level of complexity of SAP HANA SQL scripts is greater than a corresponding solution in an ABAP routine. For this reason, you should carefully evaluate which runtime you want to use, with regard to ease of use, work effort, and maintenance effort. SAP recommends using formulas if possible, because most formulas can be used in both runtimes and are easier to maintain. You should only use customer coding, if you cannot map your case into the existing set of formulas.

• In the SAP ABAP runtime, types supported in ABAP are converted automatically, so a value of a character field can easily be written to an integer field if the field contains numbers at runtime for example. This is not the case for the SAP HANA runtime: An error will occur if you do not explicitly specify the type conversion in the formula or in the SAP HANA script. See also SAP note 2800346 (Consulting: Handling of fields with conversion exits in AMDP routines).

• In the SAP HANA runtime, results of calculations are truncated according to decimal places and are not rounded. See also SAP note 2627044 (Different results for Formulas during HANA Execution and ABAP Execution).

• The error handling had been fundamentally revised for the SAP HANA runtime to ensure improved stability in complex scenarios. See also SAP note 2580109 (Error handling in BW Transformations and data transfer processes in SAP HANA runtime).

The push-down option is not restricted on data flows with only one Transformation. It is known practice to combine Transformations by InfoSources which is also referred to as SAP BW stacked data flows. SAP recommends to stack not more than a maximum of three Transformations to make sure getting the best performance benefits from the push-down.

In general, a "Stacked Data Flow" is a process in SAP BW, where several Transformations are combined by InfoSources. A mixture of Transformation Runtimes is supported in a Stacked Data Flow, if:

• The change between Runtimes happens only once

• The ABAP Runtime is at the end of the Stacked Data Flow

As with Transformations without InfoSources as source or target object, here the processing mode is defined in the individual Transformations and finally the overall combination of these runtimes result in the final execution path. This path can be pushed down to SAP HANA if certain conditions are met:

1. If all Transformations support the SAP HANA runtime, then the complete process can be executed in SAP HANA.
2. If there is a mixture of runtimes in the combined Transformations, then you can change the runtime only once and only from SAP HANA to SAP ABAP. As a consequence, Transformations in ABAP runtime need to be at the end of the data flow. This explains why a single Transformation can keep the SAP HANA Runtime, although here is an ABAP end routine.

To undermine the conditions mentioned above, let´s mention some typical scenarios of stacked data flows which are not supported:

• Scenario 1: If the ABAP runtime is not at the end of the data flow, or the runtime is changed more than once, than the DTP activation will fail with the error mentioned above. This means you can actually model this scenario with Transformations and InfoSources, but it is not possible to execute it because the DTP cannot be created (see also SAP note 2799699). So unfortunately the error will occur at a rather late point of the modeling process.
• Scenario 2: The option to create an ABAP end routine in an SAP HANA Transformation is not available for objects which use InfoSources as a target object (see SAP note 2987230).

If the Transformation processing mode is set to SAP ABAP, the used InfoSources in a stacked data flow can be used to aggregate data within the data flow. If the processing mode is SAP HANA, the data will not be aggregated as set in the InfoSource settings. This is because the SAP HANA-based Transformation does not know the processing type. Therefore, you will not get a warning message about the InfoSource aggregation behavior. See also SAP notes 2541540 and 2968024.

Consider the following recommendations, mainly described in SAP note 2057542 (Recommendation: Usage of HANA-based Transformations) before changing a Transformation to SAP HANA Runtime:

• If no significant (and critical) processing time is gained by switching to SAP HANA Runtime, there is no reason to do so.

• SAP recommends the usage of formulas in Transformations, since most formulas can be pushed down to SAP HANA.

  Note

  See also SAP notes 3072196 (BW Transformation formula availability in SAP BW/4HANA) and 2423215 (Non Supported Formulas in HANA Transformations

• SAP recommends the use of other standard interfaces, for example lookups in master data or ADSOs, instead of writing custom ABAP / HANA code.

• The SAP HANA Runtime is an alternative mode, and not a replacement for the current mode. The classic ABAP-based execution mode will also work in the future, will be supported, and will see additional improvements and new features.