Dear BW enthusiasts!
Virtual access to remote data is one of the key capabilities which was introduced with SAP HANA quite some time ago. Since SAP BW 7.4 on HANA the CompositeProvider and open ODS View represent the data modeling toolset to model these kind data federation scenarios in the SAP BW application.
This blog post will focus on one key feature of the open ODS View (ODSV) which can result in performance disadvantages. It is the result of one of my customer engagements and hopefully serves as trigger for future improvements by SAP.
Lets directly jump into the task I was given by my customer, a leading retail organization:
The customer decided to leverage the ODSV as a standard model to integrate remote data normally located in HANA databases of other SAP systems. One of the key reasons in favor of the ODSV they see is that the mapping from fields to BW InfoObjects has only be done once. Later on, this ODSV can be reused in different CompositeProviders and the assignments to the target structure are really easy and fast because you map same InfoObjects and not fields any more.
However, in my case there were several examples available, where the performance was not acceptable, and it turned out that the ODSV was the performance bottleneck. See the example below:
My first analysis focused on a SAP note which covers many different aspects of ODSVs: 2198480 (FAQ: BW Open ODS View – Query Execution): There in point 3 I found a statement which raised my interest: “…built-in conversions usually have an impact on the query runtime…”.
To understand these built-in conversions better, I studied SAP Guide called “How to implement virtual integration of external data with SAP BW powered by SAP HANA” by my dear colleagues Ulrich Christ and Marc Hartz. This paper explains the difference between HCPR and ODSV quite well and in great detail. There I found the following: “…Data types and the exact data format are important, especially when combining external data with BW master or transaction data. BW InfoObjects frequently contain format settings, e.g. the ALPHA conversion exit. Open ODS Views offer such conversions as a service. When external facts are integrated via an Open ODS View with associations to the required InfoObjects, the necessary conversions are automatically generated. It should be noted that the Open ODS framework takes a “conservative” approach, which means that these conversions are always applied even if the source delivers data in the required format.
The paper above also mentions you have to open your data model in SAP GUI tr. RSODSVIEW to understand which conversions are applied to your individual ODSV. There go to tab “Preview for Query” to find a column called “Conv.Rout.” which tells you if a conversion takes place and which one that is.
In my case these were Profit Center, and a few additional customer objects which were associated to BW InfoObjects with an ALPHA conversion exit. As the customer objects were optional navigation attributes only with a limited cardinality, my focus was the Profit Center. There we had a cardinality of several thousand values in a hierarchy down to the individual retail stores. This attribute was always processed as it was a mandatory input variable before the query result was calculated. So the ODSV built-in ALPHA conversion seems was always processed even though the source was a SAP S/4HANA system which guarantees proper format with leading zeros by default. In one word, this conversion was unnecessary, the data was in the right format already.
Finally, I found the needle in the haystack in SAP note 2104414 (BW Open ODS View: Query Performance and alphanumeric conversion): There it says: “…A BW Query on an Open ODS View shows suboptimal performance when applying filters on certain fields. …The field is associated with an InfoObject that has an ALPHA-like conversion routine. Alphanumeric conversions can have a significant impact on query runtime, especially with filtering”. BINGO
.
The note also provides a solution, because this alphanumeric conversion at query runtime can be suppressed by following RSADMIN-parameters:
Object = 2F<name of Open ODS View>-<fieldname>
Value = IS_ALPHANUM
That sounded great and really did its job: After I applied these parameters for the profit center field, the query runtime was equal to the scenario without ODSV (approximately 4 seconds / compared to 40 seconds before).
Summary
So if you run into performance challenges with ODSV which use associations to BW InfoObjects…
The purpose of this blog post is not only to document the solution, but also to take it to my peers from SAP BW product management. I think these RSADMIN parameters are well hidden, hard to find and difficult to maintain in the end. It is so well hidden, that even SAP support could not find it when we opened the case as an incident.
Instead I am sure, it would make much more sense to show the applied conversion routines in the Open ODS view modeling UI in BWMT for each field in the target structure. And if the conversion is of type ALPHA – the switch-off function should be make available there as well.
Okumaya devam et...
Virtual access to remote data is one of the key capabilities which was introduced with SAP HANA quite some time ago. Since SAP BW 7.4 on HANA the CompositeProvider and open ODS View represent the data modeling toolset to model these kind data federation scenarios in the SAP BW application.
This blog post will focus on one key feature of the open ODS View (ODSV) which can result in performance disadvantages. It is the result of one of my customer engagements and hopefully serves as trigger for future improvements by SAP.
Lets directly jump into the task I was given by my customer, a leading retail organization:
The customer decided to leverage the ODSV as a standard model to integrate remote data normally located in HANA databases of other SAP systems. One of the key reasons in favor of the ODSV they see is that the mapping from fields to BW InfoObjects has only be done once. Later on, this ODSV can be reused in different CompositeProviders and the assignments to the target structure are really easy and fast because you map same InfoObjects and not fields any more.
However, in my case there were several examples available, where the performance was not acceptable, and it turned out that the ODSV was the performance bottleneck. See the example below:
- SAP S/4HANA data is modelled in a Calculation View on the BW-side
- If the Calculation View is consumed directly in a HCPR2 in combination of existing ADSO data, the reporting performance is great (about 4 seconds).
- If the same Calculation View is integrated into an ODSV first and this one is consumed in another HCPR1 (copy of HCPR2) with the same ADSO data, the reporting performance is about 10 times worse, although the queries equal each other (Query1 = Query2).
My first analysis focused on a SAP note which covers many different aspects of ODSVs: 2198480 (FAQ: BW Open ODS View – Query Execution): There in point 3 I found a statement which raised my interest: “…built-in conversions usually have an impact on the query runtime…”.
To understand these built-in conversions better, I studied SAP Guide called “How to implement virtual integration of external data with SAP BW powered by SAP HANA” by my dear colleagues Ulrich Christ and Marc Hartz. This paper explains the difference between HCPR and ODSV quite well and in great detail. There I found the following: “…Data types and the exact data format are important, especially when combining external data with BW master or transaction data. BW InfoObjects frequently contain format settings, e.g. the ALPHA conversion exit. Open ODS Views offer such conversions as a service. When external facts are integrated via an Open ODS View with associations to the required InfoObjects, the necessary conversions are automatically generated. It should be noted that the Open ODS framework takes a “conservative” approach, which means that these conversions are always applied even if the source delivers data in the required format.
The paper above also mentions you have to open your data model in SAP GUI tr. RSODSVIEW to understand which conversions are applied to your individual ODSV. There go to tab “Preview for Query” to find a column called “Conv.Rout.” which tells you if a conversion takes place and which one that is.
In my case these were Profit Center, and a few additional customer objects which were associated to BW InfoObjects with an ALPHA conversion exit. As the customer objects were optional navigation attributes only with a limited cardinality, my focus was the Profit Center. There we had a cardinality of several thousand values in a hierarchy down to the individual retail stores. This attribute was always processed as it was a mandatory input variable before the query result was calculated. So the ODSV built-in ALPHA conversion seems was always processed even though the source was a SAP S/4HANA system which guarantees proper format with leading zeros by default. In one word, this conversion was unnecessary, the data was in the right format already.
Finally, I found the needle in the haystack in SAP note 2104414 (BW Open ODS View: Query Performance and alphanumeric conversion): There it says: “…A BW Query on an Open ODS View shows suboptimal performance when applying filters on certain fields. …The field is associated with an InfoObject that has an ALPHA-like conversion routine. Alphanumeric conversions can have a significant impact on query runtime, especially with filtering”. BINGO
.The note also provides a solution, because this alphanumeric conversion at query runtime can be suppressed by following RSADMIN-parameters:
Object = 2F<name of Open ODS View>-<fieldname>
Value = IS_ALPHANUM
That sounded great and really did its job: After I applied these parameters for the profit center field, the query runtime was equal to the scenario without ODSV (approximately 4 seconds / compared to 40 seconds before).
Summary
So if you run into performance challenges with ODSV which use associations to BW InfoObjects…
- check for applied conversion routines in tr. RSODSVIEW and
- remember that for alphanumeric conversions there is a little RSADMIN-trick to switch them off.
The purpose of this blog post is not only to document the solution, but also to take it to my peers from SAP BW product management. I think these RSADMIN parameters are well hidden, hard to find and difficult to maintain in the end. It is so well hidden, that even SAP support could not find it when we opened the case as an incident.
Instead I am sure, it would make much more sense to show the applied conversion routines in the Open ODS view modeling UI in BWMT for each field in the target structure. And if the conversion is of type ALPHA – the switch-off function should be make available there as well.
Thank you Lars and Sven to repeatedly push me to find the root-cause for these performance issues.
Okumaya devam et...