RANK Logic by SQL RANK Function & SQL Logic & Calculation View Graphical & CE Function.
Scenario:
⦁ Consider a Non SAP load (e.g: Flat file load ) which Full load daily , gets loaded into HANA table.
⦁ Because of Full load , we get daily all transactions uploaded into HANA table, unless we implement any pseudo delta logic in source side.
⦁ We have chance of getting same transaction multiple times from source file , if there were multiple changes on any key figures for same Transaction ID.
⦁ For Example , Order 100000, on created on date have Order Qty as 10 KG.
⦁ On same day or next subsequent day for above transaction there is increase in order qty from 10 KG to 20 KG.
⦁ So from NON SAP source we get these transaction multiple times with old Value & new value Order Qty with Different Time Stamp.
Requirement
So our requirement to report only Transactions with latest time stamp data, which have most current Key figures from Source Data.
To achieve this RANK node can be useful in Calculation View.
This functionality is same as RANK function in SQL .
Column Table Creation:
CREATE COLUMN TABLE <schema name>.SALES_FLAT
( SAELSORDER INTEGER,
SALESITEM SMALLINT,
DOC_TYPE VARCHAR(4),
MATERIAL_NUM NVARCHAR(18),
ORDER_QTY TINYINT,
UNIT VARCHAR(2),
NET_VALUE DECIMAL(15,2),
CURRENCY VARCHAR(3),
CREATAED_AT TIMESTAMP
);
.
Then we load Day 1 Records, ( here loading based on SQL , instead Import File) for just showcase functionality,
DAY1 Load:
INSERT INTO SALES_FLAT VALUES( 10000,10,'ZOR','MAT0001',10,'KG',1500,'INR','2016-04-01 09:10:59');
INSERT INTO SALES_FLAT VALUES( 10000,10,'ZOR','MAT0001',20,'KG',2500,'INR','2016-04-01 09:11:00');
INSERT INTO SALES_FLAT VALUES( 10001,10,'ZOR','MAT0002',10,'KG',4500,'INR','2016-04-01 09:12:15');
INSERT INTO SALES_FLAT VALUES( 10002,10,'ZOR','MAT0003',20,'KG',3500,'INR','2016-04-01 09:13:10');
INSERT INTO SALES_FLAT VALUES( 10003,10,'ZOR','MAT0004',10,'KG',1500,'INR','2016-04-01 09:13:59');
INSERT INTO SALES_FLAT VALUES( 10004,10,'ZOR','MAT0005',10,'KG',1500,'INR','2016-04-01 09:14:59');
INSERT INTO SALES_FLAT VALUES( 10004,10,'ZOR','MAT0005',40,'KG',8500,'INR','2016-04-01 09:15:59');
DAY1
we have order 10000, item 10 having multiple changes with different time stamp.
We have Order 10004, item 10 having multiple changes with different time stamp.
DAY2 Load
DAY1 + DAY2 ( Full load 😞 in this case we have not implemented any Delta logic.
DAY 2 Load records:
INSERT INTO SALES_FLAT VALUES( 10000,10,'ZOR','MAT0001',20,'KG',2500,'INR','2016-04-02 09:10:59');
INSERT INTO SALES_FLAT VALUES( 10000,10,'ZOR','MAT0001',30,'KG',3500,'INR','2016-04-02 09:11:00');
INSERT INTO SALES_FLAT VALUES( 10001,10,'ZOR','MAT0002',20,'KG',5500,'INR','2016-04-02 09:12:15');
INSERT INTO SALES_FLAT VALUES( 10002,10,'ZOR','MAT0003',30,'KG',6500,'INR','2016-04-02 09:13:10');
INSERT INTO SALES_FLAT VALUES( 10003,10,'ZOR','MAT0004',20,'KG',7500,'INR','2016-04-02 09:13:59');
INSERT INTO SALES_FLAT VALUES( 10004,10,'ZOR','MAT0005',20,'KG',8500,'INR','2016-04-02 09:14:59');
INSERT INTO SALES_FLAT VALUES( 10004,10,'ZOR','MAT0005',50,'KG',9500,'INR','2016-04-02 09:15:59');
DAY2 load Time stamp is April 2nd 2016.
In DAY2 load we have same Transactions with Different Time Stamp & Different Key Figures values.
So here Requirement to Report Only the latest updated Changes from Table SALES_FALT.
RANK function by SQL logic:
SELECT SAELSORDER, SALESITEM, DOC_TYPE,MATERIAL_NUM, ORDER_QTY,UNIT, NET_VALUE, CURRENCY, CREATAED_AT,
RANK() OVER(PARTITION BY SAELSORDER, SALESITEM ORDER BY CREATAED_AT DESC ) AS "RANK " FROM SALES_FLAT ORDER BY SAELSORDER, SALESITEM;
In above we can see one Extra Column as RANK, in this column orders got sorted based on Created At Timestamp & assigned RANK value.
These Values got Derived based on SQL RANK Functionality.
In Above code, we did RANK based on PARTITION BY on columns Sales Order number & Item and ORDER BY CREATAED_AT DESC.
RANK Logic by SQL without RANK Function:
SELECT SAELSORDER, SALESITEM, DOC_TYPE,MATERIAL_NUM, ORDER_QTY,UNIT,
NET_VALUE, CURRENCY, CREATAED_AT,
(select count(*) from SALES_FLAT T1 where T1.SAELSORDER = T2.SAELSORDER AND T1.SALESITEM = T2.SALESITEM AND T1.CREATAED_AT < T2.CREATAED_AT) +1 as RANK from SALES_FLAT T2 order by SAELSORDER, SALESITEM;
Both Output were same , With RANK functionality & without RANK Functionality.
RANK Functionality introduced in HANA SP8.
RANK function by Calculation View:
RANK Node using in Calculation View by Graphical
Graphical Calculation View in RANK Node, after Selecting Required Table, need to Set values to Required Parameters like Sort Direction, Order By & Partition BY.
We have below one Check box to Generate Extra Column in Our Calculation View, which holds RANK Values.
There is Threshold Parameter, it is the place where we can Fix Value or pass input parameter, which applies on Newly Generate Column.
It means if we pass as 1, then it will report All records which having RANK = 1.
In Graphical Calculation View in RANK Node, after Selecting Required Table, need to Set values to Required Parameters like Sort Direction, Order By & Partition BY.
We have one Check box in above screen shot, to Generate Extra Column in Our Calculation View, which holds RANK Values.
There is Threshold Parameter, it is the place where we can Fix Value or pass input parameter, which applies on Newly Generated Column.
It means if we pass as 1, then it will report All records which having RANK = 1.
by Passing Input parameter to Calculation View, we get only those Records which we required, this input parameters works on newly generated column RANK.
Passing Value 1, we get latest Timpstamp Transaction items from Calculation view, this is because in RANK node , SORT order of CREATED AT field is Descending Order.
- SAP Managed Tags:
