BW can effortlessly expose data for external consumption e.g. by a third party tool, using the checkbox “External SAP HANA View”. Those automatically generated Calculation Views which are based on Composite Providers or even BW Queries are usually quite complex, so SQL access will probably perform poorly. This blog is about two parameters which can greatly enhance runtime performance for certain scenarios, tested with BW 7.4 SP10 on HANA Revisions 85.02, 97.02 and 102.02.
1. Parameter “RS2HANA_FORCE_SQL”
Up until HANA SPS8, SQL statements were executed using the Column Engine by default. Starting with HANA SPS9 this behavior changed, now the SQL Engine is used whenever possible. It involves a lot more optimization rules e.g. join reordering determination, so in theory this might be beneficial. However, it’s only efficient if every graphical model node can be translated into SQL. What’s more, the SQL engine is still immature and currently has some bugs, which in some cases results in very bad runtimes. As a workaround, the SQL engine can be disabled.
How to set locally:
Open a View in HANA Studio, change “Execute In” to “ “, save and redeploy. The setting will apply for that single View only.
How to set globally:
Add object RS2HANA_FORCE_SQL with value “ ” into the table RS2HANA_VIEW_SET.
The table is buffered, so direct change with DB tools will only be effective after a buffer reset on the Application Servers. For that reason, it is recommended to customize this table through the appropriate SET method, see SAP Note 2252122.
For the setting to take effect, it is required to reactivate all the Views by using the button “repair external SAP HANA View” in transaction RS2HANA_ADMIN.
Note: it does make a difference whether a View is activated from within ABAP or within HANA Studio. ABAP directly inserts View information into the Generation XML. It is possible that there are some features implemented which HANA Studio doesn’t support yet, so potentially functionality could get lost. Therefore, it is advisable not to interfere with HANA Studio when the parameter RS2HANA_FORCE_SQL is set.
How to check:
Via HANA Studio -> Catalog -> “Find Table” the Create Statement of a View can easily be reviewed. If the SQL Engine is enabled, there will be an additional “WITH PARAMETERS (FLAGS=’1024’)” at the end of the statement.
If not present, the Column Engine is active instead.
Side effects:
For HANA revisions up to SPS8 the NULL handling in expressions differs between the two engines. If switched, there might be unexpected results. Example: In the column engine the expression 2 + null results in 2, whereas in SQL engine it results in null, see SAP Note 1857202. Since HANA SPS9, the NULL handling is identical for both engines, so there won’t be side effects here.
2. Parameter “EXTRA_VIEW_ATTRIBUTES_MODE”
Some data models might require another abstraction layer, so there are probably SQL Views built on top of Calculation Views, see screenshot below.
In this example, there is a SQL View (“TESTVIEW”) with 10 fields which are derived from an underlying BW Query View (“TESTQUERY01”). What happens, if a SQL statement is executed, which selects just 3 fields from the SQL View?
Common sense expectation would be that only the 3 requested fields are passed down and read from the original Calculation View, which indeed is true for HANA revisions up to SPS8. However, starting with HANA SPS9 redundant attributes in the projection list of the SQL view are no longer reduced automatically. So for the scenario above, all 10 fields would be internally materialized at first, only to sort out the invisible ones afterwards. While the ultimate output is identical, this calculation logic is inefficient and runtimes are potentially many times higher.
Fortunately, there is a parameter which tells the optimizer to behave like in earlier revisions, that is to ignore possible differences in the result and never materialize invisible fields. While that is not standard compliant anymore, it is safe to use with BW and should drastically increase performance.
How to set locally:
In HANA Studio SQL console apply the following additional parameter at the end of each select statement:
WITH PARAMETERS (PLACEHOLDER = ('$$CE_INTERNAL_EXTRA_VIEW_ATTRIBUTES_MODE$$','2'));
How to set globally:
In HANA Studio open the SQL console and execute the statement below once:
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'System' ) SET ('calcengine', 'extra_view_attributes_mode') = '2' WITH RECONFIGURE;
To unset the global parameter just execute:
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'System' ) UNSET ('calcengine', 'extra_view_attributes_mode') WITH RECONFIGURE;
- SAP Managed Tags:
