This blog is about the pros and cons of External SAP HANA Views vs. External SAP HANA SQL Views in context of mixed modeling scenarios. It also shares some insights on cold store pruning and how to handle currencies.
Mixed modeling has become a very popular pattern for data modeling in SAP BW/4HANA in the past. In this blog, the term is used for scenarios where data stored in a DataStore object is consumed in a calculation view in SAP HANA which is then again consumed in a CompositeProvider in SAP BW/4HANA.
Such mixed modeling scenarios should be avoided whenever possible, for several reasons. A calculation view is a black box for SAP BW/4HANA. The system does not know anything about the semantics of the data provided by the calculation view. This prevents usage of the OLAP cache, pruning of cold data, and many other optimizations.
In the past, such data models were often created to overcome feature gaps in the CompositeProvider. Since SAP BW/4HANA 2.0 SPS 04, the CompositeProvider is much more flexible when it comes to projections, aggregations, joins, and calculated fields, just to name a few. Therefore, calculation views are often no longer needed. Many requirements can – and should – be covered by CompositeProviders.
For this blog, let's assume we have a valid requirement that justifies a mixed modeling approach. When consuming a DataStore object in a calculation view, you have several options:
Consuming internal artifacts, i.e., database tables, database views, or column views is not supported. They may change without prior notice which may then break your scenarios.
Let's have a closer look at the scenario used in this blog and how to leverage the External SAP HANA View resp. the External SAP HANA SQL View. Since both names are very similar, I will reference the latter as External SAP HANA SQL View.
The DataStore object DGADSO is of type 'Data Mart DataStore Object' with temperature schema 'Hot' and 'Cold' and dynamic partitioning by 0CALYEAR. It contains data for calendar years 2013 to 2023. Data for 2013, 2014, and 2015 is in the cold store.
The system generates two views for this DataStore object:
Both views differ regarding the feature set they offer. The External SAP HANA View is secured by analytic privileges and offers master data attributes and texts. These features make it a complex view and they are often not needed in mixed modeling scenarios. The External SAP HANA SQL View is much simpler and lightweight. This can be beneficial regarding performance. On the other hand, the External SAP HANA View offers built-in pruning techniques that can be useful in mixed modeling scenarios.
Before we go into the details, let's cover an important step that is needed whenever cold store is involved.
The SQL Optimizer in SAP HANA needs database statistics to create suitable query execution plans. This is also true for virtual tables which act as a kind of proxy from the SAP HANA database of SAP BW/4HANA to the remote IQ cold store. With process chains, you can create and update database statistics for virtual tables. To do so, schedule a process chain as documented in SAP Help. This is mandatory to ensure optimal query performance when cold store data is read!
The External SAP HANA SQL View /BIC/ADGADSO8 is created with CAST operators to ensure that correct data types are used, see SAP Notes 3110157 and 3198229. This is needed since cold store is involved. CAST operators can be blockers for several optimizations in the SQL Optimizer.
The External SAP HANA View system-local:bw:bw2hana::DGADSO…
You haven't heard about the decimal shift for currencies? No worries, that's rather easy to digest but its impact on data modeling and performance can be significant.
Key figures with currencies in DataStore objects are stored in the internal format – with decimal shift for currencies like KWD or JPY that have more than or less than two decimals. Such currencies are stored in table TCURX. Japanese Yen does not have any decimals, for example, while Kuwait Dollar comes with three decimals by default. The fields that store currencies, however, are created with two decimals in the database tables. To overcome this limitation, 1,234 KWD are internally stored as 12,34 by simply shifting the decimal separator by a factor of 10. 100 JPY on the other hand are internally stored as 1,00. This is to make use of the decimal places. Thus, we can store larger values.
The External SAP HANA View and the External SAP HANA SQL View offer key figures with currencies in both formats: the internal format as stored in the database and the external format after applying a decimal shift. The columns with currencies in the external format are therefore calculated columns. As always, calculated columns can have a significant impact on performance. We will come back to this point shortly.
The Analytic Engine in SAP BW/4HANA calculates with currencies in the internal format. Only before displaying a query result, the amounts are converted from the internal format into the external format, i.e., a decimal shift is done for those currencies with more than or less than two decimals.
For DataStore objects as part providers in CompositeProviders, the Analytic Engine knows that key figures with currencies are provided in the internal format. That's why no decimal shift is needed for such data prior to perform calculations in the Analytic Engine.
Calculation views as part providers in CompositeProviders usually provide external data. Therefore, it is assumed that key figures with currencies are provided in the external format. That's why these key figures are first converted from the external into the internal format by the CompositeProvider runtime. Only then, all required calculations can be done by the Analytic Engine. Finally, before displaying the query result, currencies are again converted into the external format.
For performance reasons, this multiple conversion from the internal format (as stored in the database tables of the DataStore object) into the external format (in the External SAP HANA View resp. in the External SAP HANA SQL View) into the internal format (by the CompositeProvider prior to calculations in the Analytic Engine) and finally back to the external format (in the query result) should be avoided. The impact on performance can be twofold: The calculations may take time, but they can also prevent the SQL Optimizer from applying optimizations. Note that this is also true if your data does not contain any currencies with more than or less than two decimals.
To avoid these multiple decimal shifts, it is recommended to use currency fields in the internal format whenever you consume DataStore objects in a calculation view in mixed modeling scenarios. For calculation models created in HDI, you can indicate that a currency field stores values in the internal format. Like this, the CompositeProvider is aware that these currency fields are provided in the internal format and therefore, no decimal shift into the internal format must be applied before calculations in the Analytic Engine can be performed. For calculation models created in SAP HANA Studio, there is no such option available. You can, however, set an RSADMIN parameter which indicates that calculation views as part providers of a given CompositeProvider provide all currency fields in the internal format. This RSADMIN parameter is provided with SAP Note 2660715.
In this very simple scenario, the External SAP HANA SQL View is consumed in a calculation view.
Note: The scenario in this blog is very much simplified and the calculation view is not(!) a reasonable exception where mixed modeling should be used. To keep it simple, we still use it to discuss aspects of data modeling for mixed modeling scenarios.
This calculation view is then consumed in a CompositeProvider.
Note:
Prior to activation of the CompositeProvider, the RSADMIN parameter HCPR_HM_NOSHIFT_ followed by the name of the CompositeProvider must be set. The value is not relevant (can be initial).
In the sample query, we have filters on the cold store flag and on calendar year.
The calculation view is a black box for SAP BW/4HANA. Therefore, in a mixed modeling scenario like this, the system cannot decide whether the cold store must be read or not. Cold store pruning can still be implemented by filtering the field COLD_STORE_F.
The Cold Store Flag can be filtered on
If no filter is set on COLD_STORE_F, the system reads hot, warm, and cold.
The filter on COLD_STORE_F could be determined in a customer exit variable which checks the DTO partition information stored in table RSODTOPARTINFO against the global filter on 0CALYEAR. The column CURR_LOCATION indicates the current tier: '1' = hot, '2' = warm, '3' = cold. The restrictions on the partitioning field are stored in column LOW in case of equal conditions (OPT='EQ') resp. in LOW and HIGH for between conditions (OPT='BT').
When COLD_STORE_F is not filtered and calendar year is restricted to { 2013, 2020 }, we get a remote query executed against the cold store like this:
The functions SUBSTRING and CONVERT are needed because of the CAST expressions used in the External HANA SQL View.
In this very simple scenario, the External SAP HANA View is consumed in a calculation view.
Note: The scenario in this blog is very much simplified and the calculation view is not(!) a reasonable exception where mixed modeling should be used. To keep it simple, we still use it to discuss aspects of data modeling for mixed modeling scenarios.
The input parameter _read_nls of the generated External SAP HANA View must be mapped in the calculation view. This can later be used for cold store pruning if the built-in pruning mechanism does not fulfill your requirements. To do so, choose 'Input Parameter Manage Mapping' > 'Data sources'.
In the mapping dialog, in the context menu for _read_nls, choose 'Copy and Map 1:1'.
This calculation view is then consumed in a CompositeProvider.
Note:
Prior to activation of the CompositeProvider, the RSADMIN parameter HCPR_HM_NOSHIFT_ followed by the name of the CompositeProvider must be set. The value is not relevant (can be initial).
In the sample query, we have filters on the field READ_NLS and on calendar year.
The calculation view is a black box for SAP BW/4HANA. Therefore, in a mixed modeling scenario like this, the system cannot decide whether the cold store must be read or not. Still, in this scenario that uses the External SAP HANA View, we can leverage the pruning logic that is built into the External SAP HANA View. For External SAP HANA Views on CompositeProviders, this pruning logic can prune part providers; for External SAP HANA Views on DataStore objects, it can prune cold store, based on global filters. The built-in pruning can be traced by activating a HANA trace for component 'llang' with trace level 'info', see SAP Note 2190204.
If you observe that the embedded pruning does not fulfill your requirements, you can still implement a customer exit variable to filter the input parameter.
The field READ_NLS can be filtered on
Note that the default value of the input parameter is 'off'.
When READ_NLS is set as 'on' and calendar year is restricted to 2013 and 2020, we get a remote query executed against the cold store like this:
Note that this remote query differs from the remote query in the scenario where the External SAP HANA SQL View is used. It is simpler since it does not need the functions caused by the CAST operators.
Conclusion
Use CompositeProvider features and avoid mixed modeling scenarios as much as possible. In reasonable exceptions, consider the External SAP HANA View or the External SAP HANA SQL View when DataStore objects should be consumed. The built-in cold store pruning logic is an advantage of the External SAP HANA View over the External SAP HANA SQL View. On the other hand, complex External SAP HANA Views can have a negative impact on the runtimes of model instantiation and optimization. Whereas the much simpler and lightweight External SAP HANA SQL View does not contribute much to those runtimes. For performance reasons, use currencies in their internal format and don't forget to update database statistics for the remote table when cold store is involved.
Thanks to my colleagues André, Dirk, konstantinos.stergios, and markus-s for their valuable input.
Mixed modeling has become a very popular pattern for data modeling in SAP BW/4HANA in the past. In this blog, the term is used for scenarios where data stored in a DataStore object is consumed in a calculation view in SAP HANA which is then again consumed in a CompositeProvider in SAP BW/4HANA.
Such mixed modeling scenarios should be avoided whenever possible, for several reasons. A calculation view is a black box for SAP BW/4HANA. The system does not know anything about the semantics of the data provided by the calculation view. This prevents usage of the OLAP cache, pruning of cold data, and many other optimizations.
In the past, such data models were often created to overcome feature gaps in the CompositeProvider. Since SAP BW/4HANA 2.0 SPS 04, the CompositeProvider is much more flexible when it comes to projections, aggregations, joins, and calculated fields, just to name a few. Therefore, calculation views are often no longer needed. Many requirements can – and should – be covered by CompositeProviders.
For this blog, let's assume we have a valid requirement that justifies a mixed modeling approach. When consuming a DataStore object in a calculation view, you have several options:
- Consuming the tables of the DataStore object ending at 1 and 2 (depending on the type of the DataStore object)
- Consuming one of the internal database views of the DataStore object created for reporting and extraction ending at 6 and 7
- Consuming the internal column views of the DataStore object starting with 0BW:BIA
- Consuming the External SAP HANA View (which is a calculation view)
- Consuming the External SAP HANA SQL View (which is a database view)
Consuming internal artifacts, i.e., database tables, database views, or column views is not supported. They may change without prior notice which may then break your scenarios.
Let's have a closer look at the scenario used in this blog and how to leverage the External SAP HANA View resp. the External SAP HANA SQL View. Since both names are very similar, I will reference the latter as External SAP HANA SQL View.
The DataStore Object
The DataStore object DGADSO is of type 'Data Mart DataStore Object' with temperature schema 'Hot' and 'Cold' and dynamic partitioning by 0CALYEAR. It contains data for calendar years 2013 to 2023. Data for 2013, 2014, and 2015 is in the cold store.
The system generates two views for this DataStore object:
- The External SAP HANA View system-local:bw:bw2hana::DGADSO
- The External SAP HANA SQL View /BIC/ADGADSO8
Both views differ regarding the feature set they offer. The External SAP HANA View is secured by analytic privileges and offers master data attributes and texts. These features make it a complex view and they are often not needed in mixed modeling scenarios. The External SAP HANA SQL View is much simpler and lightweight. This can be beneficial regarding performance. On the other hand, the External SAP HANA View offers built-in pruning techniques that can be useful in mixed modeling scenarios.
Before we go into the details, let's cover an important step that is needed whenever cold store is involved.
Database Statistics for Virtual Tables
The SQL Optimizer in SAP HANA needs database statistics to create suitable query execution plans. This is also true for virtual tables which act as a kind of proxy from the SAP HANA database of SAP BW/4HANA to the remote IQ cold store. With process chains, you can create and update database statistics for virtual tables. To do so, schedule a process chain as documented in SAP Help. This is mandatory to ensure optimal query performance when cold store data is read!
The External SAP HANA SQL View
The External SAP HANA SQL View /BIC/ADGADSO8 is created with CAST operators to ensure that correct data types are used, see SAP Notes 3110157 and 3198229. This is needed since cold store is involved. CAST operators can be blockers for several optimizations in the SQL Optimizer.
The External SAP HANA View
The External SAP HANA View system-local:bw:bw2hana::DGADSO…
- … consumes the internal view 0BW:BIA:DGADSO:QO which is optimized for the SQL Optimizer. Unlike the internal reporting view 0BW:BIA:DGADSO, the optimized view does not reference any analytic views. The External SAP HANA View is generated like this when 'Optimized Column View' is set in transaction RS2HANA_VIEW,
- … is generated with currencies in external and internal format (w/ and w/o decimal shift). The columns in the internal format are generated when 'Add measures w/o decimal shift' is set in transaction RS2HANA_VIEW.
You haven't heard about the decimal shift for currencies? No worries, that's rather easy to digest but its impact on data modeling and performance can be significant.
Key Figures with Currencies in Internal and External Format
Key figures with currencies in DataStore objects are stored in the internal format – with decimal shift for currencies like KWD or JPY that have more than or less than two decimals. Such currencies are stored in table TCURX. Japanese Yen does not have any decimals, for example, while Kuwait Dollar comes with three decimals by default. The fields that store currencies, however, are created with two decimals in the database tables. To overcome this limitation, 1,234 KWD are internally stored as 12,34 by simply shifting the decimal separator by a factor of 10. 100 JPY on the other hand are internally stored as 1,00. This is to make use of the decimal places. Thus, we can store larger values.
The External SAP HANA View and the External SAP HANA SQL View offer key figures with currencies in both formats: the internal format as stored in the database and the external format after applying a decimal shift. The columns with currencies in the external format are therefore calculated columns. As always, calculated columns can have a significant impact on performance. We will come back to this point shortly.
The Analytic Engine in SAP BW/4HANA calculates with currencies in the internal format. Only before displaying a query result, the amounts are converted from the internal format into the external format, i.e., a decimal shift is done for those currencies with more than or less than two decimals.
For DataStore objects as part providers in CompositeProviders, the Analytic Engine knows that key figures with currencies are provided in the internal format. That's why no decimal shift is needed for such data prior to perform calculations in the Analytic Engine.
Calculation views as part providers in CompositeProviders usually provide external data. Therefore, it is assumed that key figures with currencies are provided in the external format. That's why these key figures are first converted from the external into the internal format by the CompositeProvider runtime. Only then, all required calculations can be done by the Analytic Engine. Finally, before displaying the query result, currencies are again converted into the external format.
For performance reasons, this multiple conversion from the internal format (as stored in the database tables of the DataStore object) into the external format (in the External SAP HANA View resp. in the External SAP HANA SQL View) into the internal format (by the CompositeProvider prior to calculations in the Analytic Engine) and finally back to the external format (in the query result) should be avoided. The impact on performance can be twofold: The calculations may take time, but they can also prevent the SQL Optimizer from applying optimizations. Note that this is also true if your data does not contain any currencies with more than or less than two decimals.
To avoid these multiple decimal shifts, it is recommended to use currency fields in the internal format whenever you consume DataStore objects in a calculation view in mixed modeling scenarios. For calculation models created in HDI, you can indicate that a currency field stores values in the internal format. Like this, the CompositeProvider is aware that these currency fields are provided in the internal format and therefore, no decimal shift into the internal format must be applied before calculations in the Analytic Engine can be performed. For calculation models created in SAP HANA Studio, there is no such option available. You can, however, set an RSADMIN parameter which indicates that calculation views as part providers of a given CompositeProvider provide all currency fields in the internal format. This RSADMIN parameter is provided with SAP Note 2660715.
Consuming the External SAP HANA SQL View
In this very simple scenario, the External SAP HANA SQL View is consumed in a calculation view.
Note: The scenario in this blog is very much simplified and the calculation view is not(!) a reasonable exception where mixed modeling should be used. To keep it simple, we still use it to discuss aspects of data modeling for mixed modeling scenarios.
This calculation view is then consumed in a CompositeProvider.
Note:
- The amount in the internal format (without decimal shift) is used in the target.
- The field COLD_STORE_FLAG is added to the target.
Prior to activation of the CompositeProvider, the RSADMIN parameter HCPR_HM_NOSHIFT_ followed by the name of the CompositeProvider must be set. The value is not relevant (can be initial).
In the sample query, we have filters on the cold store flag and on calendar year.
 |  |
