Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
uladzislau_pralat
Contributor
1,270
SAP Fiori Overview Page is a part of overall S/4 HANA user experience. That is why Overview Page response time is very important aspect. In my blog I will explain how to turn your List Card into Intelligent Top N List Card improving performance.

SAP Fiori Overview Page List Card displays not aggregated data on detailed level. It means even though only first N records are displayed on List Card all records are read by OData service to calculate record count. This might cause Overview Page slow response time.



As you can see from the screenshot above only top 3 flights care displayed. List Card is meant to give a preview of Top N items. Total number of items is not that important. Potentially is can be thousand if not millions of records.

Special thanks to d_kuznetsov whose idea of CDS Select Top N I took to a next level.

The main idea of Intelligent Top N with CDS View is to provide Top N items for each and every Overview Page selection criteria combination. It means no matter what your selection can be it is guaranteed that Top N items will be displayed (still significantly improving performance).



With Intelligent Top N OData Service read almost 3 times less data. In real life scenarios reduction can be hundreds of times which result in significant performance improvement.



In my case Intelligent Top N with CDS View is a union of:

  • Top N

  • Top N for each continent

  • Top N for each continent / airline

  • Top N for each continent / airline / connection


Intelligent Top N is implemented using ABAP CDS table function SQL rank window functions. See below for more information:
CLASS zcl_sapbc_flight_top_n DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .

PUBLIC SECTION.

INTERFACES if_amdp_marker_hdb .

CLASS-METHODS function
FOR TABLE FUNCTION zsapbc_flight_top_n_fact.

ENDCLASS.

CLASS zcl_sapbc_flight_top_n IMPLEMENTATION.
METHOD function BY DATABASE FUNCTION
FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY
USING sflight.

it_data_1 =
SELECT mandt,
CASE
WHEN carrid = 'LH' or carrid = 'AB' or carrid = 'AF' or carrid = 'AZ' or carrid = 'BA' or carrid = 'NG' or carrid = 'SR'
THEN 'EUR'
WHEN carrid = 'AA' or carrid = 'UA' or carrid = 'CO' or carrid = 'DL' or carrid = 'NW' or carrid = 'WA' or carrid = 'AC'
THEN 'NA'
WHEN carrid = 'SA'
THEN 'AFR'
WHEN carrid = 'FJ' or carrid = 'JL' or carrid = 'SQ'
THEN 'ASIA'
WHEN carrid = 'QF'
THEN 'AU'
END AS continent,
carrid,
connid,
fldate,
currency,
P_DisplayCurrency as currency_disp,
seatsmax + seatsmax_b + seatsmax_f AS seatsmax,
seatsocc + seatsocc_b + seatsocc_f AS seatsocc,
seatsocc / seatsmax as seatsocc_prc,
paymentsum as payment,
CONVERT_CURRENCY(
amount=>paymentsum,
"SOURCE_UNIT" =>currency,
"SCHEMA"=>'SAPA4H',
"CONVERSION_TYPE" => 'M',
"TARGET_UNIT" =>:P_DisplayCurrency,
"REFERENCE_DATE" =>fldate,
"ERROR_HANDLING"=>'set to null',
"CLIENT" => '100') as payment_disp
FROM sflight;


