Did you know that If WebI is used in HANA , all statements gets executed only in master node in a scale out setup w/o specific settings in WebI

WebI by default runs only in Master node without specific settings:

How to check the target of WebI reports execution in HANA DB:

A.Check M_EXPENSIVE_STATEMENTS using select * from M_EXPENSIVE_STATEMENTS where APPLICATION_NAME = ‘Web Intelligence’ and HOST <> ‘<master node>’; =>Output of list of webi statements that were executed in node other than master node.

If we have some entries here ,it means load distribution is fine and no further changes are required from our end. If no, it means that load distribution is not working in needs changes to specific settings in WebI.

Or

B.Refresh repeatedly a Web Intelligence document based on a JDBC connection to a HANA cluster that implements HANA scale out

In parallel, using HANA Studio, run repeatedly the SQL sentence select * from m_connections where user_name = <‘Your Web Intelligence user name’> order by START_TIME desc;

>> All SQL processing for the Web Intelligence document takes place on the HANA master node, instead of being dispatched across nodes of the HANA cluster

Reason:

-This behavior is by design and can be changed by configuring the product.

-By default, Web Intelligence executes SQL statements in one stage (direct execution).

-In order to be able to dispatch SQL statements in a cluster, HANA requires its clients to use two-stage execution (prepared statements).

Solution:

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

Change the way SQL statements are executed by Web Intelligence:

Locate the newdb.sbo file. It is generally installed in C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc.

The file is in XML format, so open it using a text editor.

Search for a line containing a Parameter named Optimize Execute.

If the line exists, edit it to match the line below, otherwise add the line below in the Defaults section of the file
<Parameter Name=”Optimize Execute”>False</Parameter>

Using the Central Management Console, restart your Web Intelligence Processing Servers.

Note: for some releases of HANA there is a known issue with some HANA-specific SQL statements that are used to query view metadata. So, while the solution above works fine for authored universes, it does not yet work with HANA Direct Access. For more information about the issue and its resolution, please refer to SAP Note 2558911.

Thanks for reading!
Follow for more such posts!
Like and leave a comment or suggestion if any!