The calculation view is a black box for SAP BW/4HANA. Therefore, in a mixed modeling scenario like this, the system cannot decide whether the cold store must be read or not. Cold store pruning can still be implemented by filtering the field COLD_STORE_F.
The Cold Store Flag can be filtered on
- '' (empty string) or ' ' (space) in SQL resp. '#' in the query (initial value) ⇒ Only hot and warm is read.
- 'X' ⇒ Only cold is read.
If no filter is set on COLD_STORE_F, the system reads hot, warm, and cold.
The filter on COLD_STORE_F could be determined in a customer exit variable which checks the DTO partition information stored in table RSODTOPARTINFO against the global filter on 0CALYEAR. The column CURR_LOCATION indicates the current tier: '1' = hot, '2' = warm, '3' = cold. The restrictions on the partitioning field are stored in column LOW in case of equal conditions (OPT='EQ') resp. in LOW and HIGH for between conditions (OPT='BT').
When COLD_STORE_F is not filtered and calendar year is restricted to { 2013, 2020 }, we get a remote query executed against the cold store like this:
SELECT
SUM("/BIC/OUDGADSO"."AMOUNT"),
SUBSTRING(CONVERT(VARCHAR,"/BIC/OUDGADSO"."CURRENCY"),0,5),
SUBSTRING(CONVERT(VARCHAR,"/BIC/OUDGADSO"."CALYEAR"),0,4)
FROM "SAPD3T_NLS"."/BIC/OUDGADSO" "/BIC/OUDGADSO"
WHERE ( (SUBSTRING(SUBSTRING(CONVERT(VARCHAR,"/BIC/OUDGADSO"."CALYEAR"),0,4),0,4) = '2020')
OR (SUBSTRING(SUBSTRING(CONVERT(VARCHAR,"/BIC/OUDGADSO"."CALYEAR"),0,4),0,4) = '2013') )
GROUP BY "/BIC/OUDGADSO"."CURRENCY","/BIC/OUDGADSO"."CALYEAR" The functions SUBSTRING and CONVERT are needed because of the CAST expressions used in the External HANA SQL View.
Consuming the External SAP HANA View
In this very simple scenario, the External SAP HANA View is consumed in a calculation view.
Note: The scenario in this blog is very much simplified and the calculation view is not(!) a reasonable exception where mixed modeling should be used. To keep it simple, we still use it to discuss aspects of data modeling for mixed modeling scenarios.
The input parameter _read_nls of the generated External SAP HANA View must be mapped in the calculation view. This can later be used for cold store pruning if the built-in pruning mechanism does not fulfill your requirements. To do so, choose 'Input Parameter Manage Mapping' > 'Data sources'.
In the mapping dialog, in the context menu for _read_nls, choose 'Copy and Map 1:1'.
This calculation view is then consumed in a CompositeProvider.
Note:
- The amount in the internal format (without decimal shift) is used in the target.
- The input parameter _read_nls is added to the target as field READ_NLS.
Prior to activation of the CompositeProvider, the RSADMIN parameter HCPR_HM_NOSHIFT_ followed by the name of the CompositeProvider must be set. The value is not relevant (can be initial).
In the sample query, we have filters on the field READ_NLS and on calendar year.
 |  |
