Question & Answer
Question
This document explains the numeric calculation behavior of the TM1 database.
Answer
TM1 database stores all numeric values by using the IEEE-754 standard for floating point doubles. Numeric formats can be applied to change the presentation of values but all numeric values are always stored as floating point doubles.
IEEE-754 is an industry standard that is commonly used in financial software applications including Microsoft Excel. More detail about this standard can be found here https://en.wikipedia.org/wiki/IEEE_754. The maximum number of digits that can be reliably stored is 15.
Calculations that involve decimal values can introduce a degree of imprecision. Consider the following simple rule calculation in TM1.
['C'] = N:['A']/['B'];
If the value of A is 5.55 and the value of B is 2.5 then C will evaluate to 2.2199999999999998. The formatted value of C will be 2.22, but the actual value in the cell is 2.2199999999999998.
This calculation behavior is expected based on the floating point double implementation used by TM1.
In this case it may be desirable to round so the actual value of the cell is also 2.22. This can be accomplished using the ROUND function in rules as follows:
['C'] = N:ROUNDP( ['A']/['B'], 2 );
This same imprecision will also occur in calculations in Turbo Integrator processes. The ROUND and ROUNDP functions can be used in both rules and Turbo Integrator processes.
TM1 uses the Round down method of floor(x + .5). Microsoft Excel uses the Round to nearest method. This can result in different values depending on where the calculation is being performed when using the TM1 Perspectives or Planning Analytics for Excel add-in.
Special consideration should be applied when comparing numeric values in TM1. Comparison of values may occur in cube rules, in Turbo Integrator processes, and when using consolidations where the consolidated value is expected to evaluate to 0. Although two formatted values may appear to be the same, it's possible that the actual value being stored by TM1 are not the same.
Zero suppression should also be considered. In many cases calculations and consolidations (with or without negative weight) in TM1 are intended generate zero values. Views with zero suppression can be used to check for scenarios where two numbers do not consolidate for zero. Zero suppression is based on the actual value in the cell, not the formatted value. A small difference between the actual and formatted value may explain why zero suppression does not suppress a value that initially appears to be zero.
The MTQ feature also impacts the consistency of calculated and consolidated values. When MTQ is enabled a single query is split into worker threads. The results of each worker thread are combined to present a single view of data. The number of worker threads, and the order worker threads complete work is not predicable. This will create small variances in calculated and consolidated values each time a view or MDX query is executed by TM1.
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSD29G","label":"IBM Planning Analytics"},"ARM Category":[{"code":"a8m3p000000PC9LAAW","label":"Planning Analytics-\u003EServer Data Tier"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions","Type":"MASTER"}]
Was this topic helpful?
Document Information
Modified date:
18 October 2022
UID
swg21515228