Improvements of BW/4HANA Analytical Engine in Q1 2019 with BW/4HANA 2.0

Let us have a glimpse on each of the topics in detail.

Conditions evaluated in SAP HANA

With BW/4HANA 2.0 also the execution of queries with conditions can be pushed down to HANA. For this we introduced a new value 9 for query property ‘Operations in BWA/HANA’ (with technical widely known name TREXOPS). Setting TREXOPS to this value, the OLAP engine tries to push down the calculation of the query containing conditions to HANA.

Each condition is in general applied on the subtotal levels it fits to. This evaluation process runs from the coarsest to finest subtotal level. When conditions can be calculated in SAP HANA, then for each matching subtotal level a pre-query is run that returns matching characteristic tuples. The result of this pre-query is later used as a filter.

As coarser subtotal levels contain also data outside the condition filter of a finer subtotal level, the data of the finer subtotal level cannot be aggregated to the coarser subtotal levels. Instead, data for the coarser subtotal levels needs to be retrieved separately from the database.

However, following prerequisites and constraints have to be taken into account (also documented with TREXOPS F1 help):

Supported operators

A condition parameter can only be calculated in SAP HANA if it uses a supported operator.

Supported ranking operators are TopN, BottomN, TopSUM and BottomSUM
Supported having operators are Equal, NotEqualTo, LessThan, GreaterThan, LessEqual, GreaterEqual, Between and NotBetween

Prerequisites for conditions calculated in SAP HANA

Prerequisites for calculation of conditions in SAP HANA are:

◈ All condition parameters can be calculated in SAP HANA.
◈ NULL values never fulfill the condition because

     ◈ NULL values that are considered as zero during condition processing can’t fulfill the condition

or

     ◈ the flag to ignore NULL values is set in the definition of the condition. The flag ignore NULL values can either be set via the BW Modeling Tools for individual conditions of one query or via the report RSO_RES_QD_CONDMODE for all conditions of multiple queries.

Structure member used by condition parameter

A condition parameter can only be calculated in SAP HANA, if the used structure members can be calculated in SAP HANA.

◈ The structure members can be calculated in SAP HANA with the option ‘Formulas calculated in SAP HANA with complex currency/unit’.

◈ The structure members fulfill all prerequisites for the calculation in SAP HANA with the option ‘Formulas calculated in SAP HANA with complex currency/unit’ except the prerequisite that it has an exception aggregation.

Condition parameters using formulas

A condition parameter that uses a structure member which is a formula can only be calculated in SAP HANA, if the following prerequisites are given:

◈ All operands of the formula have the same constant selection context.
◈ The formula result is NULL, when all formula operands are NULL.

Limitations for conditions using ranking operators

If more than one condition with ranking operators is applied on a subtotal level, then conditions can’t be calculated in SAP HANA for this subtotal level.

If only one of the conditions applied on a subtotal level uses ranking operators, then for this subtotal level only this condition is calculated in SAP HANA.

RSECADMIN in BW/HANA Web UI

In order to support a modern new Web UI on BW/4HANA we developed a new UI5 based administration UI to maintain analytic authorization. The scope is to maintain single analytical authorizations.

You can find it in the section Modeling under ‘Model Analysis Authorizations’.

Here you can specify the analytical authorization which you want to maintain. You can maintain the special characteristics like InfoProvider, Key Figures, and Activity. Then the normal authorization relevant characteristics are maintained.

Other functions of transaction RSECADMIN can also be accessed through the WebGUI in the section User Management. Here also the security protocol as well as other functions like mass maintenance, generation, and transport can be accessed.

Variable Type BRF+

In cloud environments the use of ABAP is restricted. This leads to the need of having languages available or more rule based functions. we now also enabled BRF plus for variables.  For variables we offer a new type of variables, called ‘BRFplus Exit’.

The only thing you must maintain in the variable screen is the BRFplus function.

To create a BRFplus also the utility program RSR_BRFPLUS_TOOL is provided. Running this program, a new BRFplus function is created. This can then be used to maintain the rules like decision tables.

Read Access Logging (RAL) integration

