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.
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
Nice blog - have you thought about adding it to technical articles?
Thank you for the feedback! What do you mean with "technical articles"?
Well at the top - you decided to pick "Personal Insights". And I can see how it would be a personal insight. Since it is technical in nature, you might be missing some of your audience. Down towards the end of your blog - when in edit - there will be "select a category". Just change it to Technical Articles. (I'm not sure if you can do that after it's been published.)
Thanks - good point!
Good Article brother.
Hi Jan Zwickel
The help documentation link talks about some new processing engines like HEX and others, but not mentioned about SQL engine. Is the calculation engine delegates the tasks to SQL engine if this option is checked?. Can you please let me know the relevance of SQL engine in the picture below?.
Hi Sreekanth Surampally,
you can understand calculation engine as a first layer of optimization that is then handing over the optimized query to the SQL optimizer which is then running the usual optimizations and subsequently handing over to the individual engines (such as HEX) for execution.
If query unfolding does not occur then the SQL optimizer gets a kind of blackbox from calculation engine that is optimized by calculation engine but cannot be much optimized by the SQL optimizer anymore in addition.
Therefore, the target is that all queries should be unfolded to benefit from both optimization steps.
Sometimes this is not possible because features "unknown" to the SQL optimizer are used by the calculation engine. We are working to reduce these situations but at the moment it can still occur that full unfolding is not possible. In these situations the "Execute in SQL Engine" can be beneficial because it tries to unfold as much as possible even if the full query cannot be unfolded.
In the picture above you could read SQL Optimizer/SQL Engine