The issue
The current split and splat functionality in Board EPM offers valuable flexibility for data entry at various aggregation levels. However, it can introduce significant errors and instability when applied to aggregated values that are near zero, particularly when negative values are present in the underlying cube.
Specifically, when the aggregated value used for the split & splat calculation approaches zero (due to offsetting positive and negative values or floating-point rounding errors), the allocation process results in extremely large, often inaccurate, values at the cell level. This occurs because the input value is divided by a near-zero value. The resulting distortion can lead to:
- Extremely large numbers : The cell values after split and splat may be absurdly large (e.g., billions of billions)
- Data Integrity Issues: The total after split & splat no longer matches the user's input.
Example:
Consider a cube dimensioned by an entity with four members, where the first three cells have values of 0.1 and the last has a value of -0.3. The aggregate total is close to zero due to floating-point precision. Attempting to enter a value of 100 at the total level results in absurdly high cell-level values, and the final total will not even be equal to 100.
The two dataview below show the values before and after inputting the value 100 on the total row. A large number of digits are displayed in order to show the rounding error on the total row.
Proposed Solution:
Introduce an option within the layout configuration to disable split & splat functionality when the absolute value of the target aggregate falls below a developer-defined threshold.
- Threshold Definition: The Board developer would specify a threshold value (e.g., 0.1).
- Conditional Disablement: If the absolute value of the aggregated cell targeted for split/splat is less than the threshold, the split/splat function would be disabled for that cell, effectively behaving as if the cell is locked.
- Cell-Level Exemption: This restriction would not apply to direct data entry at the cell level. Users would still be able to directly enter values into individual cells, even if those cells contain zero or near-zero values.