The calculation view is a black box for SAP BW/4HANA. Therefore, in a mixed modeling scenario like this, the system cannot decide whether the cold store must be read or not. Still, in this scenario that uses the External SAP HANA View, we can leverage the pruning logic that is built into the External SAP HANA View. For External SAP HANA Views on CompositeProviders, this pruning logic can prune part providers; for External SAP HANA Views on DataStore objects, it can prune cold store, based on global filters. The built-in pruning can be traced by activating a HANA trace for component 'llang' with trace level 'info', see SAP Note 2190204.
If you observe that the embedded pruning does not fulfill your requirements, you can still implement a customer exit variable to filter the input parameter.
The field READ_NLS can be filtered on
- 'on' ⇒ Hot, warm, and cold is read.
- 'off' ⇒ Only hot and warm is read.
Note that the default value of the input parameter is 'off'.
When READ_NLS is set as 'on' and calendar year is restricted to 2013 and 2020, we get a remote query executed against the cold store like this:
SELECT
SUM("/BIC/OUDGADSO1"."V3"),("/BIC/OUDGADSO1"."V1"),("/BIC/OUDGADSO1"."V2")
FROM (
SELECT
"/BIC/OUDGADSO"."CURRENCY" AS "V1","/BIC/OUDGADSO"."CALYEAR" AS "V2",SUM("/BIC/OUDGADSO"."AMOUNT") AS "V3"
FROM "SAPD3T_NLS"."/BIC/OUDGADSO" "/BIC/OUDGADSO"
WHERE "/BIC/OUDGADSO"."CALYEAR" IN ( '2020' , '2013') AND "/BIC/OUDGADSO"."/BIC/DGCOMPCDE" IS NOT NULL
GROUP BY "/BIC/OUDGADSO"."CURRENCY","/BIC/OUDGADSO"."/BIC/DGCOMPCDE","/BIC/OUDGADSO"."CALYEAR"
) "/BIC/OUDGADSO1"
GROUP BY "/BIC/OUDGADSO1"."V1","/BIC/OUDGADSO1"."V2" Note that this remote query differs from the remote query in the scenario where the External SAP HANA SQL View is used. It is simpler since it does not need the functions caused by the CAST operators.
Conclusion
Use CompositeProvider features and avoid mixed modeling scenarios as much as possible. In reasonable exceptions, consider the External SAP HANA View or the External SAP HANA SQL View when DataStore objects should be consumed. The built-in cold store pruning logic is an advantage of the External SAP HANA View over the External SAP HANA SQL View. On the other hand, complex External SAP HANA Views can have a negative impact on the runtimes of model instantiation and optimization. Whereas the much simpler and lightweight External SAP HANA SQL View does not contribute much to those runtimes. For performance reasons, use currencies in their internal format and don't forget to update database statistics for the remote table when cold store is involved.
Thanks to my colleagues André, Dirk, konstantinos.stergios, and markus-s for their valuable input.
- SAP Managed Tags:
4 Comments
