Enhancements in BW4HANA 2.0 SP04

CompositeProvider offers extensive functionalities for merging data from BW InfoProviders as well as for creating mixed scenarios with HANA Views. Either by Joining or combining multiple data providers. With SAP BW4HANA 2.0 SP04 many new enhancements were included, which are summarized and demonstrated in this Blog.

By Vahan Nersesyan, Analytics Consultant

1 June 2020

With SAP BW4HANA 2.0 SP04 multiple new enhancements were included. Many of these functionalities already existed in HANA Calculation Views. Nevertheless, I find it interesting to see their BW alternatives.

In this Blog, the following topics are addressed. At the end, a simple case study depicts its use cases:

Aggregation and Projection
SQL Filter
Calculated Fields and Normal Fields
Duplicate Fields

Aggregation and Projection

Previously, there was no standard functionality for aggregating key figure values in the target structure of a CompositeProvider. This behavior is similar to the functionality already provided in HANA Calculation Views. Mostly it is necessary when joining data of multiple transactional Providers on different aggregation states. This type of Aggregation is performed at database level. Projection on the other hand, is mainly there for defining SQL Filters on specific PartProviders such as ADSOs. More on this under “HANA SQL Filter” Subtopic.

HANA SQL Filter

The SQL Filter is a powerful tool that allows filtering out specific data from the whole CompositeProvider or its PartProviders. It allows creation of additional filters on different node levels of a CompositeProvider. Directly on the Top Node or on its different parts, such as on specific Unions, Joins, Projections and Aggregations underneath. However, such filters cannot be directly defined on a PartProvider. Therefore, an Aggregation or a Projection node has to be added on top of it, in order to enable this functionality. In an SQL Filter, both Calculated Fields as well as normal fields can be used to build an expression. HANA SQL Script language is used for these purposes. A list of available expressions are shown when selected. There is already an extensive HANA SQL Script reference available here(opens in new tab) for consumption.

Calculated Fields and Fields

Calculations Fields is another HANA DB functionality integrated in the CompositeProvider. It offers a list of SQL Expressions. Simple Fields can also be added as well. Both can be defined either as a Characteristic or as a key figure. As a characteristic the option “Forced Group” can also be enabled. It is important to note that in case of Temporal Joins the SQL filters, SQL expressions, Force Group By and aggregation change are not allowed.

Duplicate Fields

After migrating from MultiProvider to CompositeProvider the cross-mapping functionality was not supported any more. As a workaround, Duplicate Fields can be created now. In this case a template is taken from the original Object with a custom naming possibility. The “Force Group By” functionality can now be enabled for all Fields and Characteristics. A symbol appears marking this functionality.

Case Study:

Our example implementation includes 3 transactional (Data Mart) ADSOs with different granularity levels.

ADSO1 (DE)

Characteristics: Country DE, Fiscal Year, Fiscal variant
Key Figure: Amount

ADSO3 (DE&CH)

Characteristics: Country DE & CH, Date, Fiscal Year, Fiscal variant
Key Figure: Amount

ADSO2 (Cost Element Information)

Characteristics:Cost Element, Controlling Area, Fiscal Year, Fiscal variant
Key Figure: Value in LC

Raw Data:

In this case study, we would like to combine DE data from ADSO1 with CH information of ADSO3 as well as to enhance ADSO1 data with ADSO2 values. At the end of this case study the result data preview is shown.

Step 1: Since ADSO1 and ADSO2 have different granularity, an additional aggregation is built on top of ADSO2. For this purpose, we need to Aggregate ADSO2 data, remove the Cost Element Information and join with ADSO1.

Step 2: In our example, we retrieve current date information as a Calculation Field, type Characteristic. The “Forced Group By” is enabled for 0FISCYEAR.

Step 3: ADSO1 is joined with the aggregation of ADSO2. Here we define a Calculation Field, type Key Figure. In our example, it is a simple variance between “Amount” and “Amount in LC”. On the level of Join we use this Calculation Field to filter out specific data combinations directly from Join.

Step 4: ADSO3 includes both DE and CH data, in order to avoid duplicates with ADSO1, we create a projection on top of it and use SQL Filter to select only CH data.

Step 5: In the Union, the date field from ADSO3 is mapped to Calculation Field for current date from Join. The Duplicate Field “Fiscal Year” (0FISCYEAR_D) is mapped for ADSO3 from the navigational attribute of 0DATE and for others 0FISCYEAR is taken.