When to use Execute in SQL Engine for Calculation Views

This posting will give an overview of the implications of the setting “Execute in SQL Engine”. This setting is available for Calculation Views to overrule the default execution behavior.

Background

When Calculation Views are included in queries the query is first optimized in a special engine called “Calculation Engine”. This has a drawback when the query is included in a larger SQL query because in this case two different optimization processes are involved: One for the SQL query part and a separate one for the Calculation View part. This can lead to inefficiencies between the different optimization processes.

To avoid these inefficiencies a global optimization was introduced starting with SPS09. The global optimization is tried automatically. For this global optimization, after applying some Calculation Engine specific optimizations the resulting plan is translated into a SQL type of representation called “QO”, so that the SQL optimization can consider the whole query. The translation into a SQL-type representation is called “unfolding”. For more details about unfolding see, e.g., SAP Notes 2618790, 2223597

However, some Calculation View features cannot be translated readily into a SQL optimization due to their non-relational behavior. This means that the whole Calculation View is blocked from unfolding. In such situations you can direct the Calculation Engine to try to unfold as much as possible even though full unfolding is not possible. This can be done by setting the parameter “Execute in” to “SQL Engine”. One exception is that this setting is active for Cubes with Star Joins automatically even though the “Execute in” option is not set to “SQL Engine”.

Many features that blocked unfolding in the past, do not block unfolding anymore with recent HANA releases. The current plan is to be able to unfold all Calculation Views until end of 2020. For more details on the features that blocked unfolding with certain HANA releases have a look at the attachment to SAP Note 1857202 . Changes with SPS12 can be found in SAP Note 2441054

As of HANA, SPS04, remaining prominent blockers are queries that include non-SQL hierarchy views and anonymization nodes.

In this posting, the effect of the flag “Execute in” on the execution plan of a query will be illustrated. In some queries setting this flag to “SQL Engine” can improve the runtime because recent optimizations have focused on unfolded queries due to the fact that global unfolding is the long-term goal. Unfortunately, there is no general rule when this flag leads to performance improvements. Optimally, it will not be needed, as the query is unfolded anyways. Nevertheless, currently for some queries unfolding might still not be possible. For these queries, setting the flag to “SQL Engine” can help sometimes, depending on the context in which the query is run. Similarly, MDS queries often benefit from setting the flag to “SQL Engine” in the topmost Calculation Views that are queried by MDS. A typical scenario in which MDS queries are involved are scenarios that use SAP Analytics Cloud.

Later, we will have a look at ways to verify whether unfolding occurs for a query and also show how unfolding can be controlled.

Impact of Setting “Execute In”

To illustrate the mechanism a Calculation View will be used in which a feature prevents unfolding. The feature of k-anonymity was selected for this example with SPS04. In later releases k-anonymity might not block unfolding anymore and thus the example might not work any longer.

The view is pretty simple and includes an anonymization node as well as a join with a table:

Due to the anonymization node the View cannot be fully unfolded. If “Execute In SQL Engine” is not set, blocked unfolding means that all optimization takes place in Calculation Engine. If you set the flag “Execute In” to “SQL Engine” all parts of the global plan that can be translated are translated into a SQL representation. This means that unfolding is not blocked globally for the view but only for parts of the plan. In this example a SQL translation of joining and fetching will be created but the anonymization itself will not be unfolded. You can detect this if you have a look at the Analyze SQL plan:

On the left hand side you see the execution if “Execute In” is set to “SQL Engine”. On the right side when “Execute In” is set to empty. As you can see from there on the left side “ceQoPop” operators appear. “ceQoPop” are the SQL representation of parts of the Calculation Engine plan where translation is possible even though not the whole Calculation View can be unfolded. In contrast, on the right side you see no “ceQoPop” operators and optimization is done in Calculation Engine only. On the left side, the first “ceQoPop” (1) fetches the data for the anonymization node, the second “ceQoPop” (2) executes the join. On the right side, the table is read with a Calculation Engine operator (3) and also the join is executed using Calculation Engine operators (4).

Thus by setting “Execute in” to “SQL Engine” you enforce partial unfolding if global unfolding of the view is blocked.

Checking whether a query is unfolded

To check whether a query is unfolded you can use the Explain Plan functionality. If all tables that are used in your query appear in your plan, unfolding has taken place successfully. As an illustration you see the Explain Plan of a query addressed to the view above when the k-anonymity node is replaced by a projection node. Per default unfolding will take place and you find all involved tables in the explain plan:

As in this case unfolding is taking place per default we will use the hint “no_calc_view_unfolding” to block unfolding:

Now, a column view but no tables appear at the lowest level.

Influencing whether a query is unfolded

Unfolding is the default and done when possible so you can only block unfolding. Blocking unfolding should be avoided when possible because long term all queries should be unfolded and future optimizations are therefore focusing on unfolded queries.

Several options exist to block unfolding (compare SAP Note 2441054):

a) attaching a hint to an individual statement:with hint (NO_CALC_VIEW_UNFOLDING)

b) pinning a hint to every execution of a statement (SAP Note 2400006):

ALTER SYSTEM ADD STATEMENT HINT (NO_CALC_VIEW_UNFOLDING) FOR <sql_statement>

ALTER SYSTEM PIN SQL PLAN CACHE ENTRY <plan_id> WITH HINT (NO_CALC_VIEW_UNFOLDING)

c) adding the hint to a specific view via execution hint in a Calculation View: name: no_calc_view_unfolding, value:1

d) blocking unfolding globally: indexserver.ini -> [calcengine] -> no_calc_view_unfolding:1