Avoid precision loss in your SAP BI implementation

25 May 2010

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 below.

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)

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 below.

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 - FLTP - Floating point number, accurate to 8 bytes (fig 2)

Figure 1

 sandeep-again

Figure 2

 sandeep2

Example1

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 data types and then use them subsequently to calculate the amounts. For simplicity, I have hard coded the values for price and quantities.

ABAP Report

 sandeep3

Output of the above program

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

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

Figure 1

 sandeep-again

Figure 2

 sandeep2

Example1

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 data types and then use them subsequently to calculate the amounts. For simplicity, I have hard coded the values for price and quantities.

ABAP Report

 sandeep3

Output of the above program

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

View comments

Comments

Blog post currently doesn't have any comments.

About the author

Bluefin and SAP S/4HANA - welcome to the one horse race

We use cookies to provide you with the best browsing experience. By continuing to use this site you agree to our use of cookies.