In the previous article you have learned about the advantages of SQLScript. While SQLScript transformation routines are undoubtedly beneficial then it comes to performance, there are also some limitations. These are discussed in this article.
Transformations are not always executed in the SAP HANA database. Within the transformation you can check if the HANA runtime is supported. Use the check button in the Runtime Status area.
If the transformation cannot be performed at the HANA database level, the list of unsupported functions is displayed.
In the following I explain the 10 most important restrictions.
Please note that not all InfoProviders can be used as the target of a HANA transformation. The following objects are supported as targets:
As of SAP BW 7.5, the following objects can also be used as targets:
However, only the extraction and transformation are performed with SAP HANA. The SIDs are generated in ABAP. Therefore, the use of AMDP Script in a simple transformation with 1:1 assignment does not lead to any performance improvement. We therefore recommend to use ADSOs with the Enterprise Data Warehouse Architecture templates when modeling.
In addition, queries that have been defined as InfoProviders cannot be used as source of a HANA transformation. You can circumvent this restriction by defining an external SAP HANA View based on the query.
The setting “External SAP HANA View” must also be set for the InfoProvider.
Furthermore, it is not possible to perform transformations with rule groups in HANA. Once a rule group is created, only execution in the ABAP environment is possible.
Nearline Storage provides the possibility to outsource data via a data archiving process. In doing so, the data volume of InfoProviders is reduced, while the data is still available for queries. Unfortunately, these nearline connections cannot be used within HANA transformations.
Please also note that the DTP setting Semantic Groups has no influence on the sequence of the extracted data. This setting only affects the grouping of the data sent to the error stack.
If you create a routine within a transformation, you have to choose either ABAP or SQLScript. Both together are not possible.
ABAP start, end or field routines cannot be combined with SQLscript routines.
If you use several transformation routines that are connected by an InfoSource, the following rule applies. Once a preceding transformation is implemented in ABAP, the second transformation cannot be implemented in HANA. However, if the first transformation is executed in HANA, the second transformation can be executed in ABAP. As a reminder, remember the following principle - once the data is in the ABAP stack, pushdown is no longer possible.
If you create routines using the formula editor, you may only use the functions delivered by SAP. Custom functions are not supported.
When checking the HANA support in the runtime status area of the transformation a corresponding message is displayed.
Data transformed with an AMDP script is written directly to the data target. The system does not perform any data validation. The output of the transformation must be in tabular form, using a predefined order of columns and the values must correspond to the specified data types.
Please note that the output data must not only correspond to the data type used in SAP HANA, but also to the BW-specific definition for each field. For example, the data type DATE with the YYYY-MM-DD format valid in HANA is not compatible with the data type DATS in the YYYYMMDD format.
In addition, watch out for the following pitfalls:
Error handling is important in this context. As of SAP BW 7.5 SP 4, error handling can be activated in the DTP. This changes the signature of the ABAP-managed database procedure and enables error messages to be returned to SAP BW.
However, error handling with BW on HANA and BW/4HANA 1.0 is not supported for the HANA runtime. So, the incorrect data records are written to the error stack, but cannot be emptied via an error DTP.
With BW/4HANA 2.0 the error handling for the HANA runtime has been fundamentally revised, so that you can also use the error DTP. However, you should consider the performance aspects before using the error handling in the productive environment.
For more information, see SAP Note 2580109 - "Error handling in BW transformations and data transfer processes in SAP HANA runtime".
If you work with very large amounts of data (e.g. hundreds of millions of data records) in BW, you must note that extraction with the HANA execution only takes place with the following InfoProviders:
InfoCubes, however, are not supported. If you use an InfoCube as a source, data is not extracted in packages. This can exhaust the memory capacity and lead to out of memory errors.
For more information, see SAP Note 2329819 - “SAP HANA execution in DTPs (Data Transfer Processes) - Optimizations”. Possible solutions are offered in SAP Note 2230080 - “Consulting: DTP: Out of memory situation during 'SAP HANA Execution' and the 'Request by Request' Extraction”.
If you consider restrictions and criteria for switching to SQLScript, as mentioned in this article, you can benefit from enormous performance advantages. In our consulting practice we have observed cases with more than 15 times better performance. The customer can now focus on data analysis and achieving results instead of waiting for the data to be processed.
Therefore, you should always use SQLScript for data-intensive logics to avoid additional data transfers between application and database servers.
Contact us, we will help you to choose the right strategy and individual implementation.