Document TitleThe Limits of Data Precision in Essbase (Doc ID 1311188.1)

Applies to:
Hyperion Essbase – Version: 6.0.0.0.00 to 11.1.2.1.000 – Release: 6.0 to 11.1
Information in this document applies to any platform.

Abstract
This article explains how Essbase stores data values and considers how this affects the accuracy of its results. For example, values in Essbase have a maximum precision of 15 significant digits; however, aggregate values may have less accuracy in some cases. Furthermore, some input values cannot be represented exactly, and they may have very slight differences in the results of a query. Recommendations are given for managing the effects of these precision limitations.


Document History
Author:Eric Watkins
Create Date 15-MAR-2011
Update Date 15-MAR-2011

The Limits of Data Precision in Essbase

How Essbase stores cell values

Essbase represents cell values as IEEE double values. Doubles have a maximum of 15 significant digits of precision. Anything beyond the 15th digit is imprecise.

Note that “digits after the decimal” is not the same as “significant digits”. Significant digits refers to the count of digits both before and after the decimal place. Therefore, the number 497.1 has four significant digits. Furthermore, when stored as a double, the fact that it has three digits before the decimal means that it is only accurate up to the 12th digit after the decimal (which is the 15th significant digit).

Some decimal values are not perfectly representable in binary. In effect, when you load a value into Essbase and then query for it, it may be very slightly different from what you expect. For example, consider the value -132.35. When you load it into Essbase and then query for it using full precision, it displays as -132.3499999… However, when you round to only 12 decimals, you get the expected answer of -132.3500000…

Another thing to remember is that the number -132.349999999999 is accurate to at least 15 significant digits. Just because there is a “9” in the sixth significant digit does not mean it is only accurate to six significant digits. The 9s are a result of being off by minus one in the 15th significant digit. If we had been off by plus one, the result would have been -132.350000000001.

Note that differences in hardware and compilers lead to a slight variance in results across different platforms.

Precision of aggregate values

When Essbase aggregates values, the precision of the result depends on the input values. Here is an example:

Parent1
|_ Child1 = 497.00 <- accurate to 15 significant digits, or 12 decimal places
|_ Child2 = -290.00
|_ Child3 = -200.00
|_ Child4 = #MISSING

You may expect Parent1 to have a value of 7.0 that is accurate to 15 significant digits; however, you may actually see this:

Parent1 = 7.0000000000036
^ the 12th decimal place or 13th significant digit

In general, the precision of the parent value depends on to which decimal place the child values are accurate. If, instead, Child3 has a value of -100.00, then the aggregated parent value is as follows:

Parent1 = 107.0000000000036
^ the 12th decimal place or 15th significant digit

Because some of the values are negative and cancel each other out, the aggregated parent value may have fewer significant digits than the children.

As shown by the above example, aggregate values in Essbase are only accurate up to the decimal place which is the 15th significant digit of the child with the largest absolute value. The largest child value in the example above was 497.00; because it has 3 digits before the decimal, it is only accurate up to the 12th decimal place. The same is true for Parent1.

Precision of aggregate values in aggregate storage (ASO) databases

If you load values into Essbase and they are not perfectly representable in binary, you may think that aggregating thousands of such values may add up to having a large effect on the result. However, for aggregate storage databases, Essbase uses a well-known algorithm called Kahan’s Summation Algorithm to guarantee the margin of error stays constant instead of growing linearly with each new summation.

Recommendation for dealing with aggregate data precision issues

1. Set the rounding appropriately in your client so that the precision differences are not visible. For Essbase Report Writer, for example, see the configuration setting NUMERICPRECISION.

2. Make sure that formulas in the outline and calculation scripts do not have conditional statements that explicitly compare a cell value to another value. Instead, you should round the values to, for example, 10 decimal places before comparison.

Advertisements
This entry was posted in Oracle Essbase and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s