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.
A FEMS filter ( F orm E le M ent S elektion) 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:
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.
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)
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.
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.
The new statement generator can only be used, when the following prerequisites are fulfilled:
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:
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 .
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.