KPI Calculations (by Version) for Balance Sheet and Balance Sheet (Segment) Cubes

Return to Financial Consolidation Model Overview.

The KPI calculation defines calculations for certain elements of a dimension. The calculation is defined by an expression stored in an attribute of the calculated element. The KPI calculation for the Balance Sheet cube is defined in the BS Account dimension and for the Balance Sheet (Segment) cube in the BS Account (Segment) dimension.

The KPI calculation can be different for actual data and planning data. Therefore, the Version elements are grouped into calculation schemas. The standard configuration defines the following two calculation schemas:

Calculation Schema

Definition

A

Calculation in the Actual version.

P

Calculation in planning versions Mid-Term Planning, Budget, Forecast 3+9, Forecast 6+6, Forecast 9+3, etc.

This assignment is defined by the Calculation Schema attribute of the Version dimension.

If the Calculation Schema attribute is empty (like for Previous Year in the above example), no calculation applies to this version.

The calculations for elements in the BS Account dimension are defined in the attributes PCalculation (for versions assigned to the P calculation schema) and ACalculation (for versions assigned to the A calculation schema). More generally speaking, the calculation for a calculation schema is defined in the attribute starting with the first letter of the calculation schema followed by …Calculation.

The calculation can be defined as an expression in the Jedox rule syntax. This expression must not contain the target nor the equal sign. It may contain an optional cell-type restriction (B: or C:) followed by any valid rule expression.

Example:

For each element of the BS Account (or BS Account (Segment)) dimension in combination with the calculation schema having a calculation, a rule instance will be created. This is managed by a rule template.

The creation of rule instances can be restricted by additional dependencies. The dependencies for elements of the BS Account (or BS Account (Segment)) dimension are defined in the attribute PCalculationDependencies (for versions assigned to the P calculation schema) and ACalculationDependencies (for versions assigned to the A calculation schema). More generally speaking, the calculation for a calculation schema is defined in the attribute starting with the first letter of the calculation schema followed by …CalculationDependencies.

If the calculation dependency is the text Cube: followed by a cube name, the calculation will only be valid if the referred cube exists in the database. Otherwise, the rule instance will not be created by the rule template. If the calculation dependency is empty or the attribute is missing, the calculation is always valid.

The target area of the calculation is the element from the BS Account (or BS Account (Segment)) dimension, the list of versions for the calculation schema and the Input measure of the Balance Sheet_measure dimension.

The target area of the calculation can be restricted further individually for each account. The target restrictions for elements of the BS Account (or BS Account (Segment)) dimension are defined in the attribute PTargetRestriction (for versions assigned to the P calculation schema) and ATargetRestriction (for versions assigned to the A calculation schema). More generally, the target restriction for a calculation schema is defined in the attribute starting with the first letter of the calculation schema followed by …TargetRestriction. This attribute can be empty (indication of no further restriction) or a single transaction type (e.g., Transaction Type':'T299' or a list of transaction types (e.g., 'Transaction Type':{'T111','T112'}). Other target restrictions conflict with the Adjustments to the Consolidation Method step of the consolidation procedure and are thus not supported.

Note: after changing the calculation schemas or the calculations, the rule templates handling the KPI calculations in the fact cubes need to be updated.

Updated July 8, 2024