For read access logging BW offered so various solutions all having some limitations as describe in the BW/4HANA security guide. All of them had some disadvantages. For example for standard BW objects the LOPD solution described in the attachment of note 933441 and the BW/4HANA security guide was proposed as standard solution. However, the characteristic needed to be authorization relevant and CDS queries were excluded. As alternative the BW UI Logging is a good alternative for BW. But this is not shipped by the standard, requires an extra license and misses newer UIs like SAP analytics cloud. On the other side the ABAP Read Access Logging from the ABAP application server offered for various channels like Dynpro, WebDynpro, oDATA and WebServices a good service to provide read access logging but it missed the query channel. We now have developed a new ‘Analytics’ channel fully integrated into Read Access Logging in order to overcome this limitation.

First data protection and read logging characteristics must be flagged as such. For this we introduced new fields on the ‘Extended’ tab of the InfoObject maintenance in the BWMT. Here you set the business area and the log domain of you RAL configuration.

This should not be mixed up with data protection relevant flag on the ‘General’ tab used in deletion of data.

Then using the transaction SRALMANAGER you can setup your own configuration. You can add single query names as condition.  For queries, usually you choose fields for the log group as shown below

On top you can decide if you want to also log the output/result of the query. For this a flag ‘ Log Read Access Output’ exists on various providers like Composite Provider, InfoObject, and aDSO.

Custom Exception Aggregation and IF Operator for Analysis Office and SAC

One thing that was still missing was the IF operator which has now been added and will be shipped with Analysis Office 2.8.

One thing that was still missing was the IF operator which has now been added and will be shipped with Analysis Office 2.8. From backend side this is supported from SAP NW 7.50 SP7 onwards.

In addition, in the HANA connection of Analysis Office or SAC the custom created exception aggregation was missing. With BW/4HANA 2.0 we support also this feature on backend side. BW in general supports multiple dimensions to be selected for the exception aggregation. AO and SAC are adapting now. However, due to the limitation on HANA direct connection maybe only one is offered by the UIs in AO. Also, the set of exception aggregations that BW in offers general might be limited by the UI.

SAC Adoption through BW Live Connection

When BW live connection was released for SAP Analytics Cloud we mainly focused on the table control. While certain charts- such as bar chart – already were usable, other chart types have very special requirements towards the backend. We therefor focused on enabling the missing chart types.

The list of chart limitation lifted with BW/4HANA 2.0 are

◈ Linked Analysis with more than one dimension

◈ Geo Map

◈ Waterfall Chart

◈ Time Series Chart

◈ Variance Chart based on different measures

◈ Concurrent Conditions (backend condition + frontend topN function)

Linked Analysis with more than one dimension

One of the challenges was the tuple filter which arises in linked analysis when having more than one dimension like in the heat map shown below. Tuple filter take the combination of characteristic values on different characteristics into account. In BW the filter was always the cartesian product of characteristic values on different dimension. Example: Consider dimension A and B and a tuple filter (A=1 AND B=’X’) OR (A=3 AND B=’Y’)  OR (A=1 AND B=’Z’)  then the cartesian product filter would be ( A=1 OR  A=3 ) AND (B=’X’ OR B=’Y’ OR B=’Z’). In linked analysis the sums shown on the chart on the right hand would have different numbers. For Cola we would expect then 153 thousand while for IceTea we would expect 342 thousand.

Tuple Filter + Linked Analysis

Individual tuple sums in BW could only be done on Excel frontends like analysis office or EPM client with local excel formula. To support this in linked analysis use cases, we now developed the tuple filter in BW and the OLAP engine calculates the sum only from the tuples specified in the filter. In BW we still have limitations with tuples on hierarchies and on leading NOT. Also, in linked analysis we will not support 2 sources or a source with a general advanced filter above tuple. We currently work on lifting the limitations as far as possible in an upcoming feature pack. Parts will also be available with an feature pack on NW BW .750 (planned SP16).

Geo Map

In BEX-Web and Lumira Designer (formerly named Design Studio) had already a Geo solution which was handled purely on the frontend side to map query results to maps. Only the meta and master data of the InfoObject and shape files were read from the backend. The metadata setting on the InfoObject was reused for the new SAP Analytics Cloud based solution. But now the geo information, filter etc. are handled in the HANA spatial engine.

