Author – Mark Judson, Senior Business Intelligence Consultant, itelligence UK
Many organisations transact and hold their data in multiple currencies. When it comes to analytics there is, therefore, a requirement to be able to easily convert and display that data in a single currency and to be able to easily switch from one currency to another.
SAP Analytics Cloud (SAC) includes Currency Conversion functionality so that Models and Stories can display data in multiple currencies. Before you enable Currency Conversion within your Model, it is necessary to create a Currency Conversion table within SAC. Unless you have a Planning Professional licence, however, you will notice that the option to create a Currency Conversion table is greyed out.
In short, a Planning Professional SAC license is required to create the Currency Conversion table. Once it is created, you can then enable and setup Currency Conversion within your Model (including Analytic Models), using a BI license.
But, what do you do if you do not have a Planning Professional license in your tenant?
This blog looks at an alternative mechanism for Currency Conversion in SAC, using external currency rate data, and then using standard SAC functionality to integrate that data in Models and Stories.
Currency Conversion Data
We will start with our Currency Conversion data. In our example, we are maintaining a file of daily exchange rates as follows, simplified to cover just two dates:
The fields within this data are as follows:
- From – From Currency
- To – To Currency
- Date – Exchange Rate Date
- Rate – Exchange Rate
- Type – Exchange Rate Type
We will now move on to our primary dataset (transaction). Again, we will keep things relatively simple for the purposes of this blog.
|Country||Revenue||Tx Currency||Tx Date|
The fields within this data are as follows:
- Country – Country Dimension
- Revenue – Sales Revenue
- Tx Currency – Transaction Currency
- Tx Date – Transaction Date
We will now build our Model in SAC. Firstly we will Import our Regional Sales data.
We will now run a “Combine Data” action, in order to add the Currency Conversion data to the Model.
We will configure the relationship so that the two are combined as follows:
Note the message in the Combine Preview, which is warning us that we may end up with duplicated measures. This is expected, and we will need to take steps later on within our Story to ensure that these duplicates do not manifest into our visualisations.
Our combined data can now be seen within the Model. Each row from our original Sales data now has multiple rows, one for each available currency conversion.
We will now add a Calculated Column to the Model to store our converted Revenue, multiplying the Revenue and the Rate:
Finally, we will update the names of 2 of our fields so that they have more meaning when we start referencing them in the Story:
- Revenue – Revenue – (Tx Currency)
- To – Display Currency
We will now Create the Model.
We will put some further protection around the Revenue (Tx Currency) Measure, to ensure that it is not displayed erroneously. Within the Model we will configure this Measure to have a “Required Dimension” of Tx Currency. In the Story we will then be forced to add the Tx Currency to any visualisation that displays the Revenue (Tx Currency), ensuring the Measure is seen in the correct context.
During the Combine Data phase of the Model build, we received a message to warn us that we may end up with duplicate measures. We need to counter this within our Story build, so at this point, we will highlight the following:
The Story MUST contain a filter or filters which restrict the output to a SINGLE Display Currency.
This ensures that we only ever see a single record per Country per Date and we eliminate any erroneous duplication of our Revenue measures. For this example, I have chosen to add a Story Filter for the Display Currency, but we could also add this as a Page Filter or as a Component/Widget Filter.
We will now add a simple bar chart, with Country as a Dimension and Revenue (Display Currency) as a Measure. We will use Dynamic Text within the chart header, to populate the selected Display Currency from the Story Filter.
We will then interact with our Story Filter in order to see the output in different currencies.
- With the Display Currency Story Filter set to GBP:
2. With the Display Currency Story Filter set to EUR:
So, it can be seen that it is possible to introduce currency conversion and multi-currency display into SAC Stories, using external currency rate data, and then using standard SAC functionality to integrate that data in Models and Stories. A few final thoughts:
- I have kept things simple by just developing a Currency Conversion based on a Daily Rate. This makes it easy to combine the Tx Date from the Sales data with the Currency Date from the Currency Conversion data. I appreciate that other conversion rate types are out there, and perhaps we could extend this solution to handle those at some point.
- This alternative approach gives the option to change the currency across the entire Story using the Display Currency Story Filter. Standard SAC Currency Conversion does not allow for this, only supporting currency selection on each component/widget.
- The need for the Display Currency Filter may cause issues for ad-hoc analysis of the Model. End-users would need to be educated on the reasons this filter is required. For standard, pre-canned Dashboard/Stories then it is not really an issue.
If you would like to discuss this in greater detail then please contact us here
Interested to learn more about SAC, then register for itelli2020 where we’ll showcase SAC with multiple presentations and our Analytics experts will be available all day on our expanded “Hands On Zone” Register here.