Child pages
  • Convex Hull: Example III
Skip to end of metadata
Go to start of metadata

BW75 system on ORACLE with BWA

Our simple test queries are defined on an Infocube for which a BWA index exists. We compare the trex statements(used by Hana and the BWA, see HANA Analytics-API) with the SQL statements for both queries. The only difference between these two queries is an additional filter(which does not change the result) value for the characteristic Customer(0VC_CUST) which leads to the usage of the feature Convex Hull(by default only when SQL is used):

  • query STPE_CONVEX_HULL_2: 7 restricted key figures, convex hull not used
  • query STPE_CONVEX_HULL_3: convex hull used(one additional filter value for one restricted key figure; query result is the same)

We run these queries using SQL and trex and take a look at the statements and the number of records(called DBTRANS, see Query Performance and Memory Consumption) transferred from the database to the Analytic Engine. DBTRANS is one very important number when it is about query performance and memory consumption(see Query Performance and Memory Consumption):

  • query STPE_CONVEX_HULL_2
    • trex used(convex hull not applied):  DBTRANS=5
    • SQL used(convex hull not applied): DBTRANS=775
  • query STPE_CONVEX_HULL_3
    • trex used(convex hull not applied): DBTRANS=5
    • SQL used(convex hull applied):      DBTRANS=1145

You can see that via trex only 5 records are handed over to the Analytic Engine, when SQL is used we once 775 and once even 1145 records(convex hull applied).

  • So, in general, the usage of the HANA Analytics-API(used by Hana and BWA) leads to a much smaller result set (transferred from the database to the Analytic Engine) which in turn can save a lot of time. The rare case where this is not the case is discussed in Example II.
  • In general the usage of convex hull leads to a bigger number of DBRANS. However, in some case it is necessary to use it in order to avoid too big and complex SQL statements(which in turn may also have a very bad impact on the query performance).
Query Definition

The queries have 7 restricted key figures which leads to so called 7 FEMSes. The 4th key figure(FEMS=4 in our case) is restricted to a long list of single customer values. In case this number is larger than 128(or when the number of local selections is bigger than 40), the system uses convex hull when creating SQL statements.

BWA(Trex Statement) used by Query 2

We run the query in Transaction RSRT and use the button Execute&Debug to get the trex statement and statistics data displayed:

relevant part of trex statement

relevant part of query runtime statistics

Query Result

SQL used by Query 2

Now we run the query again and use the button Execute&Debug to get the trex statement and statistics data displayed and to force the query to use SQL:

The local restrictions are connected with a logical OR. The Analytic/OLAP Engine has to check afterwards every record and assign it to the corresponding restricted key figure(see also HANA Analytics-API).

Relevant part of query runtime statistics

SQL with Convex Hull used for Query 3

The trex statement is basically the same when comparing query 3 with 2. However, when SQL is used the system applies the feature convex hull and simplifies the SQL statement:

You can see that the SQL statement has been very simplified which also leads to a higher data volume from the database.

 

 

  • No labels