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):
To get a record with MAXIMUM date value from a set of records with same key fields
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):
For same ITEM, LOC combinations, if we have multiple SUBITEMS & user expects all the entries.
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.