Direct calculation of KPIs in CDS views

[juan_suros]

SAP has tamed the factory calendar information stored in table TFACS in a series of CDS views:

Eclipse name	SE11 name	Description
FCLM_BAM_DATE1	VFCLMBAMD1	Split Factory Calendar year into months
FCLM_BAM_DATE2	VFCLMBAMD2	Split Factory Calendar months into days
FCLM_BAM_DATE3	VFCLMBAMD3	List valid Factory Calendar days
FCLM_BAM_DATE4	VFCLMBAMD4	Example: Calculate next Working day by Calendar day

In order; these views split factory calendar data by month of year, then by day of month, and then by valid and formatted Calendar days with work days marked. The fourth example above shows how to use SQL aggregation functions to calculate further results from this data.

In the example below I demonstrate a list of Sales Order line items, with associated Delivery Note item, and Invoice item. The creation date of each document is considered and the number of working days between creation of each document is calculated. This example uses a hard-coded Factory Calendar "K5". You will want to consult the assignment of Factory Calendars in your own systems. See assignment by Plant (T001W-FABKL) for instance.

An initial view based on document flow is extended to working day calculations, and then assembled into the unified KPI view.

define view ZZCV_KPI_FC as
select from vbak
inner join vbap on vbap.vbeln = vbak.vbeln
left outer join vbfa as f1 on f1.vbelv = vbap.vbeln
                          and f1.posnv = vbap.posnr
                          and f1.vbtyp_v = 'C' -- from Sales Order
                          and f1.vbtyp_n = 'J' -- to Delivery Note
left outer join vbfa as f2 on f2.vbelv = f1.vbeln
                          and f2.posnv = f1.posnn
                          and f2.vbtyp_v = 'J' -- from Delivery note
                          and f2.vbtyp_n = 'M' -- to Invoice
{
key vbak.vbeln as so,
key vbap.posnr as so_item,
vbak.erdat as so_date,
f1.vbeln as dn,
f1.posnn as dn_item,
f1.erdat as dn_date,
f2.vbeln as inv,
f2.posnn as inv_item,
f2.erdat as inv_date
}

 

define view zzcv_kpi_days_so_dn as
select from ZZCV_KPI_FC as kpi
inner join VFCLM_BAM_DDL_DATE3 as fc1 on fc1.ident = 'K5'
                                     and fc1.date_sap >= kpi.so_date
                                     and fc1.date_sap <= kpi.dn_date
                                     and fc1.day_sap_w = '1' -- include working days only
{
key kpi.so,
key kpi.so_item,
key kpi.dn,
key kpi.dn_item,
count(distinct fc1.date_sap) as days_so_dn
} where kpi.dn_date is not null
group by kpi.so, kpi.so_item, kpi.dn, kpi.dn_item
 

define view zzcv_kpi_days_so_inv
as
select from ZZCV_KPI_FC as kpi
inner join VFCLM_BAM_DDL_DATE3 as fc1 on fc1.ident = 'K5'
                                     and fc1.date_sap >= kpi.so_date
                                     and fc1.date_sap <= kpi.inv_date
                                     and fc1.day_sap_w = '1' -- include working days only
{
key kpi.so,
key kpi.so_item,
key kpi.dn,
key kpi.dn_item,
key kpi.inv,
key kpi.inv_item,
count(distinct fc1.date_sap) as days_so_inv
} where kpi.inv_date is not null
group by kpi.so, kpi.so_item, kpi.dn, kpi.dn_item, kpi.inv, kpi.inv_item
 

define view zzcv_kpi_days_dn_inv as
select from ZZCV_KPI_FC as kpi
inner join VFCLM_BAM_DDL_DATE3 as fc1 on fc1.ident = 'K5'
                                     and fc1.date_sap >= kpi.dn_date
                                     and fc1.date_sap <= kpi.inv_date
                                     and fc1.day_sap_w = '1' -- include working days only
{
key kpi.so,
key kpi.so_item,
key kpi.dn,
key kpi.dn_item,
key kpi.inv,
key kpi.inv_item,
count(distinct fc1.date_sap) as days_dn_inv
} where kpi.dn_date is not null
    and kpi.inv_date is not null
group by kpi.so, kpi.so_item, kpi.dn, kpi.dn_item, kpi.inv, kpi.inv_item
 

define view zzcv_kpi_calculated as
select from ZZCV_KPI_FC as kpi
left outer join zzcv_kpi_days_so_dn as k1 on k1.so = kpi.so
                                         and k1.so_item = kpi.so_item
                                         and k1.dn = kpi.dn
                                         and k1.dn_item = kpi.dn_item
left outer join zzcv_kpi_days_dn_inv as k2 on k2.so = kpi.so
                                          and k2.so_item = kpi.so_item
                                          and k2.dn = kpi.dn
                                          and k2.dn_item = kpi.dn_item
                                          and k2.inv = kpi.inv
                                          and k2.inv_item = kpi.inv_item
left outer join zzcv_kpi_days_so_inv as k3 on k3.so = kpi.so
                                          and k3.so_item = kpi.so_item
                                          and k3.dn = kpi.dn
                                          and k3.dn_item = kpi.dn_item
                                          and k3.inv = kpi.inv
                                          and k3.inv_item = kpi.inv_item
{
key kpi.so,
key kpi.so_item,
kpi.so_date,
kpi.dn,
kpi.dn_item,
kpi.dn_date,
kpi.inv,
kpi.inv_item,
kpi.inv_date,
k1.days_so_dn,
k2.days_dn_inv,
k3.days_so_inv
}

My development system returns 900k rows in just under a minute. I'd appreciate any performance tips readers can suggest.

This simple example can be extended with any columns you would like to drill down on for cause of slow performance. Additional aggregation calculations can be added at database level or in the display tools.