Wave Multi-Currency Datasets using Apex

In this post, we are going to show that with Apex, it’s as easy as A-B-C to make Wave datasets speak € ? z?.

There are a variety of tried-and-true ways to load data into Wave:

  • Upload CSVs via a UI
  • Implement ETL tools that connect to the external API
  • Use a connector for Microsoft Excel
  • Create a Dataflow JSON to bring in Salesforce data

These options are covered in great detail in the help docs, and so we won’t go over those here. On the other hand, one of the more interesting and powerful methods is to use Apex to create data in Wave. This method is great for cases where your data resides in Salesforce, but requires complicated transformations to get it into the right form in Wave.

Multi-currency data is one such gnarly problem, and I felt would make a great proof of concept to demonstrate what could be done with Apex. In addition, since advanced currency management supports a limited number of objects and only an opportunity’s close date, using Apex gives the additional benefit of being able to support any custom object, and exchange rates that can be based on any date field. Apex FTW, right?

Let’s talk about multi-currency setup in Salesforce

There are two multi-currency objects that are accessible, CurrencyType and DatedConversionRate, each with an ISO Code that can be matched to a Currency ISO Code on standard and custom objects. Both are pretty straightforward, they store the conversion rate from corporate currency to another currency. Dated conversion rates additionally store the date range that conversion is valid for.

And… this is where the majority of the challenge is: trying to match up the right conversion rate based on the date. Fortunately for you, we created an unmanaged package that has all this logic to convert currency fields into every currency available in an org (see below).

Let’s talk about how data gets into Wave via Apex

When I started this proof-of-concept, I thought that this would be really complicated, but I was so… so wrong. Simply, we only need to create and update these two objects InsightsExternalData and InsightsExternalDataPart.

Think of InsightsExternalDataPart as parts of one CSV file split into several records related to a single InsightsExternalData record. Create your insights external data record with action equals none, create one of more parts related to it, and then update the insights external data action to process. That starts bringing all of those CSV parts on over to Wave.

For more information on using Apex and Wave together, see this video (password: asktheACexperts).

Let’s put a multi-currency dataset into Wave

As mentioned beforehand, we’ve created an unmanaged package as a starting point for implementing this. It will create a batch process for any custom object handed to it using a given field set to create the columns as well as basing on a specific date field if dated exchange rates are in play. The most notable aspect is when we hit a currency field, we create a column for each active currency in the org (e.g. USD, MXN, EUR) and a column for each currency’s exchange rate. The final output looking like so.

Five easy steps to get it working in your org

  1. Install this unmanaged package (password: waverocks) in an org with Wave enabled and Multiple Currencies enabled.
  2. Update the fieldset named ‘WaveMultiCurrency’ with the fields you want, including a currency field such as Amount and Expected Revenue, and a dimension such as Stage and CloseDate if you are using dated exchange rates.
  3. In the Developer Console’s Execute Anonymous window, run the batch process as follows, passing in the object, date field, field set and name of dataset you want:
    MulticurrencyDataSetBatch b = new MulticurrencyDataSetBatch(
        Opportunity.SObjectType.getDescribe(),
        Opportunity.CloseDate.getDescribe(),
        Schema.SObjectType.Opportunity.fieldSets.WaveMultiCurrency,
        'MulticurrencyOpportunities',
        null
    );
    Database.executeBatch( b, 200 );
  4. Head over to Wave and when the dataset is created (you can see the progress under Data Monitor), create a dashboard from the ‘MulticurrencyOpportunities’ dataset.
  5. Edit the dashboard JSON state with the following:
    "state": {
        "widgets": {
          "currency_dimfilter": {
            "pos": {
              "w": 250,
              "x": 10,
              "h": 300,
              "y": 10,
              "zIndex": 100000
            },
            "type": "ListSelector",
            "params": {
              "selectMode": "single_required",
              "measureField": "",
              "step": "currency_selector"
            }
          },
          "chart_stage": {
            "pos": {
              "w": 450,
              "x": 270,
              "h": 360,
              "y": 10,
              "zIndex": 0
            },
            "type": "ChartWidget",
            "params": {
              "chartType": "hbar",
              "step": "stage"
            }
          }
        },
        "steps": {
          "currency_selector": {
            "selectMode": "single_required",
            "values": [
              {
                "display": "USD",
                "value": [
                  {
                    "compact_expectedrevenue": [
                      [
                        "sum",
                        "ExpectedRevenue_USD"
                      ]
                    ],
                    "compact_amount": [
                      [
                        "sum",
                        "Amount_USD"
                      ]
                    ]
                  }
                ]
              },
              {
                "display": "EUR",
                "value": [
                  {
                    "compact_expectedrevenue": [
                      [
                        "sum",
                        "ExpectedRevenue_EUR"
                      ]
                    ],
                    "compact_amount": [
                      [
                        "sum",
                        "Amount_EUR"
                      ]
                    ]
                  }
                ]
              },
              {
                "display": "MXN",
                "value": [
                  {
                    "compact_expectedrevenue": [
                      [
                        "sum",
                        "ExpectedRevenue_MXN"
                      ]
                    ],
                    "compact_amount": [
                      [
                        "sum",
                        "Amount_MXN"
                      ]
                    ]
                  }
                ]
              },
              {
                "display": "CAD",
                "value": [
                  {
                    "compact_expectedrevenue": [
                      [
                        "sum",
                        "ExpectedRevenue_CAD"
                      ]
                    ],
                    "compact_amount": [
                      [
                        "sum",
                        "Amount_CAD"
                      ]
                    ]
                  }
                ]
              }
            ],
            "isGlobal": false,
            "start": [
              [
                {
                  "compact_expectedrevenue": [
                    [
                      "sum",
                      "ExpectedRevenue_USD"
                    ]
                  ],
                  "compact_amount": [
                    [
                      "sum",
                      "Amount_USD"
                    ]
                  ]
                }
              ]
            ],
            "useGlobal": false,
            "type": "static",
            "isFacet": false
          },
          "stage": {
            "selectMode": "single",
            "query": {
              "measures": "{{ value(field(selection(currency_selector), 'compact_amount')) }}",
              "groups": [
                "StageName"
              ]
            },
            "extra": {
              "chartType": "hbar"
            },
            "em": "MulticurrencyOpportunities",
            "isGlobal": false,
            "start": null,
            "useGlobal": true,
            "type": "aggregate",
            "isFacet": true
          }
        }
      }
    

Notice in the Dashboard JSON two things. First, the static step ‘currency_selector’ contains a compact form for each measure in each currency. This is because when we created the dataset, each currency field (e.g. Amount) is added as a column for each currency in the org, and, therefore, it is a different measure. Secondly, in order to dynamically switch currencies, we add the following binding to the measure portion of any step we care about.

{{ value(field(selection(currency_selector), 'compact_amount')) }}

In other words, when a user selects a different currency, we are telling each chart to use a different measure. Sure, it’s a little bit of customization, but well worth it to get a multi-currency dataset. See the following video on the final result and how easy it is to set up a new chart binded to currency selector. Note the video does not have sound.

 

About the Author

Eric Schultz is designer, developer, architect and economist (well… when armchair available) turning information into action since the turn of the century. Eric is a member of The Salesforce Services IT LOB practice.

 

The Salesforce Services IT LOB practice

The Salesforce Services IT LOB practice is dedicated to helping Salesforce customers implement cutting-edge solutions for Analytics, Heroku, IoT and Shield. If you want help transforming your business with Salesforce, contact us via your Salesforce Account Executive or Success Services today.

Published
March 21, 2016
Topics:

Leave your comments...

Wave Multi-Currency Datasets using Apex