#BW-on-HANA and the “FEMS”

Klaus Nagel

Posted by Klaus Nagel on May 15, 2013

Director - Development

More by this author

FEMS stands for German expression “Form EleMent Selektion”. Despite the cryptic acronym it has become a quite popular not only SAP-internally, but also in technical discussions with customers and consultants. This is due to its importance for BW and also because it is a light-house example of the way BW and HANA interact. It shows how the real performance break-through can be achieved only due to the tight coupling and understanding of each other’s strengths. The FEMS is basically the BW-internal representation of the filter conditions for a restricted keyfigure and is used by BW to convert a row-based database result set to a cell format appropriate for a BI client.


The table below shows a simplified, but typical Query result with the value of keyfigure “Sales” per customer first restricted to product ‘Balls’, then restricted to product ‘Caps’, and then everything sold via the sales channel (‘SC’) ‘Internet’. Additionally there is a global filter condition on the customers ‘ABC’ and ‘XYZ’.

/wp-content/uploads/2013/05/query_result.jpg


The BW Analytic Manager will generate 4 different FEMS for such a Query:

  • FEMS0 with the global filter on the 2 customers,
  • FEMS1, FEMS2, FEMS3 with the different filter conditions on the keyfigures “Sales”.


When reading this data from a classic database the BW Analytic Manager generates the following WHERE- and GROUP BY-clause in the SQL-statement:

/wp-content/uploads/2013/05/sql.jpg

The result set will contain all the rows matching these criteria, e.g.:

/wp-content/uploads/2013/05/db_result.jpg

This row-based result set then needs to be post-processed in the application server to create the cell-based query result: Each data record is assigned to the appropriate restricted keyfigure, i.e. to its “FEMSn”.


Especially if the result set contains a large number of records this can be time-consuming, and it’s double work, since the database already did evaluate the filter conditions for each record. This is exactly where BW leverages the tight integration with and the capabilities in HANA. The HANA Analytics-API allows BW to formulate statements beyond the standard SQL-syntax against Views in HANAs Calculation Engine (see picture below). The WHERE-clause looks basically the same, but we can add the FEMS1, FEMS2, FEMS3 to the statement and HANA will return for each row to which FEMS it belongs.


Additionally to the quick FEMS-assignment, the result set can be reduced in many cases drastically. Since HANA already assigns the rows to the appropriate FEMS there is no need to have the characteristics in the GROUP BY clause that only appear in the restricted keyfigure definition; in our case “PRODUCT” and “SC”. The result read from HANA in this case will then basically already have the cell-based format and will look like this:

/wp-content/uploads/2013/05/hana_result.jpg

This may not look like a big issue in the very simple example, but real customer queries often have dozens, hundreds, or even thousands of restricted keyfigures (“cells”) and thus FEMS conditions (e.g. when using structure elements and hierarchies in the Query definition). The result set which is read from the database in general is also much larger – especially if the additional GROUP BY characteristics have a high cardinality (e.g. many PRODUCTs in the above example). In such a case, the fast identification of the right FEMS and the smaller result set can save a lot of time.

The benefit becomes even more evident if the number of FEMS increases beyond a certain threshold. Experience with traditional databases has shown that if the “OR” combined filters become too complex, the database access time grows dramatically. Therefore an optimization in BW kicks in when there are more than 40 such FEMSn conditions: The so-called “convex hull” is created as the hull of all filters. This enables a much simpler execution plan and a faster read access – but it may mean that more data is read than actually needed. This data has to be filtered out again as part of the post-processing by the BW Analytic Manager. Not so with BW-on-HANA: BW can pass basically any number of FEMS-conditions to HANA and HANA can very efficiently and quickly evaluate the FEMS-conditions and return the right result set.

As the efficient handling of these FEMS-conditions is indeed crucial for decent reporting performance in BW, this feature was already available in the BW Accelerator and has moved to the core engine of HANA now. As we push more and more OLAP operations to HANA, we see that the architecture chosen is the right one. The acronym “FEMS” has become so prominent and all attempts to change the name (or the feature) have been rejected … a really time-less feature!
/wp-content/uploads/2013/05/sql_versus_api.jpg

Picture: Access by BW to classic Databases via SQL or the Analytics API of HANA.

(*) Note: The BW Analytic Manager combines the filter conditions of restricted keyfigures as much as possible to simplify the statements. So you may not always find a 1:1 relation between your defined restricted keyfigure and FEMS (or the later WHERE-clause in the statements).

VN:F [1.9.22_1171]
Average User Rating
Rating: 5.0/5 (5 votes cast)
#BW-on-HANA and the “FEMS”, 5.0 out of 5 based on 5 ratings

12701 Views