1963887 - Business Objects Web Intelligence sends unprepared statements by default to HANA which prevents usage of HANA statement routing in HANA scale out landscapes

Symptom

When executing SQL below you see that connections from Web Intelligence are being established on only one HANA node which is maintained as the first node (typically master node) in your connections string in IDT (Information Design Tool) used by Web Intelligence report
select * from m_connections where user_name = <'Your WebI user name'> order by START_TIME desc

Other Terms

HANA, scale out, statement routing, prepared statement, Web Intelligence, Optimized Execution

Reason and Prerequisites

By default Web Intelligence 4.x (WebI 4.0 and 4.1) sends non-prepared SQL statements to underlying database. This means HANA statement routing in HANA scale out landscapes would not work for those Web Intelligence queries, as statement routing can be used with prepared SQL statements only as of now.

Solution

It is possible to change Web Intelligence behavior and to send prepared statements which would trigger usage of HANA statement routing if it is enabled on HANA server and HANA client side.
a)    set parameter Optimize Execute = False in file newdb.sbo for all drivers which are used with Web Intelligence against HANA.
For instance in case of JDBC driver please go to X:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc and modify newdb.sbo file in text editor
b)    restart BO server to apply the new setting
Now, Web Intelligence queries (and its connections) should be distributed over different HANA nodes as per HANA statement routing specification.

Header Data

Released On	11.02.2015 09:37:12
Release Status	Released for Customer
Component	BI-RA-WBI Web Intelligence
Other Components	HAN-DB SAP HANA Database
Priority	Correction with medium priority
Category	Customizing