High precision Configuration

ETL开发项目中遇到过一个很奇特的问题，源表的主键是number类型，精度大于15，抽取到目标表的过程中，通过debugger调试器查看会生成科学计数法的格式，workflow运行完成后，主键相差很小的数字结果却变成了很多相同的数字，然后程序报主键重复错误，当时也走了很多弯路，花了好长时间去解决这个问题。

原因：没有启用高精度

解决办法：可以在Task的Properties页签中勾选“Enable high precision”单选框，截图如下：

原因分析：

最权威的资料当属F1帮助文档，在索引处输入“high precision, handling”，结果如下：

High Precision Data Overview

High precision data determines how large numbers are represented with greater accuracy. The precision attributed to a number includes the scale of the number. For example, the value 11.47 has a precision of 4 and a scale of 2. Large numbers can lose accuracy because of rounding when used in a calculation that produces an overflow. Incorrect results may arise because of a failure to truncate the high precision data.

High precision data values have greater accuracy. Enable high precision if you require accurate values.

You enable high precision on the properties tab of the session. The Integration Service processes high precision data differently for bigint and decimal values.

Bigint

In calculations that can produce decimal values, the Integration Service processes bigint values as doubles or decimals. When a session contains a calculation that can produce decimal values and runs without high precision, the Integration Service converts bigint values to doubles before it performs the calculation. The transformation Double datatype supports precision of up to 15 digits, while the Bigint datatype supports precision of up to 19 digits. Therefore, precision loss can occur in calculations that produce bigint values with precision of more than 15 digits.

For example, an expression transformation contains the following calculation:

POWER( BIGINTVAL, EXPVAL )

Before it performs the calculation, the Integration Service converts the inputs to the POWER function to double values. If the BIGINTVAL port contains the bigint value 9223372036854775807, the Integration Service converts this value to 9.22337203685478e+18, losing the last four digits of precision. If the EXPVAL port contains the value 1.0 and the result port is a bigint, this calculation produces a row error since the result, 9223372036854780000, exceeds the maximum bigint value.

When you use a bigint value in a calculation that can produce decimal values and you run the session with high precision, the Integration Service converts the bigint values to decimals. The transformation Decimal datatype supports precision of up to 28 digits. Therefore, precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits. In this case, the Integration Service stores the result as a double.

Decimal

When a session runs without high precision, the Integration Service converts decimal values to doubles. The transformation Decimal datatype supports precision of up to 28 digits, while the Double datatype supports precision of up to 15 digits. Therefore, precision loss occurs if the decimal value has a precision greater than 15 digits.

For example, you have a mapping with Decimal (20,0) that passes the number 40012030304957666903. If the session does not run with high precision, the Integration Service converts the decimal value to double and passes 4.00120303049577 x 1019.

To ensure precision of up to 28 digits, use the Decimal datatype and enable high precision in the session properties. When you run a session with high precision, the Integration Service processes decimal values as Decimal. Precision loss does not occur in a calculation unless the result produces a value with precision greater than 28 digits. In this case, the Integration Service stores the result as a double.

由帮助文档可知：

PowerCenter内部会把长整型处理成double型或decimal型，当精度大于15位的时候，由于内部会用科学计数法和去除在精度范围之外的数字，因此会导致数字失真，所以在精度位于16～28位之间的情况下，为保证数字的完全一致，需要启用高精度选项。

字段数据类型及内部处理类型一览：

字段数据类型

  字段精度

不启用高精度 

  启用高精度

Decimal

0-28

Double

Decimal

Decimal

Over 28

Double

Double

源自：http://blog.csdn.net/allenwhitecollar/article/details/12222273