Formula members can be used in the amount and the basis but not in the target. However, because aggregate storage databases do not support transaction semantics, you might experience data inconsistency issues when using formula members in the amount or the basis.
In the following example, User 1 posts revenue values for a set of departments and User 2 performs an allocation of bonus money, for which the year-to-date revenue for each department is basis of the allocation. The order in which these operations are performed affects the result:
Scenario 1: User 1 posts revenue before User 2 runs the allocation.
The allocation results are based on the updated revenue values.
Scenario 2: User 2 runs the allocation before User 1 posts revenue.
The allocation results are based on prior revenue values, not on the updated revenue values.
Scenario 3: User 1 posts revenue and User 2 runs the allocation concurrently.
The allocation results are based on the updated or prior revenue values, depending on which user operation started first.
Oracle does not recommend running these operations concurrently when using formula members in the amount or the basis.
Also, assume that an MDX formula is used to calculate the year-to-date revenue for the allocation. The complexity of the formula can affect the result:
Scenario 4: The year-to-date revenue formula involves members from one dimension and uses only the following arithmetic expressions: +, -, /, and *.
The allocation results are based entirely on either the updated or prior revenue values.
Oracle recommends using simple MDX formulas, as described in scenario 4.
Scenario 5: The year-to-date revenue formula is more complicated than the formula in scenario 4.
It is possible that some of the allocation results are based on the updated revenue values and some are based on the prior revenue values.