SAC Currency Conversion – An Alternative Approach

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.

This is referenced in SAP’s “Features by License Type for Planning Models” 

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:

From	To	Date	Rate	Tpe
GBP	GBP	02/10/2019	1	Daily
GBP	EUR	02/10/2019	1.1223	Daily
EUR	EUR	02/10/2019	1	Daily
EUR	GBP	02/10/2019	0.891	Daily
GBP	GBP	03/10/2019	1	Daily
GBP	EUR	03/10/2019	1.1245	Daily
EUR	EUR	03/10/2019	1	Daily
EUR	GBP	03/10/2019	0.8893	Daily

 

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

Sales Data

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
UK	25000	GBP	02/10/2019
France	42000	EUR	02/10/2019
UK	27000	GBP	03/10/2019
France	38500	EUR	03/10/2019

The fields within this data are as follows:

• Country – Country Dimension
• Revenue – Sales Revenue
• Tx Currency – Transaction Currency
• Tx Date – Transaction Date

SAC Model

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.

 

SAC Story

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.

1. With the Display Currency Story Filter set to GBP:

 

2. With the Display Currency Story Filter set to EUR:

 

Conclusion

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