To enable geo maps on BW live models one has to set the Geographical Type for a characteristi

Valid types are  ‘Point Data’ (old value Dynamic Geographical Characteristic), ‘Derived Data Point from Attribute’ and ‘Shapes and Data Point’.

Then the attributes longitude and latitude are automatically added to the InfoObject.

Now you can load master data with geographical coordinates longitude and latitude in staging

For the new solution a creation of a shadow table in Hana with Hana Spatial Type is also triggered behind the scenes. Reason is this is that the DDIC Type is still missing in the ABAP Application Server.

This table is used for the HANA geo operations orchestrated by SAC. Therefor also the HANA database with the uploaded Geo shapes is a prerequisite.

Then you can use any query containing geo InfoObjects and a BW live model on top in SAC. You can use those models in any Geo chart and use various layers. Only the choropleth layer and point of interests will not be supported at the beginning. Choropleth will be added by the next feature pack in BW/4HANA as well as in NW BW 7.50 (here planned SP16) . Geo filtering as well as the within distance filter is working as in the HANA live connections.

In the picture above for example we see the flow layer on top of a bubble layer.

Waterfall Chart

Waterfall chart is a chart type in the category of comparison

There was an old implementation for the waterfall chart which required for each node the full hierarchy path in the response. Due to performance reason we did not want to introduce this in BW. Now with the new implementation of the waterfall chart also BW live connection is supported. On the backend you need BW/4HANA 2.0 or notes 2688726 and 2734214. For variance calculation also note 2660034 and 2745245 is needed.

Time Series Chart

The BW dates like 0CALDAY or 0CALWEEK were at the beginning not understood and recognized by SAC and as consequence the time series did not work at all. Then the basic data for day, week, month, quarter and year were understood but the hierarchy navigation was missing. Reason behind is that in BW the time hierarchies are normal parent-child hierarchies while SAC expect level-based hierarchies. We translated now the parent-child hierarchies for SAC into level-based hierarchies. However, to work, all time dimension contained in the specific hierarchies must be part of the query. At least they need to be free characteristics. It helps that BW support for normal time dimensions to add time dimensions with lower granularity as navigational attributes from 7.50 onwards. For example, 0CALDAY has 0CLAMONTH and 0CALYEAR as navigational attributes. So those time dimensions of lower granularity can be added quite easily on Composite Provider level and added to the query as free characteristics. They need not be part of the underlying base provider like an aDSO. As a consequence no booked data needs to be changed when the scenario is enabled.

From the backend side this is supported with note 2637157 INA: Support time level hierarchies. Minimal requirements are NW BW7.50 support package 06 or 7.51 SP01. For BW/4HANA we support it from SP4 onwards. It is also included in S/4HANA cloud 1902 delivery. Note, the validity depends on the formula refactoring note 2643164.

Variance Chart based on different measure

When the BW-live connection was released also custom restrictions and formulas were working. However, they were recreated after a variable submit and so any objects like topN based on those custom created formula/restrictions failed. Same would have been true for variances which are usually a custom formula based on the difference of two custom restrictions. So, we first had to make the solution of custom formulas more stable (rework in the RSBOLAP layer) and we needed to be careful since the solution was already used by a lot of analysis office customers where the error due to the replay through the query view was not present. With note 2643164 we accomplished this. However, this note requires NW BW 7.50 SP08 or 7.51 SP03 and BW/4HANA SP04.  Note 2660034 INA: Support SAC Variance Charts follows this validity. It releases the variance chart support also for lower releases on two different measures. We currently start working to also support versions (also note 274524 is needed then ) and time for the next release.

Concurrent Conditions (backend + frontend topN)

From the beginning of the live connection we supported topN filter of the SAC frontend. In order to avoid collisions with backend condition (disabled in former releases), we have done further development in BW/4HANA 2.0 and now we are able to support them both (frontend and backend). We also show on the frontend if backend conditions exist.

Most of the features are also available for NW 7.50 and listed in note 2541557 Support further SAP Analytics Cloud BW features. Following note 2541557 in regular intervals is the recommended procedure. An exception is geo and tuple filter support. For NW BW 7.50 this requires an upgrade to support package 15.