Skip to Content
Author's profile photo Former Member

Avoid precision loss in your SAP BI implementation

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

image

Fig 2

image

 

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:

image

 

Output of the above program:

image

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

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      Hi.

      Good blog.
      But you should keep in mind some issues when using FLTP data type:
      1. Performance - using FLTP decrease performance because of high precision OLAP calculations.
      2. You can't use zero illimination in queries when using FLTP. Lets say you delete (zeroing) any records, then those records are not 0, but have some leftovers such as epsilon.
      3. You can increase precision without using FLTP by setting "Key Figure with Maximum Precision" in KF properties.

      Regards.

      Author's profile photo Former Member
      Former Member
      Hi,

      Another solution which we implemented was

      1. Multiply the data source field by a proper amount. i.e. 10000. So your PSA will store the amount 10000 times greater than orijinal value.
      2. In Bex reporting divide this number by 10000.

      Regards,

      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Thanks all for your valuable contributions. As you have correctly stated there are other ways to address the precision loss issue with key figures.

      In the example given the key figure used was intended as an interim key figure only (meant only for downstream calculation in the backend) and not intended to feature in any frontend reports (which use the OLAP server). Hence there should be no negative impact on frontend performance due to this.

      Hopefully the blog (along with comments) will serve as a guide for key figure modeling for the SAP BW community.

      Author's profile photo Former Member
      Former Member
      good blog... i will also agree with the other solutions provided in the comments by others.. they seems to be pretty easy solutions.