HOW TO: Resolve Decimal Precision Loss in Informatica Cloud (IICS)?

Introduction

Precision loss can occur with fields of Decimal and Double data types in Informatica when the field value is with a precision greater than the maximum limit.

If high precision property is not enabled, Data Integration converts decimal values to double which results in precision loss if the decimal value has a precision greater than 15 digits. This results in populating incorrect or a value different from the value coming from the source in target.

First let us understand the problem with an example how the precision loss occurs and how it can be handled in Informatica Cloud. Later we will discuss how informatica is handling decimal and double values which results in precision loss when high precision is disabled.

Problem

Consider a snowflake source which has a decimal field with precision as (38,0)

Consider the values for the field in the table as below

Input
------------------------------------
123456789012345
1234567890123456
12345678901234567
123456789012345678
1234567890123456789
40012030304957666903

When the data is loaded through Informatica into a target with same datatype and precision, the output will be as below.

We can observe there is a precision loss in rows with precision > 16

Output
---------------------------------------
123456789012345
1234567890123456
12345678901234568
123456789012345680
1234567890123456770
40012030304957670000

Solution

To resolve the issue, follow below steps.

1. In the Source Transformation, navigate to Fields>Options>Edit Metadata, change the Native Precision of the decimal field from 38 to 28.
2. In the target transformation, repeat same steps as above and change the target decimal field precision from 38 to 28.
3. In the Mapping Task, navigate to Schedule tab>Advanced Session Properties>Add>Performance Settings> Enable High Precision. Select the Session Property Value as Yes.

To summarize, Enable High Precision and change the precision of fields to 28 if they are above 28. There is no need to change the actual precision of fields in source and target database. Changing them at Informatica mapping level would be sufficient.

For the row values with anything above 28 digits precision loss is expected even after Enabling High Precision.

In case you need precision beyond 28 digits, convert the data types to string to store the data and convert them back to decimal while performing calculations on them.

Explanation

As discussed, precision loss occurs as Informatica converts decimal fields to double after a certain limit of digits. Precision loss does not occur in a calculation if the calculated result is with in the limit.

The following table lists how Data Integration handles decimal values based on how the Enable High Precision advanced session property disabled vs enabled.

When you enable high precision, Data Integration converts numeric constants in any expression function to decimal. If you do not enable high precision, Data Integration converts numeric constants to double.

Why Informatica converts Decimal to Double?

The Decimal and Double types are different in the way that they store the values. Precision is the main difference where double is a 64-bit floating-point data type and decimal is a 128-bit floating point data type.

If you are working with larger range of numbers like currency in your calculations and precision is mandatory, it is required to use Decimal to maintain accuracy and precision of the values.

Decimal can 100% accurately represent any number within the precision of the decimal format, whereas Double, cannot accurately represent all numbers, even numbers that are within their respective formats’ precision. But in performance wise Decimals are slower than double data types and occupy double the space.

If you don’t enable the High Precision, Informatica defaults to double after 15 digits to increase performance. If you need to maintain the accuracy beyond 15 digits, enable high precision property.

How are the Double values Rounded off?

You can also observe the precision loss in Excel beyond 15 digits. Let us understand how these values are rounded off. (Copy paste the values from table below into Excel and see yourself how they are represented)

We must first understand floating point. The idea of this is that basically you have one integer (the mantissa) which gives some scaled representation of the number, and another (the exponent) which says what the scale is, in terms of “where does the dot go”. For instance, 34.5 could be represented in “decimal floating point” as mantissa 3.45 with an exponent of 1, whereas 3450 would have the same mantissa but an exponent of 3.

34.5 = 3.45x10^1
3450 = 3.45x10^3

The excel also rounds the values using the floating point as below. The precision after decimal point is retained until 14 digits.

123456789012345	     = 1.23456789012345 x 10^14
1234567890123456     = 1.23456789012345 x 10^15
12345678901234567    = 1.23456789012345 x 10^16
123456789012345678   = 1.23456789012345 x 10^17
1234567890123456789  = 1.23456789012345 x 10^18
40012030304957666903 = 4.00120303049576 x 10^19

Informatica also rounds the values in similar method but also generally implements symmetric arithmetic method which rounds the last number to next higher digit when the last digit is 5 or greater. I just took excel as an example as it is easy to explain and you can try yourself practically to understand better.

If you are with me until here, I appreciate it and hope you have learned something new and interesting how Informatica handles Decimal values.