cancel
Showing results for 
Search instead for 
Did you mean: 

When shall we use “execute in SQL Engine”?

0 Kudos

Observation: In one graphic calculation view, a script-based calculation view is used. If “execute in SQL engine” is not selected, the calculation results

  1. Are correct in HANA data preview
  2. Are correct in Lumira
  3. Are wrong in Analysis for Office
  4. Are wrong in Design Studio

              Question: what is the best practice regarding “execute in SQL engine”? In which situation must this option be selected? In which situation must this option be kept empty?

Accepted Solutions (0)

Answers (3)

Answers (3)

0 Kudos

Hi all,

I am also facing some issues and differences between HANA studio and Design Studio. When I am executing the view in HANA studio I am getting results for calculated columns, in Design Studio only 0 is shown. Can this be related to the same issue?

I tried to have different settings with the SQL engine flag, but for me it didn't help.

I will follow up with Ye on an internal ticket as well, but I hoped somebody faced the same issue and found a solution for that.

Thanks a lot,

Juliane

lbreddemann
Active Contributor
0 Kudos

If the results differ with the SQL engine option selected, that most likely has to do with the semantic differences in how e.g. NULL are handled by the CE- respectively the SQL functions.

Please check http://service.sap.com/sap/support/notes/1857202 and in case this doesn't cut it http://service.sap.com/sap/support/notes/2222121.

0 Kudos

Hi Lars

thanks for the Information.

Do you have more informaiton regarding differences in the way BusinessObjects (e.g.,Lumira, Design Studio) handle HANA views?

In HANA data preview and in Lumira, we always get the correct results no matter whether or not SQL engine is selected. But in Design Studio, execution in SQL engine will have an impact on the correctness of the calcualted columns.

Many thanks

Ye

lbreddemann
Active Contributor
0 Kudos

Nope, I don't have specifics of the mentioned clients.

As a SAP employee you may want to open internal support messages in case of wrong result sets.

Given your information, the SQL Engine flag and the wrong results coincide in 50% of the cases. That seems to be similar to rather chance and not correlation.

pfefferf
Active Contributor
0 Kudos

The document attached to note 1857202 - SQL execution in Calculation Views should answer most of your questions.

Regards,

Florian

0 Kudos

Hi Florian

thanks for the reply. I read that 

"Do not use special column store functions in filter or calculated attribute expressions as they might not be convertible. For instance the functions date() und time() only work on the column store data types and can therefore not be converted. "

In our case, we used CE functions in filter and switched on "execute in SQL Engine", the the query result is correct.

What should happen when the CE functions cannot be converted in SQL engine?

Thanks

Ye

lbreddemann
Active Contributor
0 Kudos

The switch really is a flag, that indicates that you want the query based on the model to be optimised by the SQL optimizer.

If this is not possible, e.g. because you are using functions that are not convertible, then the flag cannot be obeyed and the query won't get optimized by the SQL optimizer.

You might check on this via EXPLAIN PLAN. As long as you see all the table level accesses you are looking at a SQL engine processed plan. If there on the other hand is a sudden "end" in the EXPLAIN PLAN only pointing to your calculation view, then the SQL optimization wasn't done.

I mentioned it before, but just to drive the point home: "execute in SQL Engine" is not the go-faster switch.

Concerning CE-functions: these really should be avoided nowadays.

Former Member
0 Kudos

Hi,

I read the notes but still don’t understand when to use SQL engine. 

In our case we have a CV that for some queries the SQL engine flag make better performance but for other SQL queries we get OOM (which we don’t get without this flag).

I there any rule of thumb?

We are in SP11.

Thanks,

Amir