Skip to Content
Technical Articles
Author's profile photo Adam Baryla, Ph.D.

Handling Non-Cumulative Measures in HANA Calculation Views with Multiple Cartesian Transformation and Single Conversion Matrix

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 dynamic Cartesian Transformation in HANA model was described originally by Tony Cheung and Abani Pattanayak in [1], Jody Hesch in [2] and Raj Kumar Salla in [3].

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.

Conclusion

In case of multiple Cartesian Transformation, it is easier to manage a single conversion matrix with pseudo-binary flags rather than multiple conversion matrices with a single flag each or single conversion matrix with true binary flags.

Single Conversion Matrix with Pseudo-Binary Flags can hold up to 9 flags in each cell as the maximum value of 32 bit integer is just over 2,000,000,000.

Single Conversion Matrix with True Binary Flags can hold up to 32 flags in each cell when using 32 bit integers. However, in real life modelling only few binary flags are needed.

Since conversion matrix contains the pseudo-binary numbers with 0 and 1 digits only of decimal 10 value, it is very easy to interpret by human eye. For example, pseudo-binary value 110 for flag 1 OFF, flag 2 ON and flag 3 ON is much easier to interpret than corresponding binary value 6 consisting of 0 for flag 1, 2 for flag 2 and 4 for flag 3.

References

[1] – Abani Pattanayak, Tony Cheung – Modelling: Column to Row Transpose using Matrix in HANA – Dec 23, 2013
http://scn.sap.com/docs/DOC-50541

[2] – Jody Hesch – How To: Dynamic Transposition in HANA – Nov 11, 2013
http://scn.sap.com/docs/DOC-48833

[3] – Raj Kumar Salla – Table Transpose in SAP HANA Modeling – Jan 2, 2014
http://scn.sap.com/docs/DOC-50719

[4] – Adam Baryla – Pseudo-Binary Operations in SAP HANA Views’ Expressions – Dec 2, 2018
https://blogs.sap.com/2018/12/02/pseudo-binary-operations-in-sap-hana-views-expressions/

 

 

 

Assigned tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.