Decimal data type values and Numeric data type values in OLAP cubes are rounded off (825713)

SYMPTOMS

When you create an OLAP cube that has a measure of the Decimal data type or the Numeric data type for the fact data, the value of the measure is rounded off to a maximum of 15 digits. The measure may be rounded off when the Decimal value or the Numeric value in the measure is greater than the scale of 15 digits.

CAUSE

This problem occurs because Microsoft SQL Server 2000 Analysis Services internally converts values of the Decimal data type and the Numeric data type to the Double data type. The precision of the Double data type is restricted to a maximum of 15 digits. Therefore, values of the Decimal data type and values of the Numeric data type are rounded off to 15 digits if the values are greater than 15 digits.

WORKAROUND

To work around this problem, use one of the following methods:

In the OLAP cube, change the Decimal data type or the Numeric data type to the Currency data type. The Currency data type is more precise than the Double data type, and it can store values that have more than 15 digits. The Currency data type can store data in the range of -922,337,203,685,477.5808 to +922,337,203,685,477.5807.
Store the digits that are before the decimal and the digits that are after the decimal in two different measures or in two calculated members. For example, if the fact data in a measure (Measure1) of the Decimal data type (18,11) must store a value of 1234567.12345678901, you can create two measures (Measure1A) and (Measure1B). You can use Measure1A to store the value of the digits that are before the decimal (1234567.0000). You can use Measure1B to store the value of the digits that are after the decimal (0.12345678901).

Note To obtain the value of Measure1, you must combine the values of Measure1A and Measure1B.