Introduction
The COPA, the forecast and many other S4HANA, ECC and legacy tables contain hundreds of measures in their record structures. This format is not suitable for efficient processing in BI front end tools; e.g., WebI reports.
The WebI P&L reports are performing the best when dealing with only one or few measures at most in a single record. The structure of COPA table does not meet this requirement. There is a need to transpose a record with dozens or hundreds of measures to multiple records with one of few measures only; e.g., current year and previous year values; adding Measure Id column to each record. This could be achieved performing Cartesian Transformation, on records of the original tables.
The Cartesian Transformation could be performed during ETL or ELT processing. It could be also performed dynamically in HANA models.
The above-mentioned documents describe how to transpose dynamically in HANA model a set of N records with M measures to a set of N*M records with a single measure using conversion matrix.
Sometimes, several cumulative measures have to be exposed in resulting calculation view to calculate correctly non-cumulative measure in final HANA model or WebI report.
Usually, Multiple Cartesian Transformations are required to expose multiple numeric columns in resulting calculation view. Each Cartesian Transformation would require a conversion matrix with 0 or 1 flag values.
This blog describes how to handle multiple Cartesian Transformations with a single conversion matrix containing multiple pseudo-binary flags described in [4] – Pseudo-Binary Operations in SAP HANA Views’ Expressions, to expose 2 measures used to calculate non-cumulative measure in final HANA model; e.g., Store Expenses Per Sq Ft by Region = Store Expenses / Store Sq Ft
Sample Sales Report
We have the following SALES_BY_REGION table:
The table contains the following data:
We would like to report the following information:
1. SALES
2. EXPENSES
3. PROFIT = SALES – EXPENSES
4. EMPLOYEES
5. SQFT
6. SALES_EMPL = SALES / EMPLYEES
7. EXP_SQFT = EXPENSES / SQFT
SALES, EXPENSES, EMPLOYEES & SQFT are base measures and could be aggregated in HANA interim models/views.
SALES_EMPL and EXP_SQFT are non-cumulative measures and must be calculated in final HANA model or WebI report from aggregated base measures: SALES, EMPLOYEES, EXPENSES & SQFT. PROFIT; exposed as AMT_A and AMT_B values with corresponding Measure ID flag.
PROFIT is a cumulative measure and could be aggregated same way as base measures or calculated in final HANA model as non-cumulative measures.
The example below shows how to use a single Conversion Matrix with pseudo-binary number flags to transpose Source Model measures to the Target Model with 2 numeric columns containing component cumulative measures of non-cumulative measure expression
The concept of pseudo-binary flag is described in detail in [5] – Handling Pseudo-Binary Flags in HANA View Calculated Attribute/Measure Expression
In short, a pseudo-binary flag is a base 10 integer number that contains only digits 0 and/or 1. Each digit in the pseudo-binary number represents a single flag with value 0 for OFF and 1 for ON. The pseudo-binary integer number may hold up to 9 unique flags. The first right-most digit of the pseudo-binary number is considered to be the first flag.
With simple arithmetic on pseudo-binary number, you can get value of any flag; e.g., to retrieve value of the 3-rd flag the following expression has to be evaluated:
(IVAL MOD 200) >= 100 when the 3-rd flag is ON; e.g., 10001010110 MOD 200 = 110
(IVAL MOD 200) < 100 when 3-rd flag is OFF; e.g., 10001011011 MOD 200 = 11
In the example above the Conversion Matrix contains 2 digit pseudo-binary numbers with the first right-most flag representing nominator conversion values and the second flag containing denominator conversion values.
The AMT-A values are calculated as follows:
if(("M1" % 2) = 1, "SALES", 0) +
if(("M2" % 2) = 1, "EXPENSES", 0) +
if(("M3" % 2) = 1, "EMPLOYEES", 0) +
if(("M4" % 2) = 1, "SQFT", 0)
The AMT-B values are calculated as follows:
if(("M1" % 20) >= 10, "SALES", 0) +
if(("M2" % 20) >= 10, "EXPENSES", 0) +
if(("M3" % 20) >= 10, "EMPLOYEES", 0) +
if(("M4" % 20) >= 10, "SQFT", 0)
Two cumulative measures are exposed in the Target Model. The final values of non-cumulative measures are calculated in WebI report or final HANA model as follows:
if("OPR" = '=',
"AMT-A",
if("OPR" = 'A/B',
"AMT-A"/"AMT-B",
If("OPR" = 'A-B',
"AMT-A"-"AMT-B"
)
)
)
Sample HANA Models/Calculation Views
The CA_20_MULTI_CT_WITH_PB_FLAGS calculation view is shown below:
AMT_A and AMT_B calculated measures are implemented as follows:
The conversion matrix with pseudo-binary flags is shown below:
The model produces correct results before and after aggregation as shown below:
The AMT-A and AMT-B columns represent cumulative measures only. They were derived by 2 Cartesian Transformations implemented using single Conversion Matrix with pseudo-binary flags.
The AMOUNT column shows the final results derived in CASE statement executing expression in OPERATION column; i.e., A, A-B or A/B.
The results after aggregation are also correct including SALES/EMPL and EXPENS/SQFT non-cumulative measure values as shown on the following screen:
The AMT-A and AMT-B columns represent cumulative measures only. They were derived by 2 Cartesian Transformations implemented using single Conversion Matrix with pseudo-binary flags. As these measures are cumulative, they can be aggregated without a problem.
The AMOUNT column shows the final results derived in CASE statement executing expression in OPERATION column; i.e., A, A-B or A/B.
No comments:
Post a Comment