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 however can lead to 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-parameter:
Object = 2F<name of Open ODS View>-<fieldname>
Value = IS_ALPHANUM
That sounded great and really did its job: After I applied it 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
- validate the data format in your source to see if the conversion is really required.
- Finally, remember that for alphanumeric conversions there is a little RSADMIN-trick to switch them off.
Thank you Lars and Sven to repeatedly push me to find the root-cause for these performance issues.
- SAP Managed Tags:
12 Comments
