1.   Introduction

SAP HANA Views do not support binary/bitwise operations in calculated attributes and measures. In HANA SQL Procedures one can use BIT* functions (not available in HANA Views) instead of binary/bitwise operators to manipulate bits of an integer variable as it is possible in Java, C, C++ or other languages with e.g., & or | operators.

Sometimes, it might be convenient to pack multiple binary flags into a single integer value and perform bitwise ‘&’ operation on integer value and specific flag value to get the value of a flag, e.g.; the value of the 3-rd rightmost bit could be extracted with the following expression:

• decimal notation                       6 & 4 = 4

• is equivalent to binary notation       0110 & 0100 = 0100 

Since HANA Views expressions do not support bitwise operators, you cannot get easily the value of binary flag stored in integer variable.

This blog describes how to handle multiple flags in a single integer value; i.e., table column, HANA model’s calculated attribute or measure.

Even so HANA cannot handle binary flags, with a concept of pseudo-binary flag, up to 9 flags could be packed into an integer value or up to 19 flags into a bigint value.

Packing multiple flags into a single integer value might be very useful when implementing HANA models for complex business cases.

For example, using multiple Cartesian Transformations one could transpose 100 COPA measures in a single record to 2 measures in 50 records with additional ID column representing current year and last year values described by ID column.

2.   Pseudo-Binary Flag

The pseudo-binary number is defined as a base 10 integer number
using only 0 and/or 1 digits;
e.g.: pseudo-binary number 100 is not equal to integer value 4.
It is equal to base 10 integer value 100.

Since the largest 32 bit integer number consists of 10 digits, we could safely pack 9 pseudo-binary flags into an integer value and up to 19 pseudo-binary flags into a bigint value.

With modulo operator; i.e.; %, available in HANA model calculated attribute/measure expression, one can retrieve a value of pseudo-binary flag from a pseudo-binary number; i.e., an integer value that uses only 0 and/or 1 digits.

The following table presents how to use a % operator to get value of pseudo-binary flag:

The green color values show positive test results for sample integer values storing 9 pseudo-binary flags.

The red color values show negative test results for sample integer values storing 9 pseudo-binary flags.

Using the pseudo-binary base 10 integer number 100,110,111 with % pseudo-binary operator is equivalent to using base 10 integer value 311 with & binary operator as 100110111₂ = 311₁₀.

The big advantage of pseudo-binary numbers is that
it is very easy to visualize
which binary flag is set and which one is not.

3.   Sample HANA View

Let us now build a very simplistic attribute view that uses pseudo-binary flags in calculated attributes’ expressions.

Let us define a test table PSEUDO_BINARY_FLAG as shown on the following screen:

Its PBNUM_VALUE column is populated with pseudo-binary flags; i.e., decimal numbers using only digits 0 and/or 1, as shown on the following screen:

The AT_PB_FLAGS attribute view was built on top of the PSEUDO_BINARY_FLAG table as shown on the following screen:

The above attribute view defines 6 calculated attributes that extract values of pseudo-binary flags. The sample expressions that define CA_FLAG03_INT and CA_FLAG03_TXT calculated attributes are shown on the following screens:

The above expressions extract integer value and text value of flag 03; i.e., the 3-rd right-most digit in PBNUM_VALUE column of PSEUDO_BINARY_FLAG table.

The expressions for getting value of flag 02 i.e., the 2-nd right-most digit in PBNUM_VALUE column of PSEUDO_BINARY_FLAG table are:

• CA_FLAG02_INT = PBNUM_VALUE % 20

• CA_FLAG02_TXT = if((PBNUM_VALUE %20) >= 10, TRUE, FALSE)

The expressions for getting value of flag 02 i.e., the 2-nd right-most digit in PBNUM_VALUE column of PSEUDO_BINARY_FLAG table are:

• CA_FLAG01_INT = PBNUM_VALUE % 2

• CA_FLAG01_TXT = if((PBNUM_VALUE % 2) >= 1, TRUE, FALSE)

The data preview of the AT_PB_FLAGS attribute view shows the following data

As you could see CA_*_TXT calculated attributes’ columns display text value TRUE or FALSE depending on value of corresponding pseudo-binary flag.

4.   Summary

The example of using pseudo-binary flags in HANA models/views presented above is very simplistic. It just shows the concept.

Packing multiple flags into a single integer value might be very useful when implementing HANA models for complex business cases.

For example, using multiple Cartesian Transformations one could transpose 100 COPA measures in a single record to 2 measures in 50 records with additional ID column representing current year and last year values described by ID column – the format suitable for implementation of responsive P&L Reporting with BI Tools.

The more useful example will be illustrated in the follow up blog.