SAP BW*: Spotlight on HANA Transformations on ODP Sources
Hello again BW enthusiasts!
As of SAP BW 7.4 SP05 (2012) on HANA, there is a new option to process BW Transformations in the SAP HANA Runtime. This means, logic is not applied on the ABAP application layer, but on the HANA database directly based on HANA ColumnViews and embedded Calculation Scenarios. This approach opens the door for serious performance benefits as the expensive data transfer from database to application and back is avoided. This so-called HANA Push-Down of BW Transformations has been improved ever since and is still receiving enhancements in SAP BW/4HANA currently.
This blog post focuses on leveraging this Push-Down concept for ODP DataSources. As ODP is the key source system type for SAP data in BW/4HANA, most customers dealing with conversion projects will have to gain new expertise in this area. Here I would like to share some valuable lesson learned from customer projects.
Let´s assume, an in-place conversion goes roughly through following four phases:
- The starting point are often legacy data flows, based on S-API interfaces, and maybe even with 3.x objects in combination with the RSA7 delta queue in the source system.
- The evolution to ODP includes moving to the new Operational Delta Queue as replacement for the RSA7 predecessor.
- The final step to leverage ODP is running DTPs which skip the PSA, if this has not been accomplished in step 2 already. At this stage you are compatible with SAP BW/4HANA and ready for conversion.
- Finally, after conversion to SAP BW/4HANA there is room for additional optimizations.
As you see in the figure 1 above, the HANA Runtime is not available for Transformations sourced on ODP DataSources in SAP BW 7.x. This means, the extraction from ODP sources w/o using the PSA always needs to be processed in ABAP, because it requires a remote function call from the BW ABAP stack to the ABAP server of the source (… btw, the same applies for File DataSources as well). In BW 7.x, the HANA Runtime on DataSources is only possible if the source data is already in HANA, i.e. BW DataSources on source systems of type SAP HANA or DB-Connect to the local HANA database.
This gap was closed with SAP BW/4HANA 1.0 SP08, when the HANA Runtime for Transformations on ODP DataSources became available again. How was that achieved?
Well, the data is first extracted into a temporary table on BW/4HANA-side. This table is called Data Transfer Intermediate Storage (DTIS). This source enables HANA Runtime again, because from there the system is independent from the ODP framework. This DTIS is a crucial innovation in SAP BW/4HANA which is leveraged for other scenarios as well (error handling, sorting). It is like the PSA in BW 7.x, but data is not stored permanently there, and cleanup is managed by the system automatically.
This blog post would end here, if there wasn´t a little but in some cases critical drawback: The extraction from ODP to DTIS is processed serially and not in parallel* (see update below). This means, although a HANA Transformation on ODP DataSources provides great performance in the rule processing due to the HANA Push Down, the prior extraction process performs worse on the other hand. So here customers must take a difficult decision between superior parallel extraction with standard ABAP Runtime against serial extraction with superior HANA Runtime :-(. As a general rule of thumb, the more data is extracted, the more the parallel extraction without DTIS will be advantageous from a pure performance point-of-view. So recommendation is to carefully evaluate each customer scenario, implement both approaches and compare them.
Let´s quickly compare the two options:
In Scenario 1 the DTP Monitor looks familiar if we use a standard ABAP Transformation and parallel extraction. The overall runtime in our case is 7 min 20 sec for about 160.000 records in three data packages.
Scenario 2 is based on serial extraction into DTIS in combination with a HANA Transformation. Here the DTP Monitor is different as you clearly see the two steps and the impact of the DTIS. After the DTIS is filled by three data packages serially, the HANA Transformation processing works in parallel as expected afterwards. In our case, for the same about 160.000 records this approach is 20 seconds faster (7 min in total).
As a result, the HANA processing compensates the performance loss of the serial extraction into DTIS a bit. But assumption is that the more data volume is extracted the more this advantage shifts into a disadvantage…
Let´s turn the spotlight back on the first figure with the four phases of transition to ODP. What if you had implemented some fancy HANA Transformations with HANA SQL coding in start, field, end and/or even expert scripts in your applications before you decommissioned the PSA in phase 3? At this stage, the DTP will not work anymore and will drop some misleading error messages like “Direct Extraction from this DataSource is not supported” meaning that the HANA runtime in your Transformation is not supported any more. You have two options then:
- Go back to ABAP Runtime, which means reverting the HANA scripts and change to good old ABAP routines.
- Keep the current HANA Runtime, but insert an additional ADSO persistence. This means to setup a Staging ADSO providing the same service as your PSA did in the past. The value added here is that DTIS is avoided, and the data flow profits from the parallel extraction.
In this scenario make sure to leverage following process variant for Housekeeping this ADSO: Clean up Old Requests in ADSOs. Unless you risk deleting the delta queue in your ODP source.
The purpose of this blog post is to document the status quo, because I found it challenging to get the overall picture. The HANA Runtime for ODP DataSources will not be added to SAP BW 7.5, so make sure you are prepared to bypass this road hole on the path towards SAP BW/4HANA.
The restriction mentioned above regarding the serial extraction from ODP to DTIS has been removed with SAP BW/4HANA 2021 SP04. See more details in my follow-up blog:
References for this scenario
- 2437419 ODP and File DataSources not supported for HANA execution
- 2464541 BW/4HANA Conversion – Data Transfer Processes
- 3037467 DTIS Overview
- 3037816 After upgrade TRFNs with ODP DataSources are set to HANA Runtime by default
- 2949412 When is an ADSO as replacement for a PSA required and when not?
- SAP help Transformation in the SAP HANA Database
References for HANA Transformations in general
- 2057542 Recommendation: Usage of HANA-based Transformations
- 2549227 Collection of Consulting notes related to SAP HANA Transformation
- 2603241 Overview and summary of the most important SAP Notes in the context of BW transformations with SAP HANA and ABAP execution (incl. BW Note Analyzer)
- THE BLOG: https://blogs.sap.com/2016/05/24/hana-based-bw-transformation/
- How-To-Guide Using SQL Script in SAP BW Transformations
- SAP BW/4HANA Modeling Guide – Transformations in the HANA Database
many thanks for this blog entry and the provided overall picture for this topic.
Thanks Frank for your valuable blog!!!
Hi Frank, thanks for another great blog post!
Hi Frank, I think this is an interesting link in relation with this blog:
With an ODP source system, we recommend using ABAP runtime, since the extraction is done by ABAP anyway. A transformation in SAP HANA would cause unnecessary effort, since the data must first be persisted.
Take care, Philipp
Thank you for sharing this. Indeed, a strong statement from SAP side.