Automate Currency Conversion Process in Einstein Analytics

As many of us are aware, there is no cut-and-dried solution to working with multiple currencies in Einstein Analytics. However, there are customized Einstein Dataflow solutions that can be implemented in order to address this issue. I will  explain some of these options through illustrations of three different scenarios about multi-currency handling.

This blog post will be useful in either a) Creating multiple fields (multiple currencies) with a single existing Currency field (ex: having an existing Amount field in USD and creating multiple fields in different currencies like CAD, EUR etc.) or b) More importantly working with multiple fields (with multiple currencies) and standardizing them to one currency (having different fields Amount1, Amount2 in CAD, EUR respectively, which we standardize to USD)

We identified three different scenarios, which are the most common Currency conversion use cases:

  1. We have a single measure field on a Salesforce Object ex: ‘Opportunity Amount’ which is in USD, but on Einstein Analytics dashboard you want a toggle, to switch between say USD, CAD, EUR, GBP
  2. We have multiple fields in Salesforce Object/s in multiple Currencies, but on Einstein Analytics we want to put them in a single Lens and compare. (Standardize to USD)
  3. We have data sourced from multiple Salesforce Org’s but we are dealing with this cross org data in a Global Org (Parent Org Salesforce instance), Einstein Analytics, where each Org has different default currency. (Compare several Org’s data, by creating single dataset with common unit of Currency)

Prerequisite:

We need to enable/use Salesforce Standard Object called ‘Currency Type’, which holds the different currency rates of various currencies. Standard fields on this object namely ‘ISOCode’ and ‘ConversionRate’ are crucial for the entirety of the  currency conversion solution that we are going to provide.

‘Currency Type’ Object is basically like a Table which holds the list of currencies and their conversion rates. Whenever conversion rates change, if this Object is updated in Salesforce, that will take care of our Conversions in Einstein Analytics as well.

Scenario 1:

Let’s talk about opportunity Amount, say your Amount field is in USD and you have a use case to create a toggle on Dashboard with ability to change between USD, CAD, EUR and GBP. Instead of writing SAQL steps on dashboard for these calculations (which need to be manually updated whenever conversion rates change), we can automate the transformations in Dataflow.

Step 1:

This Process involves, creating Dummy Currency code fields using ComputeExpression transformation on your Opportunity dataset. The idea is to have values CAD, EUR and GBP on every record in four different columns at Opportunity Grain, which will be used as Join keys in next step.
Using ComputeExpression transformation on Dataflow editor, create the dummy currency fields as shown below.

Note: These dummy currency fields need to be created on the Dataset Grain node, for example, as Opportunity Digest node.

JSON Code:

“ComputeExpression_Dummy_CurrencyCode_Fields”: {
“action”: “computeExpression”,
“parameters”: {
“source”: “Digest_Opportunity”,
“mergeWithSource”: true,
“computedFields”: [
{
“name”: “DummyFieldCAD”,
“saqlExpression”: “\”CAD\””,
“label”: “DummyFieldCAD”,
“type”: “Text”
},
{
“name”: “DummyFieldE”,
“saqlExpression”: “\”EUR\””,
“label”: “DummyFieldE”,
“type”: “Text”
}
] } },

Step 2: Now filter the ‘Currency Type’ Object node for various currencies separately and create Currency specific nodes. These streams will carry the conversion rates of each currency individually.
Using Filter transformation on Dataflow editor, create the dummy currency fields as shown below.

Note: Filter transformation is used to filter the ‘Currency Type’ Extract node to single out streams that carry individual currencies separately.

For CAD:
“Transform_Filter_CurrencyType_CAD”: {
“action”: “filter”,
“parameters”: {
“filter”: “ISOCode:EQ:CAD”,
“source”: “Digest_CurrencyType”
}
},

For EUR:
“Transform_Filter_CurrencyType_EUR”: {
“action”: “filter”,
“parameters”: {
“filter”: “ISOCode:EQ:EUR”,
“source”: “Digest_CurrencyType”
}
},

Similarly for GBP, and basically any other currency, we need to filter the ‘Currency Type’ Object node and create separate streams that carry the conversion rates of these currencies as well.

Step 3: Now we need to use several Augment transformations and perform Left outer join between our Opportunity node (with dummy fields in Step1) and these ISO Code filtered Currency Type nodes (Step 2). Augment nodes have to refer each other sequentially for each currency. Start Augment transformation with one node and continue to the next by referring the previous one.

“Augment_OppDummyCurrency_CurrencyType_CAD”: {
“action”: “augment”,
“parameters”: {
“right_key”: [
“IsoCode”
],
“left”: “ComputeExpression_Dummy_CurrencyCode_Fields”,
“left_key”: [
“DummyFieldCAD”
],
“right_select”: [
“ConversionRate”
],
“right”: “Transform_Filter_CurrencyType_CAD”,
“relationship”: “CAD”
}
},
“Augment_OppDummyCurrency_CurrencyType_EUR”: {
“action”: “augment”,
“parameters”: {
“right_key”: [
“IsoCode”
],
“left”: “Augment_OppDummyCurrency_CurrencyType_CAD”,
“left_key”: [
“DummyFieldEUR”
],
“right_select”: [
“ConversionRate”
],
“right”: “Transform_Filter_CurrencyType_EUR”,
“relationship”: “EUR”
}
},

