The HANA Analytics-API(TREX API) allows BW to formulate statements beyond the standard SQL-syntax against Views in HANAs Calculation Engine. The WHERE-clause looks basically the same, but we can add the so called FEMS to the statement and HANA will return for each row to which FEMS it belongs (the FEMS is basically the BW-internal representation of the filter conditions for a restricted keyfigure). This in general leads to a much smaller result set (transferred from the database to the Analytic Engine) which in turn can save a lot of time. This API is also used when a query retrieves data from the BWA.
See the following SDN Blog for details: BW-on-HANA and the “FEMS”
In very rare cases the result set might be larger, see SAP note 1858333 'Query Execution Mode "2/3" reads more data than "0"
By default, a BW query uses the Hana Analytics-API to request the data from the database. In some cases (e.g. when the query is based on an InfoCube), it is possible to use the SQL Interface as well. This can be controlled in transaction RSRT under 'Query Properties':
- Operations in BWA/HANA
- Exception Aggregation: this is the default setting and it uses the Hana Analytics-API
- Individual access per InfoProvider: This is the standard option for optimized access to the BWA.
- Optimized Access: For MultiProviders, joint optimized access is made to all participating InfoProviders.
- No optimized Operations in SAP Hana: If possible (supported by the infoprovider; e.g. HCPR with Joins does not allow SQL), the SQL interface is used
- Formulas calculated in SAP HANA: You can also calculate formulas with exception aggregation in SAP HANA DB (avaiable as of BW75)
Execute & Debug
For testing purposes, it also possible to switch off the optimzed Hana operations in RSRT during query runtime by using the Execute&Debug functions:
The following simple test query is based on a Hana optimized InfoCube:
Operations in Hana: Execption Aggregation
Hana Analytics-API is used. All FEMS restrictions are transferred to the database and it will return for each row to which FEMS it belongs (see blog mentioned below). In this case ther are some FEMS=0 filters (highlighted red) and 3 different local FEMS>0 restrictions:
Operations in Hana: No optimized Operations in SAP Hana
If optimized operations are switched off, SQL is used to retrieve the data from the database. The local restrictions are logically connected with an OR, this means that a superset is transferred to the OLAP Engine (see blog mentioned below) which is not optimal.
As described in the blog 'BWonHana and the FEMS' (see BWonHANA: Blogs), one big advantage of the HANA Analytics-API is that it is possible to formulate statements beyond the standard SQL syntax (against column views) and add the FEMS>0 to the statement (the HANA database will return for each row to which FEMS it belongs). If you take a closer look at the statement you will notice that the selections in the WHERE clause are defined by using the so called 'Reverse Polish Notation' (RPN). RPN is a mathematical notation in which every operator follows all of its operands.
In case you are not familiar with this notation, the following simple examples could be helpful. At the top of the first picture you can see a string of operands and operators which are processed using the PRN from left to right. Operands are just 'pushed to the memory'. When a operator is processed, it is checked how many operands it refers to. E.g. the operator '+' refers to 2 operands. Then the corresponding operands left to the operator are taken. The picture trys to show how you can convert a RPN expression into a 'normal' formula with brackets:
If a formula has to be translated into a RPN expression, you can proceed as follows:
Now we take a closer look at the Hana statement from above and apply what we have learned above to convert it into a better readable format:
- STPE_C1__0COUNTRY is the technical name of the navigation attribute 0COUNTRY of the characteristic STPE_C1
- STPE_C1__0COUNTRY_SID: SIDs of the navigation attribute
- (STPE_C1__0COUNTRY_SID NE 2000008999) AND NOT (STPE_C1__0COUNTRY_SID EQ 253) means that the SID 253 is excluded. This trick is needed because of technical reasons. (STPE_C1__0COUNTRY_SID NE 2000008999) actually means select 'all' -> basis set the system then can exclude values of.
- 'f:' fems_number
- 'l:' location
- 'o:' operator
- 'c:' content_type
- 'v2:' value2
- 'v1:' value1
- PlanViz Trace generated in RSRT
- Query Property Operations in BWA/HANA
- ABAP Report RHANA_TREXVIADBSL_ANALYZER
SAP Consulting Notes
- 2962245 TrexViaDbsl Analysis Tool: Support display of import parameters on report RHANA_TREXVIADBSL_ANALYZER
- 1858333 Query Execution Mode "2/3" reads more data than "0"
- 2041740 HANA BW Calculation Scenario On-The-Fly: Supportability