CDS view currency conversion performance

[former_member194416]

Hi All,

Currency_conversion function in CDS view  terribly decrease performance of the cds  view,  view running in few seconds run for minutes after adding currency_conversion function. I am working on 7.40 SP10 system.

- I could not find any note for the issue for my system's support pack level ( I will open one )

- I tried to select  conversion ratio from tcurr table but it is necessary to subtract gdatu date from 9999999 to get actual date and could not cast gdatu to numeric field to  be able to perform necessary arithmetic operation in cds view. 

- Last solution I have in mind but  trying to avoid  is creating ZCURR table with proper date field instead of gdatu and filling it for the past and recent currency values using a simple program and daily job.

Do you have any other solution for above problem ? I searched the forum but find only one post for the issue which does not suggest any solution 

[former_member194416]

Solved myself ( But I am open if any of you know a better solution )

I succeeded to apply second approach above, it is not possible to cast nested but it is possible to call view while creating another view, to have gdatu as numeric field and subtract from 9999999 to get real date then converting back again to date field. I did it creating 3 CDS views.

View  1 ( Aim is to change gdatu to numc type )

All fields are selected from tcurr  and gdatu is converted to numc with below statement

   cast(tcurr.gdatu as abap.numc(8)) as fnumc

View 2  ( Aim is getting integer value for current date by changing fnumc field and subtracting it from 9999999 )

All fields are selected from view1 and fnumc is changed to int4 and subtracted from 99999999 and we have real date as integer

  99999999 -   cast(fnumc as abap.int4( 10 ) ) as fdate

View3 ( Aim is to have  proper date field instead of fdate which is in int4 type )

All fields are selected from view 2 and First eight character of value fdate is taken using substring function then used cast to have proper date type.

  cast( substring( cast(fdate as abap.char(11) ) , 1 , 8 )    as abap.dats ) as crdate

Now I am able to get currency for required date and don't need to use CONVERT_CURRENCY function and it is much much faster (5 minutes to 3 seconds ) than using CONVERT_CURRENCY cds function.

                     inner join   zxxbw_ddl_tcurr on crdate =  bkpf.bldat    and

                                      zigbw_ddl_tcurr.kurst = 'M' and

                                      zigbw_ddl_tcurr.fcurr = 'USD' and

                                      zigbw_ddl_tcurr.tcurr = 'EUR'

[Former Member]

Hi Gungor,

Last Friday I had a Code Jam event in NL and asked for input on this question. They acknowledged performance issues, but these should have been resolved with HANA SP 10.

On which HANA SP are you running?

I'll definitely try out you approach as well and do some testing again on this topic by using functions.

Thanks for sharing your experience.

Regards,

Bart

[former_member194416]

Hi Bart,

Our HANA is sp10 but there is still performance problem, Solution I applied is in productive system for some time and it is ok, and I find out  nested casting available  in ABAP 7.5 ( we are currently 7.40) So in a system where SP is ABAP 7.50 above approach can be applied using only one view instead of three.

Regards,

Gungor

[Former Member]

Hi Gungor,

I've got another issue, maybe you can help me as a currency conversion guru . I have a calculated currency field - because of performed calculations (price * quantity / price_unit ) I had to cast the field to FLTP. Now I need to perform a currency conversion, but this requires the amount as a CURR data type. The problem is that once I have it as FLTP I can't cast it to anything else.

We are on SP12, I haven't tested with mass data yet to see if we have the performance issue, but I will probably have to use your approach due to my casting problem. Do you see another option?

Thanks,

Marek

[former_member194416]

Hi Marek,

There is division function in CDS which can be used without casting fltp below code from my view. You can check its details from ABAP help documentation.

division ( bseg.dmbtr,  zigbw_ddl_tcurr.ukurs, 5  )

Regards,

Gungor

[Former Member]

Hi Gungor,

thanks, the division function solved my problem.

Cheers,

Marek

[christian_seitel]

Please note that the result of a currency conversion usually is not simply produced by multiplying the source amount with some value maintained in table tcurr.

Regards, Christian

[Former Member]

Hello Christian,

thank you for the comment. That is why I wanted to use the standard function. Another problem with Gungor's approach is that it requires a conversion rate in the TCURR table for any date on which the transaction data occurs (join condition crdate =  bkpf.bldat) - this ist not given in my case, I need the last rate closest to my transaction date.

Do you by any chance have a solution for the issue with casting?

Regards,

Marek

[former_member194416]

Hi Christian,

I am very well aware of that, above solution is tested and used as a workaround, and this workaround saved us from big trouble where view for a month was running for hours on HANA  just because of standard currency_conversion function in CDS . For the function we have opened an incident but still no solution from SAP. If we have the solution I will change it back to the standard cds function and publish the result here.

Regards,

Gungor