SAP Fiori Overview Page – Intelligent Top N List Card

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 Dmitry 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)