Show TOC

Inverse Formulas at RuntimeLocate this document in the navigation structure

Use

This chapter provides information about runtime aspects of inverse formulas. We assume that you can create input-ready and inverse formulas in Query Designer (see Input-Ready Query, Examples: Inverse Formula as well as Defining an Inverse Formula).

Note

Beispiele, die die Laufzeitaspekte inverser Formeln verdeutlichen, finden Sie unter Beispiele: Inverse Formel zur Laufzeit sowie im SAP-Hinweis 1236347.

You can use input-ready formulas in SAP BusinessObjects Analysis, Edition for Microsoft Office (version 1.1) and in BEx Analyzer and BEx Web Analyzer.

Caution

Note that rows can only be fixed in BEx Web Analyzer.

Input-Readiness of Formulas

Formulas inherit their input-readiness from their operands. If you have defined an input-ready formula F = F(op1, …, opn) in Query Designer, the system checks whether at least one operand is input ready at runtime. If this is not the case, formula F is not input ready either. If an operand opi = G is also an input-ready formula, the same rule applies.

As with input-ready structural elements, the input-readiness of formulas requires that specific characteristics are uniquely defined: If an input-ready formula or an operand of this type of formula uses exception aggregation, all the reference characteristics must be uniquely defined for exception aggregation on cell level, in order to make the formula input-ready. This also applies for values of master data attributes in input-ready formulas (formula variables of type Replacement Path, whose variable has been replaced by the attribute value of a characteristic).

Sequence of Calculations

Calculating inverse formulas can be seen as a kind of "inverted" reporting. As BI Integrated Planning uses input-ready queries for manual planning, the system calculates the reporting formulas for the result set that is sent to the front end. In ABAP Dynpro terminology, reporting formulas are therefore calculated in PBO (process before output). After data has been changed manually in input-ready cells in the query, a server roundtrip is triggered, known as a PAI (process after input). Inverse formulas are calculated at this point. Changes in input-ready formulas are returned to the basic key figures, while observing any disaggregation settings that have been made. This creates new delta records that are stored in the delta buffer. In the next, the OLAP processor reads the delta records in order to refresh its internal status and the result set. The reporting formulas are calculated again during this process.

For the inverse formulas, this means that there must be inversion of these input-ready formulas in the mathematical sense. Otherwise, a change in an input-ready formula would be overwritten in the next PBO after a completed PAI-PBO cycle.

The next section describes the general rules in the implemented computational model.

General Rules

The following section explains the general rules for the implemented initial calculation model and the symmetrical calculation model, which can be selected as an enhanced calculation mode in BEx Query Designer (see Input-Ready Queries andDefining Inverse Formulas).

Depending on which calculation mode is selected in BEx Query Designer, the system triggers calculation of inverse formulas either when input-ready formulas are changed/fixed, and other operands of input-ready formulas have been changed (initial - or asymmetrical calculation -mode), or if an element in a formula group has been manually changed or fixed (symmetrical calculation mode).

Let F = F(op1,..., opn) be an input-ready formula, and op1,..., opk with k < n be input-ready operands. In Query Designer, k inverse formulas have been created in order to calculate operands op1,..., opn:

Fi = Fi(F, op1,…, opi-1, opi+1, … , opk, … , opn) for i = 1,…, k

If F has been changed, for example, the system needs to find the 'best'inverse formula Fi for its calculation. The result of Fi is passed onto opi. The new value of opi can then trigger other calculations or a disaggregation.

The system uses the following rules for formula inversion.

  1. Formula inversions are made based on data records. The values of the characteristics in the drilldown determine the data record here. Inverse formulas are calculated for each data record for the structural components defined in Query Designer. These calculations are not affected by other data records.

  2. If there are nested formula groups, input-ready formulas can be calculated back to input-ready basic or restricted key figures by inversion of other input-ready formulas. These changes trigger a disaggregation, provided that key figures use one of the disaggregation settings.

  3. In a server roundtrip, just one inverse formula is calculated per formula group. After a calculation, all elements of this formula group are temporarily fixed, meaning that the elements of a formula group that has already been calculated cannot be overwritten by calculations from other formula groups during this roundtrip.

  4. To find the inverse formula that needs to be calculated, the system first gathers the elements that trigger calculations. These are changed or fixed cell values of input-ready formulas or elements in a formula group (for each data record). The calculation is triggered by different elements, depending on the calculation mode selected (asymmetrical or symmetrical). If an operand in the formula group was calculated in a previous step, this operand is also treated as fixed for the new calculation, meaning that it is considered a known source. Possible targets for calculation are input-ready operands in the current formula group, which are not changed, fixed or calculated. If one of the inverse formulas cannot clearly be recognized as requiring calculation, the system takes the inverse formula with the highest formula priority defined in BEx Query Designer.

  5. Multiple formula groups can participate in the calculations triggered by manual changes or fixed cell values. As the formula priority definition relates to all elements in a particular formula group, it is not possible to tell which formula group is to be calculated first. The system calculates the formula groups in ascending order following the number of "degrees of freedom". The current degree of freedom is worked out by taking the number of operands and subtracting the number of operands already recognized. These are operands that are constants, fixed, manually changed or were calculated in a previous step. If the current degree of freedom of the formula group is the same, the system checks whether the symmetrical calculation mode is used. If this is the case, the system analyzes the Note for Calculation to clarify the formula priority. In all other cases, the order in which the formula groups are processed remains undefined.

  6. With regard to formula inversion, new rows are treated like existing ones. If the row already exists, changed or recalculated vales are aggregated by basic key figures.

Note

The system does not try to find any old solution for the computational problem resulting from manual changes and other types of Customizing being performed on input-ready queries. The system finds a solution using the rules listed above. If this is not successful, the system informs you with an error message.

Error Handling

If the system does not find any inverse formulas for calculation, it means that the entries made by the user are invalid. The system generates messages providing information about the cause of the problem, such as too many manual changes or too many restrictions from fixed values.

There are cases where all formula inversions are possible, but the subsequent disaggregation of the values is not. This can be the case if all individual values in a subtotal, total or a hierarchy node and the subtotal, total or hierarchy node itself have been changed or calculated. The system generates messages accordingly.

Rounding Rules

Key figure values are saved in the database with a limited number of decimal places. When formulas are calculated, rounding normally takes place. Inverse formulas can be used to calculate key figures that are stored on the database. For key figures like this, every calculated key figure value or key figure value created by disaggregation is therefore rounded to the database decimals. If the system calculates aggregated values using inverse formulas and disaggregates the calculated values, serious rounding errors can occur. It is therefore important to realize that a manually changed value can deviate from the value entered after the formula inversion has been calculated and the result set updated. This also applies for fixed values.

Recommendation

We therefore recommend using the same number of database decimal places for all operands of inverse formulas.

Recommendations

We recommend using nested input-ready formulas. As far as possible, model input-ready formulas and formula inversions in such a way that there are not too many variants for possible calculations. The calculations made at runtime are then easier to understand.

To avoid formal division errors like divisions by zero for quotients as input-ready formulas, such as Average Price, use operator NDIV0. If no data has been planned yet for example, and the Master Data for Characteristic Relationships setting is used for a number of characteristics under Access Type for Result Values, a large number of empty data cells normally result. If you do not use data function NDIV0 here, the cells will not be input ready for Average Price. If you do use NDIV0, however, you will get the calculation NDIV0( 0 / 0 ) = 0 for these types of cells, and Average Price can be changed.