Avoid precision loss in your SAP BI implementation

[Former Member]

Key concept

Data Type FLTP - Floating point number, accurate to 8 bytes is often required for persistent key figures which are calculated in BI. BI developers often ignore this data type when maintaining a key figure. 

Implementation Scenario

Calculation of unit price persistently is often a requirement in the data model of a BI implementation. The calculated unit price is subsequently looked up downstream from a staging / master data DSO in a transformation and multiplied with the corresponding quantity to populate the Amount key Figure.

During Product testing / UAT it is found that the Amount Key Figure is not accurate although all calculations in BW are correct. Understandably this data inaccuracy is not acceptable to the business and will not pass UAT.

Solution

The above scenario occurs because the Unit Price key figure has been defined as Amount, CURR - Currency field, stored as DEC. which results in precision loss. When the data type of the Unit Price key figure is changed to FLTP - Floating point number, accurate to 8 bytes, the results are accurate. This is true for both BW 3.5 and SAP NetWeaver BI 7.0.

Online SAP documentation for key figure maintenance states - For the amount, quantity, and number, you can choose between the decimal number and the floating point number, which guarantees more accuracy..., but does not elaborate further on actual applications.

Demo

I have elaborated the difference between the two data types through a simple ABAP program example. The technical definition of two key figures to be tested are shown below

1. Unit Price 1 - demo (YR_UPRC1) Data type - Amount, CURR - Currency field, stored as DEC

(Fig 1)

2. Unit Price 2 - demo (YR_UPRC2) Data type - Amount, FLTP - Floating point number, accurate to 8 bytes

(Fig 2)

Fig 1

Fig 2

Example

In our example, say -

Price = £50.95

Quantity = 20

Unit Price = 50.95/20 = £2.5475

Hence amount for 50 units = 2.5475 * 50 = £127.375

The calculation of the unit price is price / quantity and is held by a Unit Price key figure. Below is a simple ABAP program to first calculate the unit price for the two key figures and then use them subsequently to calculate the amounts.

For simplicity, I have hard coded the values for price and quantities.

ABAP Report:

Output of the above program:

As can be seen from the results above, Amount2 (data type - FLTP) gives the correct output (compare with Example above)whereas there is a precision loss in Amount1 (data type - CURR).