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.

tagged , , Bookmark the permalink. Trackbacks are closed, but you can post a comment.
  • Endive software

    Thanks for sharing your information. Its very useful blog for new salesforce developer. but i have something for you. Endive Software Introduces Clone Power: An Efficient and Safe Tool for Cloning Records http://www.endivesoftware.com/hire-salesforce-developer.php

  • chinmaye sri

    clear information about wave multicurrency datasets using apex with video really nice </a

  • Nicolas Bragard

    Hello
    I followed the steps described above. I was able to install the package and run the batch process but I don’t see any progress under Wave Data Monitor and I am unable to find the ‘MulticurrencyOpportunities’ dataset in Wave. I waited several hours but it is still not there. I also tried to run the batch process on a custom object after adding the multicurrency field set and I didn’t get any dataset in wave. I don’t see any error in the execution log. I have full administration privileges on the Salesforce instance I am using and Wave is installed with a valid license. I have already created several datasets manually in Wave and some dashboards so I think I am all set to use this package.
    Any idea about this issue?
    Many Thanks

    • Nicolas Bragard

      I just had a look to the Apex Jobs log and it looks like the first batch I launched more than 12 hours ago is still running.
      It is showing:
      Total Batches: 16248
      Batches processed: 4262
      I think it will have to wait for all batches to be completed before beeing able to see the dataset in Wave?
      I have aborted all other similar batches I ran since yesterday.
      If my estimates are correct the running batch will take 2 days to complete. This is the one I did on a custom object with about 1,300,000 records. I don’t think it is a viable solution to build a multi-currency dataset using this package. Maybe this is due to the number of currencies we have? (about 25)
      Any help/advice would be greatly appreciated.