Alternative Solution for Rank Node – HANA Modelling

About this document

This document discusses the issues identified due to the usage of RANK node in SAP HANA modelling & provide solutions to achieve the same result with different approaches.

Rank Node related issue scenarios for SAP S4 HANA

Rank Node gives inconsistent records if there are number of records with same keys.

Scenario 1 – Determine MAX value of one desired column

We need to report the record with MAX (value) of a particular column (example – VALIDTO Date).

When there are multiple records with same keys & different VALIDTO dates, then Rank node displays different record with each data refresh or data load to other targets from this HANA view as source.

Scenario 2 – Identify multiple records & process for the same based on user inputs

In Rank node, when we try to get all the records with same keys, based on the threshold value, we get inconsistent records sometimes. To identify multiple records and apply logic which is applicable only for the multiple SUBITEM records.

Alternative approach for Rank Node explained with examples

Scenario 1 (alternative Approach details):

Example:

To get a record with MAXIMUM date value from a set of records with same key fields

Steps:

• Join the node (for example Projection 1) which has multiple records to an Aggregation Node (example Aggregation 1) & select only the key fields based on which we want the MAX date value

• Right click on the required date column and select ‘Convert to Aggregated Column’ option
• In Properties of this column, set ‘Engine Aggregation’ as ‘Max’

• Create a Left Outer Join between Projection 1 (Left Table) & Aggregation Node (Right Table) & get date fields from both nodes.

• Create a flag to compare the Date fields and filter on only those records with same Dates.
• This will get a single record with MAXIMUM date.

We have other options like MIN & COUNT. This can be used based on requirements

Scenario 2 (Alternative approach details):

Example

For same ITEM, LOC combinations, if we have multiple SUBITEMS & user expects all the entries.

Steps:

• Copy the same node which has all the records (Copy of Projection 1)

• Create a Left outer join (JOIN1) with Projection 1 (Left Table) and Copy of Projection 1 (Right Table) with all the fields from the Left Table and only the SUBITEM field selected from the Right Table.

• Create a flag (FLAG1) in JOIN1 (created) to compare the 2 SUBITEM columns, if the values are equal, then set the flag as ‘False’ else as ‘True’
• Create 2 Projections based on JOIN1 each filtered on the new FLAG1 = True and False respectively
• Create another Left Outer Join (JOIN2) with the Projections (created in Step 4) wherein Projection with Flag1 = False is the Left table with all fields displayed in output and the Projection with Flag2 = True as Right table with only the FLAG1 field marked for output.
• In this join, the records which do not have multiple SUBITEMs will display null values.
• Create a new flag (FLAG2) wherein you compare the FLAG1 from both the Projections, and mention that if FLAG1 = NULL, then FLAG2 = ‘F’, else FLAG2 = FLAG1.
• Now based on the value in FLAG2, we can identify the records with multiple SUBITEMs. This can now be further processed based on user requirements and the records with single SUBITEM can be reported as it is without any changes.