2141024 - SBOP ANALYSIS MS OFFICE 1.4 (AAO) SP11 and lower versions might send unprepared statements to HANA which prevents usage of HANA statement routing in HANA scale out landscapes

Symptom

When executing SQL below you see that most connections from Advanced Analysis for Office 1.4 SP11 (AAO 1.4 SP11) and lower versions are being established on only one HANA node (typically master node) which is maintained in your connection string used by AAO
select * from m_connections where user_name = <'Your HANA user name'> order by START_TIME desc

Other Terms

HANA, scale out, statement routing, prepared statement, Advanced Analysis for Office, AAO

Reason and Prerequisites

Advanced Analysis for Office 1.4 SP11 (AAO 1.4 SP11) and lower versions might send non-prepared SQL statements to underlying database in many cases.
Actually the prepared statements are sent only if some filters and/or variables are applied in your AAO report.
This means HANA statement routing in HANA scale out landscapes would not work for many AAO queries which do not have any filters and/or variables applied, as statement routing can be used with prepared SQL statements only as of now.
This note does not apply to Advanced Analysis for Office 2.0 (AAO 2.0) which uses a different architecture to connect to HANA (InA)

Solution

Please upgrade Advanced Analysis for Office 1.4 SP12 when it is available. Starting from AAO 1.4 SP12 will send prepared statement always, even if neither filters nor variables are applied in AAO report.
Now, AAO queries (and its connections) should be distributed over different HANA nodes as per HANA statement routing specification.

Header Data

Released On	17.03.2015 08:17:32
Release Status	Released for Customer
Component	BI-RA-AO-XLA Excel Addin
Other Components	HAN-DB SAP HANA Database
Priority	Recommendations / Additional Info
Category	Consulting