How the block formation of FOX formulas in SAP planning works

FOX formulas are a good solution in SAP planning if you are looking for a high-performance way to process data with SAP Business Planning and Consolidation or BW IP. It is important to understand the functionality of FOX formulas with regard to block formation. This is helpful for debugging as well as essential for increasing the performance of your planning solution. In this article I explain in detail how the blocks are created in FOX formulas.

Block formation in BPC and SAP Integrated Planning

A FOX formula is always executed several times in a row. First, the data selected by the filter is divided into blocks. The planning function is then executed for each block.

Characteristics that were not selected as characteristics to be changed form a block. Therefore these characteristics are also called block characteristics.

Blocks are formed internally by the system as they facilitate the conversion of FOX formulas into ABAP code. A loop is created for each block characteristic.

Block characteristics in BPC and BW IP

All data records in a block have the same characteristic values for the block characteristic. Let's look at an example to illustrate this. The displayed data sets represent the initial data:

Assuming that you select the version as the characteristic to be changed, the Company Code characteristic serves as a block characteristic. Two blocks are formed, one block per characteristic value. The FOX formula is thus executed twice.

The following blocks are created by the system. The characteristic value of the company code is identical for each block. Note, however, that the sort order of the blocks can be random.

Block 1:

Block 2:

However, if the Company Code characteristic is selected as the field to be changed, the version functions as a block characteristic. In this case, the following blocks are created.

Block 1:

Block 2:

If you do not select any characteristics to be changed, all characteristics serve as block characteristics. In this case, one call is made per data record. Four blocks are formed.

Block 1:

Block 2:

Block 3:

Block 4:

If, on the other hand, you select all characteristics as characteristics to be changed, only one block will be created, since no block characteristics exist. Only one call is made for all data records:

In summary, the following overview results:

Planning Tools compared - SAP BW IP vs. BPC vs. SAC

Performance

Once you know how the block formation works, the effect on performance also becomes clear. For example, you should restrict the values of the block characteristics in the filter as much as possible so that not too many blocks are created.

Using a HANA database brings strong performance advantages. By default, the FOX formulas are executed in the HANA database. However, you should consider some restrictions. The following commands are not supported in the HANA and therefore the FOX formula is executed on the application server, so you cannot benefit from code push down:

• CALL FUNCTION (1)
• ABAP BREAK-POINT command is ignored
• FOX key words are not supported for other usage like variable names. E.g. DATA I TYPE I is not allowed but DATA J TYPE I is allowed.
• attribute lookup (ATRV/ATRVT) or validity check (MDEXIST) on standard time characteristics like 0CALYEAR or characteristic referencing those
• attribute lookup (ATRV/ATRVT) or validity check (MDEXIST) on characteristics which don't have a master data table, which is a BW object. But HANA View based info objects are supported ATRV in FOX on HANA view solved with note 2644984 and Revision 2.00.030.01
• Using a hierarchy node variable in VARC or similar statements
• Using compounding in variables before revision 122.05. It work from revision 122.05 onwards when applying note 2386434.
• Using InfoObjects outside the aggregation level (e.g. in DATA statements)
• If the underlying aggregation level contains a float key figure you need at least note 2644984 and for HANA 1.0 revision 1.00.122.17 or for HANA 2.0 2.00.030.01. See also notes 2449817 and 460652 and the general advice to avoid float in BW. 
• New FOX features introduced with BW 7.4 SP8 like reading from external aggregation levels which is supported for PAK with revision 102.4 incl. note 2234156 and 2241643.  Internal tables are also supported in PAK with HANA revision 97.02 and note 2145611
• The new FOX features ‘modularization’ or ‘forms’ introduced with BW 7.5 SP0 are currently not yet supported in PAK and lead to the processing in ABAP application server.  
• The new FOX feature ‘directly reading from a non-input enabled DSO‘ (introduced in BW 7.5 SP0) is  supported in PAK with SAP BW 7.52. In case the DSO contains non-key fields the the execution however is still on the ABAP stack.
• The new feature of transient attributes (navigation attribute of a navigation attribute) coming with BW 7.4 SP4 will not be supported to run in HANA for time dependent attributes in FOX (e.g. ATRV). Then the FOX is processed on the ABAP stack.
• Key fields in internal tables of type KEYFIGURE_NAME.
• Access to external InfoProviders with variables of type KEYFIGURE_NAME.

FOX formulas - Our Conclusion

As you have noticed, a rather inconspicuous topic like block formation is essential to ensure good performance in data processing using FOX formulas.

Depending on your scenario you have to decide how many blocks you want to be created. It is very important to set the filters of the block characteristics as precisely as possible in order to reduce the number of data records to be processed, but also the number of blocks to be created to a minimum.

Furthermore, this knowledge helps you to better understand the debugging of FOX formulas.

Do you need support with your planning solutions? Please do not hesitate to contact us.