This will create new columns on our Opportunity Dataset with Currency conversion rates of respective currencies.

Step 4: The final step is to create another computeExpression node and create calculated fields with Arithmetic product that does Amount(in USD) * CAD.ConversionRate. This gives you the Amount field in CAD currency. Similarly, use the other conversion rate for respective currencies. (See Step 4)

“ComputeExpression_AUTO_STACK_Currency”: {
“action”: “computeExpression”,
“parameters”: {
“source”: “Augment_OppDummyCurrency_CurrencyType_EUR”,
“mergeWithSource”: true,
“computedFields”: [
{
“defaultValue”: “0”,
“precision”: 18,
“name”: “EUR_EURO_R”,
“saqlExpression”: “(‘Amount_USD’ * ‘EUR.ConversionRate’)”,
“scale”: 5,
“type”: “Numeric”
},
{
“defaultValue”: “0”,
“precision”: 18,
“name”: “Canadian_Dollar_R”,
“saqlExpression”: “(‘Amount_USD’ * ‘CAD.ConversionRate’)”,
“scale”: 5,
“type”: “Numeric”
}
}

This process will create new columns/Fields in your dataset with Amount field converted to multiple currencies

Scenario 2 and 3:

Usecases involving comparing results for Businesses in multiple currencies, is cumbersome. A comparison between different currencies is a comparison of apples to oranges.

Scenario 2 is slightly different from Scenario 1. Here, we are working with not one Numeric field, which is by default in one currency(USD), but with multiple fields in multiple currencies.

The bulk of the solution to this issue is to Standardize these multiple currency fields to one Single currency. For example, getting Four currencies to work with i.e., USD, CAD, EUR,  and GBP. The first step is to standardize these multiple currencies to single currency, ex: USD.

We again need to use ‘Currency Type’ Object and follow Step 1, Step 2 and Step 3. As we did for Scenario 1 i.e., we have to create dummy fields with Currency codes at their individual grain (as shown in the first page for field ‘DummyFieldCAD’ in Step 1).

Follow Step 2 and 3 from Scenario 1, by creating Filter Transformation nodes for each currency you are dealing with and Augment Transformation that puts the conversion rate of respective currencies on each record.

In order to standardize to USD, we have a unique step for this scenario, where we need to divide an Amount field in CAD with CAD conversion rate, to transform it to USD. Similarly convert all other currencies to USD, by dividing with their respective conversion rates.

Step 3a:

“ComputeExpression_Standardize_Currencies_USD”: {
“action”: “computeExpression”,
“parameters”: {
“source”: “Augment_MultipleCurrencies”,
“mergeWithSource”: true,
“computedFields”: [
{
“precision”: 18,
“name”: “Amount1_CAD_USD”,
“saqlExpression”: “(‘Amount_CAD’ / ‘CAD.ConversionRate’)”,
“scale”: 6,
“type”: “Numeric”
},
{
“precision”: 18,
“name”: “Amount2_EUR_USD”,
“saqlExpression”: “(‘Amount_EUR’ / ‘EUR.ConversionRate’)”,
“scale”: 6,
“type”: “Numeric”
}
]
}
}

Now if we need to create a multi currency toggle, follow step 4 from Scenario 1, for each of the measure in step 3a separately i.e., Multiply ‘Amount1_CAD_USD’ and ‘Amount2_EUR_USD’ with conversion rates.

Step 4 is only optional here, depending on the requirement. But the basic usecase of being able to compare different currencies(by standardizing them to USD) has been achieved in step 3a.

Scenario 3: Multiple Salesforce Org’s with different default currencies

This is very similar to Scenario 2, but usually you end up Appending (Union with Append Transformation) data from different data sources (multiple orgs) and working with a single dataset. The only difference is we have to standardize the currencies before we Append the multi org’s data into a single dataset i.e.,(Step 1, Step 2, Step 3 and 3a need to be done separately for each Org data and standardize all multiple currencies to USD, and then use the Append transformation to create a single dataset). Follow by Step 4 to toggle between different currencies.

There is one more scenario where you might have a single field with multiple currencies. In this scenario, you need to have a unique identifier for each currency on the record level. This can be created as a Currency code field on the Object itself in Salesforce, like having a currency code field on Opportunity which differentiates which currency the Opportunity Amount is in. Once you have this Unique identifier for currencies, repeat Step 2, 3, 3a and 4 to achieve the same results.

Dataflow in dataflow editor should look similar to this below screenshot. If you need Dataflow JSON reference, see this link.