Overriding C: level elements with rules

You should avoid writing a rule that overrides a consolidated value that is a component of another consolidation.

A simple example illustrates this issue. Suppose you have a two-dimensional cube named Sales that is composed of the dimensions Product and Month, with product (Total) and quarterly (1 Quarter) consolidations defined.

Two consolidations, Total and 1 Quarter, in a two-dimensional Sales cube

To calculate the grand total (Total, 1 Quarter), TM1® can consolidate the product totals for each month or consolidate quarterly totals for each product.

Grand total calculation by consolidating quarterly or product totals for each product or month, respectively

Suppose further, you write a rule that calculates a value for Total product sales in Jan, and that the rules-calculated value does not sum the individual product values for Jan. A rule that defines the value of Total products in Jan as 999 serves as an illustration.

['Jan','Total']=999;

If the grand total is calculated by consolidating the product totals for each month, the value will differ from the consolidation of the quarterly totals for each product. This is because the rules-calculated value for total product sales in Jan overrides the natural consolidation defined in the Product dimension.

Actual grand total calculation with value of Jan total calculated by the rules statement

You have no control over the order in which TM1 performs dimension consolidations. Furthermore, depending on which consolidation path is optimal at any given moment, TM1 may alternate between paths. Consequently, you may request the Total, 1 Quarter value twice in the same session and get different results.

You can remedy this situation by writing a rules statement that calculates the value of the Total, 1 Quarter consolidation as the sum of its immediate children along the Month dimension, thereby overriding the Product dimension consolidation. The statement 

['Total']=ConsolidateChildren('Month')

performs this calculation.

Image that illustrates the calculation.

However, there remains an implicit inconsistency when viewing the cube: the sum of the quarterly totals for each product is different from the sum of product total for each month. Thus, overriding C: level values that are components of other consolidations is not recommended.