Technical Articles
SAP Analytics Cloud & Currency Conversion (Part 1)
Some of you might have the need to create a currency conversion in SAP Analytics Cloud because the backend doesn’t provide all the required information. In such a scenario we can easily create a Currency Translation table in SAP Analytics Cloud and then leverage the information to allow for a Currency Conversion in our stories.
For SAP Analytics Cloud to be able to provide the ability to translate data from one currency to another, you will have to configure the source currency in your data, as well as you will have to setup the Currency Translation table with the details on the valid period and the translation details.
For our example we will use a relatively simple data set and we will configure the source currency in the model in SAP Analytics Cloud.
You can download the source file for this exercise following this URL:
https://sapanalyticscloudbook.com/sample-stories and the file name is
Mastering_SAP_Analytics_Cloud_chapter09_DataSource004.xlsx
Our source data contains a dimension for an Order ID, an Order Date, a dimension for the Country, and a dimension for the Currency as well as a measure for the Order Value.
Figure 1: Source Data
Now lets create the necessary model.
- Logon to your SAP Analytics Cloud tenant.
- Navigate to the Home menu.
- Select the menu Create Model.
- Select the option Import a file from your computer.
- Click Select Source File.
- Point to the downloaded file for this exercise.
- Ensure the option Use first row as column headers is enabled.
- Click Import.
Figure 2: Imported Data
- Select the column header for Order Date.
- Ensure the Type is set to Date Dimension and the Date Format is set to MM/DD/YYYY.
- Ensure the Order ID column is configured as Generic Dimension.
- Select the column header for column Country.
- Set the Type to Organization.
- Click Add Dimension Attributes.
- Select the option Currency.
- Select column Currency as the Currency Attribute.
- Select the column header for Order Value.
- Ensure Order Value is configured as Measure.
- Click Create Model (bottom right).
- Confirm the message by clicking Create.
- You will be asked to select a folder.
- Select a folder, enter a name and description.
- Click OK.
Figure 3: Data Management
- Use the disk symbol in the General menu to save the changes.
- Navigate to the Home menu.
- Select the menu Create • Currency.
- Select the option New Currency Conversion Table.
- Enter CurrencySample as Name and Description.
- Click Create.
Our sample data has values for January 2019 to July 2019 for the currencies USD, EUR, and CAD. Our Currency Translation table will need to have entries to cover the timeline as well as those currencies as source and target currencies.
- Enter the following entries:
Source Currency | Valid From | Target Currency | Exchange Rate |
USD | 2019.01.01 | EUR | 0.88 |
USD | 2019.01.01 | CAD | 1.31 |
EUR | 2019.01.01 | USD | 1.14 |
EUR | 2019.01.01 | CAD | 1.49 |
CAD | 2019.01.01 | EUR | 0.67 |
CAD | 2019.01.01 | USD | 0.76 |
Table 1: Currency Translation
- Use the disk symbol in the toolbar to save the changes.
- Navigate to the Home menu.
- Select the menu Browse Files.
- Navigate to the folder where you saved the previously created model.
- Open the model.
- Select the option Model (top left).
- Navigate to the General menu in the toolbar.
Figure 4: Menu General
- Open the Model Preferences.
- Navigate to the area Currency.
Figure 5: Currency Preferences
- Enable the option Currency Conversion.
- Ensure the Currency Dimension is set to Country.
- Set the Currency Rates Table to the CurrencySample table.
- Set the Date Dimension to Order Date.
- Click OK.
- Save the changes to the model.
- Select the option Model (top left).
- Now click on the Account dimension.
- Select the entry Order Value.
- In the Details panel on the right-hand side scroll down to the area Formatting.
- Ensure the option Units & Currencies is set to Currency.
Figure 6: Formatting
- Navigate to the menu General
- Save the changes to the model.
In the last few steps we create a model and uploaded data with values for three different currencies. We also created a currency translation table and enabled the currency conversion in the model. We have now all the information, so that we can create a story in the next set of steps and use the newly modeled information.
This is an easy explanation of implementing currency conversion in SAC. Thanks Ingo
Hi Ingo,
Hope you are doing great. Thanks for this brilliant blog.
We do not find sample data excel at the below link:
https://sapanalyticscloudbook.com/sample-stories and the file name is
Mastering_SAP_Analytics_Cloud_chapter09_DataSource004.xlsx
Thanks to upload the file and share the link.
Regards!
Tariq Ashraf
Hi,
the file is already there - second from the bottom.
Regards
Ingo Hilgefort
Ingo,
That link and the one below it both point to the file Mastering_SAP_Analytics_Cloud_chapter09_DataS-0003.xlsx
Thanks
Hello Todd,
the link shows to a website:
https://sapanalyticscloudbook.com/sample-stories
and there is a list of files on that page.
Regards
Ingo Hilgefort, SAP
Hi Ingo,
Hope you are doing good.
Is it always necessary to give currency dimension ? It was not mandatory BEx query then why in SAC ?
Regards,
Amit Jain
HI Ingo,
Thanks. What about UoM conversion? Is there a feature planned to be released to implement UoM conversion rate table as well?
Thanks,
Michel
Hi Ingo,
When doing the currency conversion as you said on above i am getting the below error.Can you tell the reson for it.?
Thanks,
k.v.k.reddy
Hi,
thanks for the article. One question however regarding creating currency table. Do you need Planning license in order to have that option? I don't see that to be available for me in my SAC tenant.