SYMPTOM
In Strategy, metrics with a precision of greater than or equal to 15 decimal places displays a floating point rounding error. A floating point rounding error is where values differ from the expected mathematical result after 14 decimal places.
For example in Tutorial a report built with revenue and a metric defined as (Revenue - Lag(Revenue,1,0)) will show the below values seen below in both Developer and Web.
Here the derived metric can be seen to have odd components to their values at the 15 digit mark. For example with the rows for Northwest Pop and Rock the calculation should be 34784.6 - 34069 to give a result of 715.6 but instead 715.599999999991 is seen. This can even be seen in the base Revenue metric for Southwest Alternative as nothing beyond 2 decimal places is stored in the Revenue field of the Tutorial warehouse.
CAUSE
This is an expected behavior in Strategy. Both Strategy and many ODBC drivers the program utilizes store data internally as floating point numbers (to be specific double precision floating point numbers). The way floating point numbers are stored on computers comes with an inherent rounding as the bit precision of floating point numbers does not line up with the set of real numbers. The reason is that any decimal must be represented by a division with how the floating point number storage system works. The problem is that division in base 2 does not match the behavior for division in base 10.
This means what appears to be short decimal numbers are not actually short in binary but often infinitely repeating. This is best explained through a decimal example with one third; when representing 1/3 in decimal it gives the number .333333 repeating. No finite amount of decimal digits can perfectly represent the value 1/3. The same thing occurs with floating point numbers which have a finite number of bits so have to cutoff any number that has repeating binary values. A number will have repeating binary if its decimal component is not divisible by 2 (for example .13).
The effects of such rounding are initially small, but when the values are used in additional calculations, the perceived error can grow. Functions that return an integer, such as Ceiling, Floor, Int, Round, and Trunc, may produce values that are one away from what would be returned if an exact input was used. Refer to the link below for an in depth explanation and analysis of floating point operations, types of error propagation that can occur, and other limitations.
http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems
WORKAROUND
The floating point system has several rounding systems added to it to try and mitigate the problem with the lack of precision in floating point numbers. For straight single values this can handle most scenarios (but not all hence why the base metric had one row with a rounding issue in the Tutorial example). However as calculations become more complex and more different programs have to do calculations and transfer the number these rounding systems can break down. This does leave a workaround through adding in an explicit round function to the desired number of decimal places to occur after other calculations.
In the example above if a wrapper function of Round2(new metric,4) is added this will round the value after it is calculated to 4 decimal places which is then passed into the floating point rounding systems as a single value. With the example above with this added all metric values for Round2 now show the correct values. This does depend on having a known guaranteed limit to the desired precision which is under the 15 digit limit for double precision numbers.
KB438662
