Multi-Currency in Einstein Analytics

Implementing a solution that enables users to display dashboard widgets in the currency of their choice using historical conversion rates

Outline

Suppose the users of an Einstein Analytics dashboard are active in different countries with different currencies, while the transactional data that in the source data of the dashboard is stored in a central currency: how could we satisfy the requirement to display all monetary data in the preferred currency of each user in a structured and reliable way?

In this blog a solution to this question is described in a step by step approach. In order to implement a similar solution yourself, what you need is:

  • Source data from a source system (Salesforce CRM or some other application) in which all monetary values are in a central currency ( in this blog)
  • A database table (whether in an application or a static CSV file) containing the historical conversion rates from the central currency to all other relevant currencies
  • Knowledge of these Einstein Analytics components: dataflow transformations, custom queries & bindings

The solution will lead to a dashboard with a currency filter widget through which a user can transform all charts and tables into the currency of his choice:

Implementing the solution

The Conversion Rates dataset

To be able to convert any monetary value into another currency we need to know the historical conversion rate of that currency at the correct moment in time. Therefore, a historical conversion rates dataset is needed in Einstein Analytics. The source of this dataset can be any of the following:

  • CSV file 
  • Conversion Rates Custom Object in Salesforce
  • Database table from an external application

The structure of this dataset is simple, the following columns are needed:

  • IsoCode: the code of the specific currency, in our case it contains these values:
    • GBP (Great British Pounds)
    • SEK (Swedish Krona)
    • CHF (Swiss Francs)
    • RUB (Russian Ruble)
  • Year
  • Month
  • Conversion Rate
  • IsoPeriodKey: a combination of IsoCode, Year and Month, like ‘GBP201601’

Using a custom object in Salesforce is one option to administer the historical conversion rates, in this case the IsoPeriodKey can be built through a standard formula field and the records can look like this:

 

 

 

 

 

 

 

In case of using an external application as source, the IsoPeriodKey field can be created using a ComputeExpression transformation in dataflow in Einstein Analytics. When using CSV uploads, you could either calculate the IsoPeriodKey values in dataflow or build them in the CSV file itself.

The Conversion Rates dataset provides us, for every month in the relevant history, the appropriate conversion rate from the central currency to the target currency. 

The Dataflow

In this blog’s scenario we will work with the Opportunity object from Salesforce, from which we collect data of 4 fields: Name, StageName, CloseDate and Amount. In the dataflow we want to establish that the Amount field (which is in EUR) is converted into the other currencies (GBP, SEK, CHF and RUB) using the historical conversion rate that was applicable on the CloseDate of the Opportunity. We will do this using Augment transformations to the Conversion Rates dataset. The complete dataflow looks like this:

 

 

 

 

 

 

 

The first part of the dataflow (the top row) is about extending the Opportunities table:

  1. Digest the Opportunity object from Salesforce
  2. Add one field per target currency using a Compute Expression transformation.

     

 

 

 

 

 

 

 

 

3. Calculate the Month and Year using a Compute Expression transformation (for the Month field set Precision to 2, for the Year field set Precision to 4)

 

 

 

 

 

 

 

 

 

 

4. Create the key fields, which are a combination of IsoCode, Month and Year

   

 

 

 

 

 

 

 

 

The next part of the dataflow (bottom part of the dataflow image) is about incorporating the correct conversion rate for each currency:
  1. Digest the Conversion Rates dataset
  2. For each target currency we need an Augment transformation that finds the correct conversion rate (which happens by matching the key field on Opportunity stream with the IsoPeriodKey field in the Conversion Rates dataset
    • Name: augmentConversionRateGBP
    • Left Source: [“ce_CreateKeyFields”]
    • Left Key: [“GBPKey”]
    • Relationship: GBP
    • Right Source: [“sfdcDigest_Conversion_Rate__c”]
    • Right Key: [“IsoCodePeriodKey__c”]
    • Right Fields: [“Conversion_Rate__c”]
    • Operation: Look Up Single Value
    • A similar Augment transformation is implemented for each other target currency
    • Using these Augments we bring the correct conversion rates to the Opportunities dataset

 

3. Convert the amount field into each currency (set Precision to 16 and Decimals to 2)

 

 

 

 

 

 

   

 

 

4. Register the enriched Opportunities dataset

Once this dataset has run, the resulting Opportunities dataset contains an Amount column for each currency:

 

 

 

 

Bindings and The Dashboard

In the dashboard there is a bar chart that displays Opportunity Amount by StageName, with the Amount in EUR. Through a list widget we want the chart to update, displaying the Amount in the chosen company. In Einstein Analytics terms this kind of mechanic is referred to as ‘dynamic measure’ (meaning that the Y-axis of a bar chart changes based on a selection).

In order to achieve this behaviour a couple of steps are needed:

  1. We create a custom query (this is the new name for a ‘static step’) containing the currency IsoCodes and implement it on a list widget that is set to ‘Single-selection (required)’. In the dashboard JSON we set the parameters for this custom query as in the image below. The “Display” value is what appears in the drop-down list, the “mea” value is what we will pass to the chart widget in order to change the measure upon selecting a currency.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. In the dashboard JSON we implement the binding that will make the chart update based on the selected currency like this:

 

 

 

 

 

 

 

The measure is no longer static, but it depends on the selection of the Currency_1 custom query. It is a dynamic measure now, exactly as we needed it to be to establish our goal!

Click here to see how the Multi-Currency feature looks like in Analytics

Notes

Standard Multi-Currency in the Salesforce org

While historical conversion rates can be implemented in Salesforce setup, this feature is not a great match for our use case as in this feature a conversion rate (for a specific currency) has a start date and an end date. In the Augment transformation in dataflow you want to match a row (that has a date field) with a row of the Conversion Rates dataset, which is hard to establish when the Conversion Rates rows do not have a consistent key but rather a time interval.

Historical Conversion Rates on a Daily Basis

In the described solution in this blog, historical currency rates are maintained on a monthly basis. In case conversion rates can be changed on a daily basis, a similar solution can be implemented: 

  • The Conversion Rates dataset should be extended with a ‘Day’ field and this ‘Day’ field should also be incorporated into the IsoPeriodKey field. 
  • The date fields in the source dataset (Opportunities in our example) should be broken down into Year, Month and Date so that the proper key can be created using a computeExpression transformation.
  • With that, each date field in each row of the source dataset can be matched with a row in the Conversion Rates dataset and the conversion can be done based on that.

Multiple monetary fields in a dataset

In case there are multiple monetary fields in a dataset (and they need to be converted), this comes down to creating key fields per monetary field and then doing the append transformations for each target currency for each monetary field. With many monetary fields in a dataset the described solution probably is not great. If you have a better approach for that, I’d love to hear about it!