While loading the data from source to SAP BW system, the amount can be in local currency. If the user wants to see those amounts in USD in the BI reports, you can do the currency conversion either in the BI query designer or while loading the data to the cube.
Steps for currency conversion in the data load-
- There is amount field in the source which is Amount type key figure and there is 0LOC_CURRCY field in source which holds the unit of this amount key figure like EUR or INR etc.
- Define a target info object say ZAMNT_LC for this field in the BW system with amount type key figure and unit as local currency-
- Map these objects as one to one from the source
- Define a new key figure in the target to hold the amounts in USD. Say for example, ZAMNT_USD.
- Go to tcode RSCUR and create a currency translation type say ZAM_USD-
- Give a description, go to the Exchange rate tab and give the exchange rate type. (Exchange rate types are standard exchange rates available in the SAP system. You can see the exchange rate types and their conversion factors in the TCURR table. For example, for conversion to USD, if the exchange rate type given is ‘M’, the conversion factor is accordingly selected.)
- Check the option “Dynamic Exchange rate determination”
- In the Currncy tab, select the option “Source Currency from data record” option and give “Fixed target currency as “USD”
- In time reference tab, give fixed time reference as “Current date”
- Leave other settings as default and save. The currency translation type ZAM_USD is now created.
- Now go to the transformation for source to target cube data load. Click on ZAMNT_USD field and add the source fields ZAMNT_LC and 0LOC_CURRCY
- In the currency drop down, select fixed target currency as USD and select ‘from conversion’. In the conversion type give ZAM_USD and in the source currency, give 0LOC_CURRCY. Check and save. Activate the transformation.
- Now while data load, the ZAMNT_USD will have amounts converted to USD
Note: This feature of currency conversion is available only if the target is Standard Cube. If the target is DSO, the currency conversion had to be done via ABAP routine.
Steps for currency conversion during the BI report execution-
Instead of doing the conversion in the data loads, the same can be done during the runtime of the BI reports.
Caution: If the BI report is large with many fields and calculations, doing currency conversion during query runtime may lead to reduced performance.
- Once we have the translation type ZAM_USD defined, we can use the same in the query designer
- Drag the key figure ZAMNT_USD in the columns pane, currently this is mapped one to one to ZAMNT_LC while data load
- In the properties pane, select the ‘Conversions’ tab
- Give the Conversion type as ZAM_USD
- Type target currency as USD
- Save the query. Now when the query is executed ZAMNT_USD will show the amounts in USD
Exchange rates in Source System and Target System
The source system and target system have the same exchange rates e.g. M, but the ratios may differ.
To check the same, go to the source system and type the tcode SE37. Execute the function module ‘CONVERT_TO_FOREIGN_CURRENCY’
Give a date and sample amount for converting say EUR to USD with exchange rate ‘M’.
The function module will give a value.
Now check the same in the target system.
You can also use the program ‘RCURTEST’ to check the exchange rate-
Go to SE38 and execute the program-
Many times the exchange rate has to be updated from source to target to system to get the correct conversion in the BI reports.
Check with business on this, if they confirm that the conversion should be done according to the source conversion rates, include a step in the process chain for refreshing the rates in the target system.