Enter plan data in any transactional currency in the NEW model of SAP Analytics Cloud
New model in SAP Analytics Cloud has introduced a number useful features for planning. In this blog we will discuss the currency translation using the new model and how the new model allows a planning user to enter data in any transactional currency and automatically translated in other currencies. This blog refers to the currency conversion in the new model but if you need to perform currency conversion in the earlier account based model, you can get the required information here.
Use case: The use case for this blog is that a planning user wants to enter plan data in the local currency (which for example can be GBP), the regional head of EMEA region will be reviewing and changing the entered plan data in the transactional currency (which for example can be Euro) and corporate planning head can be reviewing and changing the data in the group currency (which for example can be USD) and at each stage all three of them will be able to see the changes reflected in their own currencies automatically. Further if any of these planners wants to edit the plan data in any other currency (for which currency conversion rates are maintained in the system) then he or she will be able to do so simply by selecting that currency from an input control and all the related conversions will be done automatically in real time.
Step by step description: You can take the following steps while setting up the model. Essentially, in the following steps, we will be setting up a separate input currency dimension, linking it to the measure we use enter the plan data and then create conversion measures.
- Make sure that you have enabled currency conversion in the model preferences and maintained a currency rates table that has the conversion rates for the currencies that you want to use. This step is same for both account based model and the new model.
- Set up input currency dimension for the currencies in which the suers need to enter plan data.
- Make sure that you have all the currencies in which you want to enter the data in the input currency dimension (or the dimension that you have chosen above) and make sure that you enable currency in that dimension.
- Set up a measure with currency to be read from the input currency dimension that you created in the above step.
- Go to calculations in the model and create currency conversion measures.
- Create a local currency measure that reads the currency from the Organization type dimension. In this example, Region dimension is an Org type dimension which maintains the currency for each org dimension member. This step is also similar to what we used to do in the account-based model. By doing this we are allowing the users to use the local currency that is linked to the org dimension member where they are entering the data.
- You can create other reporting currencies as currency measures with fixed currencies. Here you can create as many reporting currencies as you need. Please note that you are only creating conversion measures here which are dynamically calculated based on the rates maintained.
- Create a story with the measures mentioned above. Now you can enter the data in any input currency or local currency and the data will get converted to reporting currency and other currencies in the story. You can edit data in any cell below and it will be converted to other currencies.
- Also if you want you can create an input control for measures for the users to select the currency in which he or she wants to enter the data.
Thus with the new model we can enter plan data now in any transactional currency and convert it to other currencies automatically in real time.
This blog is part of the Best Practice series. If you want to discover more best practices, please check out these blogs as well:
Thank you so much for the explanation.
Kindly help me with the below question
For example, if I need to convert the data from USD to GBP, I do not have the direct rates maintained in the Currency table.
Instead, I have below only loading from source system i.e BW:
USD to EUR & EUR to GBP.
I'm sure this is possible in BPC embedded but need your help to implement the same in SAC planning.
Thanks Sandeep. I think you are talking about triangulation of currency. If so, then yes it can be done in SAC. The only thing is that you have to use data actions to accomplish that in addition to the conversion measures.
Thank you for the hint towards data actions using which we can accomplish my scenario.
Can you please give a little more insight on how it can be achieved? Because not sure if we have any function for currency conversion/function to lookup into currency table within the advanced script logic.
And the conversion option in the data action will stage the data physically, and we do not want to stage the intermediate calculation in the system.
Thank you in advance,
Thanks Sandeep. You are right that you can't look up into currency table. However one way to accomplish this is to use another model to store the calculation and then use cross model copy data action to copy the calculation to the measure in the original model. Another way out can be to have multiple measures with different currencies persisted in the model (instead of using conversion measures) and calculating the converted value using data actions. In that case the second model is not required but you need two data actions - one to convert to the intermediate currency and the second to convert from intermediate currency to the target currency. If you are interested in discussing these options further then you can schedule a call with me and I can show you in the system.
Thanks and best regards