Introduction
This paper presents in detail how to implement Cartesian Transformation in SAP ABAP CDS Views to convert a record with hundreds of measures to multiple records with few measures and their IDs.
This approach could be very useful when converting COPA and/or CCA data with hundreds of measures in each record to P&L data with multiple records; e.g., Sales, Rent, …, Revenues, Payroll, Utilities, …, Expenses for each original record and; e.g., , 2 measures in each record; e.g., current year and previous year values.
Some standard and custom SAP applications’ tables contain multiple measures in a single record as shown on the following screen:
In many financial reports; e.g., P&L, we want to display aggregated expenses and revenues one per row with two measure columns displaying amounts for the current and the previous time periods; e.g., November 2017 vs November 2016 as shown on the following screen:
To get data in P&L format shown on the screen above, one has to:
◈ Filter data for Time Periods further called Time Buckets
◈ Aggregate all expenses; e.g., advertising, utilities, …;, and revenues; e.g., sales, royalties, …; by Time Buckets
◈ Calculate Total Expenses and Total Revenues for Aggregated Records
◈ Calculate Profit and Profit to Revenue Ratio
◈ Transform All 10 original and 4 calculated measures in each aggregated record to single measure AMOUNT with measure id; i.e., UTILITIES, SALES, RENT, PROFIT, …; in 14 records
◈ Split single measure in transformed records for current CYCM and previous PYCM time buckets to 2 measures; i.e., AMOUNT_CYCM and AMOUNT_PYCM
◈ Aggregate transformed records to get current and previous time bucket amounts in a single record
◈ Convert Profit to Revenue Ratio to Decimal Number
In “Implementing Time Buckets in SAP ABAP CDS Views” paper I discussed how to group data in Time Buckets without performing Horizontal to Vertical Measures’ Transformation. The results for the same data were presented in the following format:
You could see on the above screen that Total Revenues, Total Expenses and Profit are the same as it was shown in P&L Report shown again on the screen below:
The main point discussed below is to demonstrate how to transform a single record with multiple measures to multiple records with single measure using Cartesian Transformation also called in this document Horizontal to Vertical Measures’ Transformation:
◈ Transform All 10 original and 4 calculated measures in each aggregated record to single measure AMOUNT with measure id; i.e., UTILITIES, SALES, RENT, PROFIT, …; in 14 records
Cartesian Transformation
When we multiply a matrix by another one containing value 1 in diagonal cells and zeros in all other cells, we get rows in the first matrix to appear as columns in the resulting matrix as shown below:
When we use the principals of matrix algebra with measures in database tables, we can convert multiple measures M1, M2, M3 and M4 in a single record to a single measure AMOUNT in multiple records with additional ID column as shown below:
The following sections of this document would explain in detail the entire process from:
◈ Filter data for Time Periods further called Time Buckets
◈ Aggregate all expenses; e.g., advertising, utilities, …;, and revenues; e.g., sales, royalties, …; by Time Buckets
◈ Calculate Total Expenses and Total Revenues for Aggregated Records
◈ Calculate Profit and Profit to Revenue Ratio
◈ Transform All 10 original and 4 calculated measures in each aggregated record to single measure AMOUNT with measure id; i.e., UTILITIES, SALES, RENT, PROFIT, …; in 14 records
◈ Split single measure in transformed records for current CYCM and previous PYCM time buckets to 2 measures; i.e., AMOUNT_CYCM and AMOUNT_PYCM
◈ Aggregate transformed records to get current and previous time bucket amounts in a single record
◈ Convert Profit to Revenue Ratio to Decimal Number
ZABL_HVT20 Transformation Table
In order to perform Cartesian Transformation; i.e., Horizontal to Vertical Measures’ Transformation one has to define a transformation table with value 1 in diagonal cells and value zero in all other cells. This could be achieved by defining the following database table:
define table zabl_hvt20 {
key dummy : abap.int4 not null;
key application : abap.char(20) not null;
key sort_by : abap.char(20) not null;
category1 : abap.char(20);
category2 : abap.char(20);
category3 : abap.char(20);
category4 : abap.char(20);
category5 : abap.char(20);
category6 : abap.char(20);
category7 : abap.char(20);
category8 : abap.char(20);
m001 : abap.int4;
m002 : abap.int4;
m003 : abap.int4;
m004 : abap.int4;
m005 : abap.int4;
m006 : abap.int4;
m007 : abap.int4;
m008 : abap.int4;
m009 : abap.int4;
m010 : abap.int4;
m011 : abap.int4;
m012 : abap.int4;
m013 : abap.int4;
m014 : abap.int4;
m015 : abap.int4;
m016 : abap.int4;
m017 : abap.int4;
m018 : abap.int4;
m019 : abap.int4;
m020 : abap.int4;
}
The above table allows handling transformation of up to 20 horizontal measures to vertical format. It also allows storing data for multiple applications and categorized measures in up to 8 categories; i.e., hierarchies that could be aggregated in a wrapper CDS view. They could be also expended and collapsed by front end application.
The sample data for our P&L ADOC application was generated by ABAP program and is shown on the following screen:
Note DUMMY column value 1 that will be used in N..M JOIN with application tables.
The ZABL_HVT20 table was wrapped by ZABA_HVT_10ADOC_DDL ABAP CDS View to expose only 14 numeric columns to transform 14 measures in a single record to a single measure in 14 records as shown in the following table:
define view ZABA_HVT_10ADOC_DDL
as select from zabl_hvt20
{
key dummy,
key application,
key sort_by,
category1,
category2,
m001,
m002,
m003,
m004,
m005,
m006,
m007,
m008,
m009,
m010,
m011,
m012,
m013,
m014
}
where
dummy = 1
and application = 'ADOC'
Note that:
◈ since ZABL_HVT20 table may contain records for multiple applications, only records for application ADOC were selected
◈ only category1 and castegory2 used in P&L application were exposed
ABAP CDS Implementation
The following ABAP CDS Models were developed:
to implement conversion from:
Note the naming conventions of CDS Views above. The ZABA prefix indicates package they belong to. The second token in name ADOC indicates application. The third token in CDS View name indicates dependency level; i.e., level 10 are initial CDS Views, 20 is the next level CDS View that calls level 10 view, etc. Multiple levels are needed due to limitation of CDS View syntax; e.g., you cannot define in the same CDS View a calculated measure that uses as a component of the expression another calculated measure. Additionally, having multiple level CDS Views allows to keep logic reasonably simple. The same prefixes and 10, 20, … level numbers allow to keep all related CDS views together on the list of Data Definition components in SAP HANA Studio.
Incrementing level numbers by 10 allows to add in the future if needed some additional levels; e.g., 15, and keep intact the principals of the naming convention.
The final CDS View name; i.e., ZABA_ADOC_PNL_REPORT_DDL; does not 2 number prefix in the third token
The following subsections of this document discuss the above ABAP CDS Views.
ZABA_ADOC_10TBA_DDL ABAP CDS View
This ABAP CDS View selects data from ZABA_ADOC table for CYCM and PYCM Time Buckets and aggregates it as shown in the following table:
define view ZABA_ADOC_10TBA_DDL
with parameters
p_today : char8
as select from ZABL_TBUCKET_DDL( p_today:$parameters.p_today )
association [1..*] to zaba_adoc as _adoc on ZABL_TBUCKET_DDL.cal_date = _adoc.doc_date
{
key cast(TO_DATE as char8) as to_date,
time_bucket_id,
time_bucket_name,
cal_year,
PERIOD,
sum(_adoc.prop_tax) as prop_tax,
sum(_adoc.advertising) as advertising,
sum(_adoc.admin) as admin,
sum(_adoc.payrol) as payrol,
sum(_adoc.utilities) as utilities,
sum(_adoc.insurance) as insurance,
sum(_adoc.sales) as sales,
sum(_adoc.rent) as rent,
sum(_adoc.royalties) as royalties,
sum(_adoc.frenchise) as frenchise
}
where time_bucket_id = 'CYCM' or time_bucket_id = 'PYCM'
group by
TO_DATE,
time_bucket_id,
time_bucket_name,
cal_year,
PERIOD
The above ABAP CDS View produces the following results for P_TODAY = 20171114:
The ZABL_TBUCKET_DDL ABAP CDS View was discussed separately in [1] “Implementing Time Buckets in ABAP CDS View” document.
ZABA_ADOC_20ERP_DDL ABAP CDS View
This ABAP CDS View calculates Total Expenses, Total Revenue, Profit and Profit to Revenue Ratio as shown in the following table:
define view ZABA_ADOC_20ERP_DDL
with parameters
p_today : char8
as select from ZABA_ADOC_10TBA_DDL( p_today:$parameters.p_today )
{
key to_date,
time_bucket_id,
time_bucket_name,
cal_year,
PERIOD,
prop_tax,
advertising,
admin,
payrol,
utilities,
insurance,
sales,
rent,
royalties,
frenchise,
prop_tax+advertising+admin+payrol+utilities+insurance
as expenses,
sales + rent + royalties + frenchise
as revenue,
sales + rent + royalties + frenchise
-prop_tax-advertising-admin-payrol-utilities-insurance
as profit,
cast((sales + rent + royalties + frenchise
-prop_tax-advertising-admin-payrol-utilities-insurance) * 100 as abap.fltp)
/
cast( sales + rent + royalties + frenchise as abap.fltp)
as pr_ratio
}
Note that to calculate PR_RATIO the components of the division must be converted to abap.fltp type.
The above ABAP CDS View produces the following results for P_TODAY = 20171114:
ZABA_ADOC_30PRD_DDL ABAP CDS View
This ABAP CDS View converts PR_RATIO value to abap.dec(8.2) type and adds DUMMY attribute with constant value 1 as shown in the following table:
define view ZABA_ADOC_30PRD_DDL
with parameters
p_today : char8
as select from ZABA_ADOC_20ERP_DDL( p_today:$parameters.p_today )
{
key to_date,
time_bucket_id,
time_bucket_name,
cal_year,
PERIOD,
prop_tax,
advertising,
admin,
payrol,
utilities,
insurance,
sales,
rent,
royalties,
frenchise,
expenses,
revenue,
profit,
fltp_to_dec( pr_ratio as abap.dec(8,2) ) as pr_ratio,
1 as dummy
}
The DUMMY column will be used in N..M JOIN with ZABA_HVT_10ADOC_DDL CDS View.
The above ABAP CDS View produces the following results for P_TODAY = 20171114:
ZABA_ADOC_40HVT_DDL ABAP CDS View
This ABAP CDS View transforms 14 measure columns in a single record from ZABA_ADOC_30PRD_DDL CDS View to 14 records with a single AMOUNT measure for every record from ZABA_ADOC_30PRD_DDL CDS View:
define view ZABA_ADOC_40HVT_DDL
with parameters
p_today : char8
as select from ZABA_ADOC_30PRD_DDL( p_today:$parameters.p_today )
association [*] to ZABA_HVT_10ADOC_DDL as _hvt on ZABA_ADOC_30PRD_DDL.dummy = _hvt.dummy
{
key to_date,
time_bucket_id,
_hvt.sort_by,
_hvt.category1,
_hvt.category2,
prop_tax*_hvt.m001 + advertising*_hvt.m002 + admin*_hvt.m003 + payrol*_hvt.m004 + utilities*_hvt.m005 +
insurance*_hvt.m006 + expenses*_hvt.m007 + sales*_hvt.m008 + rent*_hvt.m009 + royalties*_hvt.m010 +
frenchise*_hvt.m011 + revenue*_hvt.m012 + profit*_hvt.m013 + pr_ratio*_hvt.m014
as amount
} where time_bucket_id = 'CYCM' or time_bucket_id = 'PYCM'
The above ABAP CDS View produces the following results for P_TODAY = 20171114:
ZABA_ADOC_50AMT_DDL ABAP CDS View
This ABAP CDS View splits the AMOUNT measure to 2 measures AMOUNT_CYCM and AMOUNT_PYCM based on value of TIME_BUCKET_ID parameter as shown in the following table:
define view ZABA_ADOC_50AMT_CPYM_DDL
with parameters
p_today : char8
as select from ZABA_ADOC_40HVT_DDL ( p_today:$parameters.p_today )
{
key to_date,
sort_by,
category1,
category2,
amount as amount_cycm,
fltp_to_dec(0.00 as abap.dec(15,2) ) as amount_pycm
}
where
time_bucket_id = 'CYCM'
union all
select from ZABA_ADOC_40HVT_DDL ( p_today:$parameters.p_today )
{
key to_date,
sort_by,
category1,
category2,
fltp_to_dec(0.00 as abap.dec(15,2) ) as amount_cycm,
amount as amount_pycm
}
where
time_bucket_id = 'PYCM'
The above ABAP CDS View produces the following results for P_TODAY = 20171114:
ZABA_ADOC_60AGR_DDL ABAP CDS View
This ABAP CDS View aggregates AMOUNT_CYCM and AMOUNT_PTCM by categories as shown in the following table:
define view ZABA_ADOC_60AGR_CPYM_DDL
with parameters
p_today : char8
as select from ZABA_ADOC_50AMT_CPYM_DDL ( p_today:$parameters.p_today )
{
key to_date,
category1,
category2,
sum(amount_cycm) as amount_cycm,
sum(amount_pycm) as amount_pycm
}
group by
to_date,
category1,
category2
The above ABAP CDS View produces the following results for P_TODAY = 20171114:
ZABA_ADOC_70PER_DDL ABAP CDS View
This ABAP CDS View calculates the PERFORMANCE measure as shown in the following table:
define view ZABA_ADOC_70PER_CPYM_DDL
with parameters
p_today : char8
as select from ZABA_ADOC_60AGR_CPYM_DDL ( p_today:$parameters.p_today )
{
key to_date,
category1,
category2,
amount_cycm,
amount_pycm,
cast((amount_cycm - amount_pycm)*100 as abap.fltp) / cast(amount_pycm as abap.fltp)
as performance
}
Note that the AMOUNT measures must be converted to abap.fltp type to perform the division operation.
The above ABAP CDS View produces the following results for P_TODAY = 20171114:
ZABA_ADOC_80DEC_DDL ABAP CDS View
This ABAP CDS View converts back the PERFORMANCE measure to abap.dec(8,2) type as shown in the following table:
define view ZABA_ADOC_80DEC_CPYM_DDL
with parameters
p_today : char8
as select from ZABA_ADOC_70PER_CPYM_DDL ( p_today:$parameters.p_today )
{
key to_date,
//category1,
category2,
amount_cycm,
amount_pycm,
fltp_to_dec( performance as abap.dec(8,2) ) as performance
}
The above ABAP CDS View produces the following results for P_TODAY = 20171114:
ZABA_ADOC_PNL_REPORT_DDL ABAP CDS View
This is a final ABAP CDS View that hides the PERFORMANCE measure calculation as shown in the following table:
define view ZABA_ADOC_PNL_REPORT_DDL
with parameters
p_today : char8
as select from ZABA_ADOC_80DEC_CPYM_DDL ( p_today:$parameters.p_today )
{
key to_date,
category2,
amount_cycm,
amount_pycm,
performance
}
The above ABAP CDS View produces the following results for P_TODAY = 20171114:
The same ABAP CDS View was also called from the following ABAP program:
REPORT zaba_zaba_adoc_pnl_report_ddl.
PARAMETERS:
p_date TYPE char8 DEFAULT '20171114'.
DATA: go_alv_table TYPE REF TO cl_salv_table.
START-OF-SELECTION.
SELECT * FROM zaba_adoc_pnl_report_ddl( p_today = @p_date )
INTO TABLE @DATA(gt_out).
cl_salv_table=>factory( IMPORTING r_salv_table = go_alv_table CHANGING t_table = gt_out ).
go_alv_table->display( ).
It produces the same result as presented above – however, this time in ALV Grid format:
It could be also called from SAP Gateway Client producing the same results in JSON format as shown on the following screen:
The above results in JSON or XML format could be consumed by SAPUI5 or SAP Fiori HTML5 based application.
No comments:
Post a Comment