SAP BW Transformations with SQL Script - When to Convert

"Code to Data" instead of "Data to Code"

With the introduction of the HANA database, SAP has introduced the "Code Pushdown" concept whereby time-consuming calculations are delegated to the database level. Instead of loading the data from the database to the ABAP server in order to perform the calculations there ("Data to Code"), the calculation is carried out directly in the SAP HANA database, therefore "Code to Data".

To implement this concept, the so-called ABAP Managed Database Procedure (AMDP) was introduced. With this new feature, ABAP developers can create and manage database procedures in the ABAP framework. These can also be used in data-intensive transformations to speed up the loading process. AMDP provides native access to the HANA database through SQLScript and avoids unnecessary data transfers between database and application server. Thus, the performance of the ETL processes (Extract, Transform and Load) in BW can be significantly improved.

Decision criteria

Customer specific transformation rules are usually nested in ABAP routines. However, these are processed on the application server, which requires data transfer between the HANA database and the application server. Especially with large amounts of data, this approach has a negative impact on performance.

However, before you set up a project to transform all the routines from ABAP to AMDP and SQLScript, you should understand that SQLScript will mainly effect performance. Therefore, it makes little sense to customize all routines. Rather, you should consider the following aspects to make a well-rounded decision.

Criticality

Ask yourself if the transformations in question are business or time critical. You should first look at the transformations that exceed or are close to the service level agreements (SLAs). In addition to the total runtime, you should also note how often the transformations are executed. So it might make sense to save a few minutes in a transformation that runs every hour. On the other hand, a process which runs over night is less critical.

Feasibility

Moreover, you should find out the true cause of the long load times. Good candidates are transformations in which the ABAP processing or the transfer of the data from the database to the application server take a long time. On the other hand, if database accesses are inefficient in the first place, it makes more sense to tune the ABAP SELECT statements. 

Increase the performance of your BW with SQLScript

Complexity

Evaluate the complexity of custom ABAP routines. Some logics are incredibly complex and it would take a lot of time (and money) to translate them into SQLScript. This would relativize the time gained in the execution of the transformation. Always carry out a cost-benefit analysis.

Know-How

AMDPs and SQLScript require different skill set than ABAP. Developers who are used to working with datasets and loops need to rethink their approach. Ask yourself if the required SQLScript knowledge is available in-house. No doubt you want to adjust the implemented transformations as soon as the consultants are done with the work and out of the house. Make sure you can.

Conclusion

In my opinion, the conversion of existing transformation to SQLScript is definitely worthwhile, as long as one follows the listed decision criteria. SQLScript should always be used with data-intensive logic to avoid additional data transfers between application and database servers. SQLScript covers many common scenarios such as master data lookup, unit conversion, currency conversion, derivation of values from a custom table and many more.

Choosing the AMDP approach with SQLScript instead of the ABAP routine can lead to significant performance gains. In practice, we have observed cases with more than 15 times better runtime. Customers can now focus on data analytics and results, rather than waiting for data to be processed.

Image: Pexels, CC0 License