Describing the SAP BW/4HANA Analytic Manager

The OLAP engine has always been a key component in SAP BW to handle all query processing. Processing was always executed in the ABAP runtime. Since SAP HANA became the database to power SAP BW/4HANA, a new component called the Analytic Manager was introduced. This acts as a broker to define the most efficient execution path for processing the query runtime. This means it synchronizes processing on the SAP HANA platform with processing on the SAP BW/4HANA ABAP Application Server with the purpose of pushing as many calculations as possible down to SAP HANA to achieve the best performance.

The BW Query Designer is the design tool of the Analytic Manager. In SAP BW/4HANA, the classic OLAP operations are no longer exclusively handled by the ABAP application layer, but are increasingly executed by the calculation engine of SAP HANA instead. The analytic manager converts existing SAP BW OLAP queries to SAP HANA optimized scripts that use SAP ABAP functions to enable push-down processing that exploits SAP HANA performance capabilities. This includes support of advanced SAP BW features, such as exception aggregation for count and currencies key figures, hierarchies, restricted key figures, or non-cumulative key figures (inventory and headcount scenarios, and so on).

The system always checks whether operations in SAP HANA are possible for the relevant InfoProvider. It is the task of the new Runtime Optimizer to select the best suited operations mode. When you execute a query, the optimizer decides whether the query is executed with less code push-down (negative decision) or using an enhanced expert mode (positive decision). If an enhanced expert mode is used, you get a smaller result set in the database (DBTRANS). This shortens the processing time of the result set in the analytic manager. On the other hand, there are additional setup costs for enhanced expert modes. For this reason, it is only useful to execute a query with an enhanced expert mode if the benefit (shorter processing time for the result set returned by the database) is greater than the additional setup costs.

The Runtime Optimizer estimates the number of rows in the result set in the database for the positive decision [EST_DBTRANS_POSITIVE_DECISION] and for the negative decision [EST_DBTRANS_NEGATIVE_DECISION]. The estimation only includes those parts of the query that are executed differently with a positive or negative decision. The estimation takes global filters into account and calculates a reduction factor by which the result set in the database is smaller with a positive decision: [EST_DBTRANS_NEGATIVE_DECISION] / [EST_DBTRANS_POSITIVE_DECISION].

SAP BW/4HANA introduces the so-called Runtime Optimizer concept, resulting in new relative runtime modes for Operations in SAP HANA. The purpose of these modes is to manage the processing in SAP HANA (that is, the code push-down) in a more sophisticated and flexible manner. They replace the absolute modes that were previously available.

• J Defensive: This mode corresponds to expert mode (3).

• M Standard: The optimizer decides whether the BW query is executed with expert mode (8) or (3). M is the default setting for new CompositeProviders, InfoObjects, and queries in SAP BW/4HANA.

• P Offensive: The optimizer decides whether the query is executed with expert mode (9) or (3).

Note that the system executes a one-time automatic mapping of the previous (fixed) expert modes to the new (relative) standard modes:

• Previous setting = 0, 2 or 3 — new setting = J

• Previous setting = 6, 7, or 8 — new setting = M

• Previous setting = 9 — new setting = P

There is a switch to go back to the former absolute runtime modes. As described in SAP note 2094114, you can set the RSROA_TREXOPS_EXPERT switch for all users through an entry in the RSADMIN table, as well as user-specifically as a user parameter. The switch enables all Operations in SAP HANA modes. The following values are possible:

• ON: All Operations in SAP HANA modes (relative and absolute) are shown.

• OFF (or no value): Only the relative Operations in SAP HANA modes are shown. The exception is that if the mode of an object is set to an absolute value, this value will be shown as well.

The absolute modes (aka expert modes) distinguish between seven different runtimes:

• Mode 0: No Operations in SAP HANA

  If you do not want the system to use optimized operations in SAP HANA for the selected query, choose this option. This may be necessary, for example, if queries use virtual characteristics or key figures, and the calculations of these characteristics or key figures depend on a specific level of aggregation. Each single InfoProvider is accessed by the generated SQL statement. The statements are executed in parallel, with no more than six at the same time. Read access is restricted to the SQL syntax and is similar to the read access on other databases. The SQL execution on SAP HANA is significantly faster than on traditional databases.

• Mode 2: Individual PartProvider Access

  Each single InfoProvider is accessed by a call of the SAP HANA API against the generated InfoProvider ColumnView. The SAP HANA API includes pushing down cell-based filters from restricted key figures (the FEMS), and an optimized handling of hierarchy aggregation and filtering. The SAP HANA API is called in parallel for each InfoProvider, with no more than six at the same time.

• Mode 3: Optimized Access

  For CompositeProviders, joint optimized access is made to all participating InfoProviders. The CompositeProvider is analyzed, and a cluster of InfoProviders with a homogenous mapping is created. A CalculationScenario is created in SAP HANA at runtime, combining the ColumnViews of the InfoProviders. Then a single statement is executed by the SAP HANA API for this cluster. The other InfoProviders are accessed independently, but in parallel. The difference from mode 2 is that UNION operations of CompositeProviders are also pushed into SAP HANA.

• Mode 6: Exception Aggregation

  This is the execution mode where all OLAP/calculation operations are pushed down to SAP HANA, including the exception aggregation. For this purpose, a CalculationScenario is created that combines the InfoProviders and creates the OLAP calculation graph with its different layers. This optimization is supported for the following definitions:

  • No aggregation if there is more than one record [NO1]

  • No aggregation if there is more than one value [NO2]

  • Standard deviation [STD]

  • Variance [VAR]

• Mode 7: Formulas Calculated in SAP HANA

  In SAP HANA 1.0 SP11 and higher, numerous exception aggregations are supported, especially for calculating formula exception aggregations. A formula with exception aggregation can only be calculated in SAP HANA if the formula result does not have a currency or a unit, or the currency or unit of the formula result is taken over from another individual operand. This mode was introduced with SAP BW/4HANA 1.0 and also applies to BW 7.5 SP4.

• Mode 8: Formulas Calculated in SAP HANA with Complex Currency or Unit

  In addition to the Formulas Calculated in SAP HANA (7) mode, the system also supports formulas with a complex currency or unit. This mode was introduced with SAP BW/4HANA 1.0 SP08.

• Mode 9: Conditions Calculated in SAP HANA

  The system supports the calculation of conditions. This mode was introduced with SAP BW/4HANA 2.0 .

  • Supported ranking operators: TopN, BottomN, TopSUM, BottomSUM

  • Supported having operators: EqualTo/NotEqualTo, LessThan/GreaterThan, LessEqualThan/GreaterEqualThan, Between/NotBetween

This property can be altered for BW Queries, CompositeProviders, and InfoObjects in the BW Modeling Tools. For BW Queries, there is still the old option to set this parameter in the Query Monitor (transaction RSRT).

In the Query Monitor (transaction RSRT), beside of setting permanent parameters, you can also use the Execute+Debug functions. Here, it is possible to switch the SAP HANA optimization temporarily on or off, for simulation and testing purposes only.