BW Queries by factors faster using FEMS-pushdown

  • Article

Complex SAP BW queries often contain FEMS-filters. These filters used to be applied on the SAP application server, which was not very efficient. A new SQL statement generator in SAP BW implements an optimized algorithm for FEMS processing. We have seen performance improvements up-to factor 100. The actual performance improvement varies heavily for different BW queries. The main idea was to reduce the processing time of the application server by pushing down the FEMS filters from the application server to the database. However, the new algorithm even reduces the database processing time in most cases. The following example shows the BW query statistics of a simple BW query with 15 Selection Groups. The total runtime went down from almost 42 seconds to 1.4 seconds.

fems1

What are FEMS filters?

A FEMS filter (Form EleMent Selektion) in SAP BW are filters on Selection Groups. To make a long story short: FEMS filters are filters on key figures, structures or cells. For an existing BW query you can check the number of Selection Groups in SAP transaction RSRT:

fems2 fems3

A BW query with FEMS filters is not necessarily slow. However, complex FEMS filters were often not applied on the database. A SQL query containing almost no filters was created, which returned a huge result set (672,184 rows of the 100,000,000 rows cube in the example above). Running such a SQL query requires high processing time on the DB server and results in high network I/O. Afterwards, the FEMS filters have to be applied on the result set on the SAP application server. Due to the architecture of the SAP application server, this step is always performed single-treaded!

SAP realized soon, that FEMS filters should be pushed down to the database. However, SAP was convinced that this cannot be performed efficiently with SQL. Therefore, an undocumented API was implemented in SAP BWA (and later in HANA) some years ago. This API was used for pushing down FEMS filters to BWA by bypassing the SQL interface. SAP implemented further DB-pushdowns exclusively for BWA (and HANA). However, it figured out, that some BW queries were faster without using DB-pushdown. Therefore, the so-called TREXOPS modes were introduced. Mode 2 is the FEMS-pushdown, mode 3 is the MultiProvider-pushdown. You can configure the TREXOPS mode per BW provider and per BW query. A higher number always includes all optimizations of a lower mode number.

fems4

SAP BWA supports TREXOPS modes 2 and 3. The most important is the FEMS-pushdown. The default setting for BWA is TREXOPS mode 2 (see SAP note 1790426)

FEMS-pushdown with SQL Server

The FEMS-pushdown on SQL Server implements a new algorithm, which uses the standard SQL interface. This algorithm does not only push-down the FEMS filters. For example, it further reduces the complexity of the SQL query by factorizing the FEMS filters on the application server before running the SQL query. As a result, even the DB response time decreases in many cases. One may argue, that reducing the DB response time will result in a higher utilization of the DB server (which is a unique resource, in contrast to the application servers). However, this is not necessarily the case. The optimized algorithm even reduced the consumed CPU time on the DB server in the example above. You can see the total CPU time in the SQL Server query statistics in column worker_time.

fems5

For checking the SQL Server response time, the SAP BW statistics (see Data Manager time above) is more accurate. The column elapsed_time in the SQL Server query statistics does not contain the compilation of the SQL statement. On the other hand, it contains processing time on the application server between the fetches of the SQL query (which is using a cursor).

The FEMS-pushdown on SQL Server also uses the TREXOPS modes described above. Any mode higher than 0 enables the new SQL statement generator for FEMS queries. For non-FEMS queries (or TREXOPS mode 0) the old SQL statement generator is used. The performance improvements caused by the FEMS-pushdown is highly depend on the actual BW query. In a few cases it may even increase the BW query runtime. However, we have only seen this for BW queries, which are fast anyway. It does not really hurt whether a BW query takes 3 or 4 seconds, but it makes a difference whether it takes 300 or 4 seconds. In the worst case, you can disable the FEMS-pushdown for a particular query using the TREXOPS modes.

Prerequisites for FEMS-pushdown

The new statement generator can only be used, when the following prerequisites are fulfilled:

  • The required SAP BW code is implemented (minimum: 7.50 SP4 + correction instruction in SAP Note 2368402)
  • The BW query is a FEMS-query (it has at least 2 FEMS-filters)
  • FEMS-pushdown is activated by setting RSADMIN parameter USE_FEMS_IN_DB = X (and TREXOPS mode ≥ 2)
  • An existing BWA connection has to be completely disabled (for all cubes)
  • The InfoProvider is a Flat Cube (This requires SQL Server 2014 or newer). You can also benefit from FEMS Filter Pushdown for Multi-Provider. In this case, a separate SQL query is running against each Part-Provider. SQL queries against Flat Cube Part-Providers use the new statement generator, SQL queries against other Part-Providers simply use the old statement generator.
  • Inventory queries are currently not supported for FEMS Filter Pushdown. They always use the old statement generator.
    However, you can use the new statement generator for inventory cubes, as long as the query does not contain an inventory key figure.

Summary

There are already several options available to speed-up BW queries with SQL Server. Each of them alone can speed-up BW queries by factors:

  • Having sufficient hardware resources, you can simply increase SQL Server intra-query parallelism using RSADMIN parameter MSS_MAXDOP_QUERY.
  • You can use an optimized index structure for BW cubes by applying SQL Server Columnstore. With SQL Server 2012, 2014 and 2016 we already released the 3rd generation of SQL Server Columnstore.
  • As of SAP BW 7.40 (SP8) you can apply an optimized table structure for BW cubes by converting them to Flat Cubes.
  • Finally, as of SAP BW 7.50 (SP4) you can use the optimzed SQL statement generator as described above.

To benefit from all improvements, your SAP release planning should include an upgrade to SAP BW 7.50 SP4 (or higher) and SQL Server 2016.