Extract from SAP ECC COPA the financial KPIs formulas (defined in trx KE36) to SAP BW. Then calculate P&L KPIs based on the COPA cube
Explain how to build a dataflow extracting the financial KPIs definitions (see transaction KE36 or KE33) of ECC COPA module to SAP BW.
A form in ECC COPA is like a spreadsheet with many key figures that compound the P&L’s KPIs of a company such as Net Sales, Gross Profit, Operating Profit, etc.
With those forms and formulas in SAP BW, then you can calculate the KPIs applying the formulas to the key figures stored in the COPA cube.
The main requirement was to keep the P&L’s KPIs defined in ECC COPA updated in BW.
Furthermore, the organization was multicompany, so they had many forms, one per each company. Each form had different formulas (different ways of calculation) for the same KPI.
This was a research with some deductions I made and implemented successfully in a client.
The solution includes ABAP programming in the expert routine of a transformation. I will explain it briefly with a pseudo code.
Formulas definitions dataflow example: 8 forms, one per company in the source system (ECC)
COPA transactional data dataflow example: 2 different source systems.
The last cube “Consolidated COPA” contains the KPIs calculated with the formulas + COPA transactional data fields.
Step by step:
1. Creation of generic datasources
In SAP ECC we have these 4 tables that holds forms and formulas definitions:
- CEFORMT: Texts of formula’s elements
- CEFORMS: Form’s structure
- CEFORMF: Filters on formula’s elements
- TKESK: Formulas
Based on these tables, we must create 3 generic datasources:
It will cointain the structure of the forms, lines of the forms, each with its description.
First we need to create an Infoset (for example XIS_COPA_LNFORM) joining the tables CEFORMT and CEFORMS
Then create the generic datasource based on this Infoset:
It is based on the table CEFORMF and contains the filters on formula’s elements
The field “Pointer” is the identifier of the form’s element.
This identifier, generally starting with “0” in this extractor, is equivalent to the element in the formula replacing the “0” by “Z”. For example the element “0100700000” could be referred in formulas with “Z100700000”.
It is based on the table TKESK. It cointains formulas for each form:
Each formula can refer to other formulas or elements defined in the extractor XFD_COPA_FLTR (filtered elements).
One formula could take more than one line, as I remark in red. One formula is identified by the Element ID (for example Z100470000)
2. Formula’s dataflow
After creating those 3 generic datasources, we have to create its dataflow storing the data in 3 ODS respectively:
3. Creation of mapping tables in SAP BW
We need to configure some mapping between the ECC COPA and SAP BW components. We need to create 3 tables to set the relationships:
- Table ZRELCOMP_FORM: define the relation between the company code and its respective form in KE36:
2. Table ZRELFORMULAS_BI: define the relation between the KPIs in ECC COPA and the respective key figure in SAP BW.
3. Table ZRELFLDSAP_BI: maps the fields in ECC with infoobjects: for example, a ratio is filtered by some values of the Document Type. In ECC the field’s name is AUART. In SAP BW the infoobject name is 0DOC_TYPE.
4. Create a Consolidation Cube
The Consolidation cube will holds the KPIs calculated based on ECC COPA formulas and the basic fields of the COPA Cube (the transactional data).
5. Transformation and routine
Create an Expert routine in the transformation COPA Cube -> Consolidation Cube.
With ABAP programming parse the formulas stored in the new ODS and the mapping tables and apply the formulas to the key figures stored in COPA Cube.
The routine will simulate a recursive programming to calculate the formulas, because one formula could be used in another formula.
- Sort SOURCE_PACKAGE by Company Code
- Loop at SOURCE_PACKAGE
- if Company Code changed
- Load the 3 ODS in 3 internal tables filtered by the FORM related to the Company Code
- Calculate Filtered ratios and keep them in an internal tables
- Parse formulas using the internal tables obtained in the previous step. Store the results in an internal tables. Pseudo – recursive programming
- Assign the result of the internal table obtained in the previous step to the target Key figures of the structure <result_fields>
- Append the structure <result_fields> to RESULT_PACKAGE.
- if Company Code changed
The above mentioned solution keeps the ECC COPA financial KPIs updated in SAP BW. Also could be used to integrate multicompany P&Ls.