*** Rank1 = 1 ***
it_data_2 =
SELECT mandt, continent, carrid, connid, fldate, currency, currency_disp, seatsmax, seatsocc, seatsocc_prc, payment, payment_disp,
RANK ( ) OVER ( PARTITION BY mandt ORDER BY payment_disp DESC ) AS rank2
FROM :it_data_1;
it_data_3 =
SELECT mandt, continent, carrid, connid, fldate, currency, currency_disp, seatsmax, seatsocc, seatsocc_prc, payment, payment_disp,
'1' as rank1, rank2
FROM :it_data_2
WHERE rank2 between 1 and :P_TopN;
*** Rank1 = 2 ***
it_data_4 =
SELECT mandt, continent, carrid, connid, fldate, currency, currency_disp, seatsmax, seatsocc, seatsocc_prc, payment, payment_disp,
RANK ( ) OVER ( PARTITION BY mandt, continent ORDER BY payment_disp DESC ) AS rank2
FROM :it_data_1
WHERE NOT EXISTS ( SELECT * FROM :it_data_3 WHERE Mandt = :it_data_1.Mandt
AND continent = :it_data_1.continent
AND carrid = :it_data_1.carrid
AND connid = :it_data_1.connid
AND fldate = :it_data_1.fldate );
it_data_5 =
SELECT mandt, continent, carrid, connid, fldate, currency, currency_disp, seatsmax, seatsocc, seatsocc_prc, payment, payment_disp,
'2' as rank1, rank2
FROM :it_data_4
WHERE rank2 between 1 and :P_TopN;
*** Rank1 = 3 ***
it_data_6 =
SELECT mandt, continent, carrid, connid, fldate, currency, currency_disp, seatsmax, seatsocc, seatsocc_prc, payment, payment_disp,
RANK ( ) OVER ( PARTITION BY mandt, continent,carrid ORDER BY payment_disp DESC ) AS rank2
FROM :it_data_1
WHERE NOT EXISTS ( SELECT * FROM :it_data_3 WHERE Mandt = :it_data_1.Mandt
AND continent = :it_data_1.continent
AND carrid = :it_data_1.carrid
AND connid = :it_data_1.connid
AND fldate = :it_data_1.fldate )
AND NOT EXISTS ( SELECT * FROM :it_data_5 WHERE Mandt = :it_data_1.Mandt
AND continent = :it_data_1.continent
AND carrid = :it_data_1.carrid
AND connid = :it_data_1.connid
AND fldate = :it_data_1.fldate );
it_data_7 =
SELECT mandt, continent, carrid, connid, fldate, currency, currency_disp, seatsmax, seatsocc, seatsocc_prc, payment, payment_disp,
'3' as rank1, rank2
FROM :it_data_6
WHERE rank2 between 1 and :P_TopN;
*** Rank1 = 4 ***
it_data_8 =
SELECT mandt, continent, carrid, connid, fldate, currency, currency_disp, seatsmax, seatsocc, seatsocc_prc, payment, payment_disp,
RANK ( ) OVER ( PARTITION BY mandt, continent,carrid, connid ORDER BY payment_disp DESC ) AS rank2
FROM :it_data_1
WHERE NOT EXISTS ( SELECT * FROM :it_data_3 WHERE Mandt = :it_data_1.Mandt
AND continent = :it_data_1.continent
AND carrid = :it_data_1.carrid
AND connid = :it_data_1.connid
AND fldate = :it_data_1.fldate )
AND NOT EXISTS ( SELECT * FROM :it_data_5 WHERE Mandt = :it_data_1.Mandt
AND continent = :it_data_1.continent
AND carrid = :it_data_1.carrid
AND connid = :it_data_1.connid
AND fldate = :it_data_1.fldate )
AND NOT EXISTS ( SELECT * FROM :it_data_7 WHERE Mandt = :it_data_1.Mandt
AND continent = :it_data_1.continent
AND carrid = :it_data_1.carrid
AND connid = :it_data_1.connid
AND fldate = :it_data_1.fldate );
it_data_9 =
SELECT mandt, continent, carrid, connid, fldate, currency, currency_disp, seatsmax, seatsocc, seatsocc_prc, payment, payment_disp,
'4' as rank1, rank2
FROM :it_data_8
WHERE rank2 between 1 and :P_TopN;

it_data_11 =
SELECT mandt, continent, carrid, connid, fldate, rank1, rank2, currency, currency_disp,
seatsmax, seatsocc, seatsocc_prc, payment, payment_disp
FROM :it_data_3

UNION ALL

SELECT mandt, continent, carrid, connid, fldate, rank1, rank2, currency, currency_disp,
seatsmax, seatsocc, seatsocc_prc, payment, payment_disp
FROM :it_data_5

UNION ALL

SELECT mandt, continent, carrid, connid, fldate, rank1, rank2, currency, currency_disp,
seatsmax, seatsocc, seatsocc_prc, payment, payment_disp
FROM :it_data_7

UNION ALL

SELECT mandt, continent, carrid, connid, fldate, rank1, rank2, currency, currency_disp,
seatsmax, seatsocc, seatsocc_prc, payment, payment_disp
FROM :it_data_9;



RETURN
SELECT mandt, continent, carrid, connid, fldate, RANK ( ) OVER ( PARTITION BY mandt ORDER BY payment_disp DESC ) AS rank, rank1, rank2, currency, currency_disp,
seatsmax, seatsocc, seatsocc_prc, payment, payment_disp
FROM :it_data_11;

ENDMETHOD.
ENDCLASS.

 

Note: do not forget to drill down by rank first in order to have item sorted correctly (in my case by payment amount descending)



 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
3 Comments
Labels in this area