SAP Fiori Overview Page – Intelligent Top N List Card

[uladzislau_pralat]

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)