Dynamic Currency Conversion in Sisense

[intapiuser]

Currency Conversion.zip

https://data.sisense.com/sisense-shares/Currency Conversion.zip

Introduction

For Sisense users with international data, a common issue is how to show revenue/costs across different currencies.  This example assumes the source data is all stored in a base currency (USD in this example) and uses exchange rates at a daily level.

(Sample data files, ecdata and dash, attached below)

We will start by preparing our data model to support this type of analysis and then build a dashboard for our market basket analysis.

Prepare Your Data Model
In our example we will use a Microsoft ‘AdventureWorks’ database sample, which holds the sales transactions split between an Order Header and Order Detail tables.  Open the attached CurrencyConversion Elasticube in the Sisense ElastiCube Manager

• Customer - This table lists out the different customers, which is referenced by the Order Header table
• Order Header - This contains information about each order, and includes the OrderDate (which we will use to get the exchange rate)
• Order Details - This contains the detail line items for each sales order, and includes the LineTotal (which we need to convert to another currency)
• Exchange Rates - This table contains the exchange rate for each day in several currencies along with an option for different currency types.  This rate type may apply only to specific use cases, and as a result it may not be required for everyone.  In this example, the Exchange Rate Currency and Exchange Rate Type will be required filters on every dashboard to make sure the calculated Sales Amount will use only 1 currency and 1 rate type.

The first step was to create relationships between the various tables, the important one being the relationship between the Exchange Rates and Order Header.  This link will allow us to lookup the proper rate for each LineTotal based on the OrderDate.  

Our data model is now ready, the next step is to add in the Currency Conversion Plugin.

Configure the Plugin

Download and unzip the attached plugin, and copy it to your C:\Program Files\Sisense\PrismWeb\plugins folder.  Open up settings.js, which contains the configuration settings for currency conversion.  Because not every Elasticube on your server may require currency conversion, this configuration file allows you to specify the options for each Elasticube.  The only section that will require modification is the definition of the currencyElasticubes variable.

var currencyElasticubes = {
 'CurrencyConversion': {
  'filters': [
   {
    'dim':'[Exchange Rates.Exchange Rate Currency]',
    'label': 'Currency',
    'datatype': 'text',
    'default': 'USD',
    'isCurrencyFilter':true
   },
   {
    'dim':'[Exchange Rates.Exchange Rate Type]',
    'label': 'Exchange Rate Type',
    'datatype': 'text',
    'default': 'A',
    'isCurrencyFilter':false
   }
  ],
  'formulas':['Sales Amount','Avg Unit Price'],
  'symbols': {
   'default':'',
   'USD':'$',
   'EUR':'€',
   'CAD':'C$',
   'GBP':'£',
   'JPY':'¥'
  }
 } 
}

var enabledByDefault = true;

• currencyElasticubes - An dictionary that contains an object for each Elasticube that requires currency conversion.  Use the name of the Elasticube as the key, and the value should follow the format in the example above.
• filters - An array of filters that are required for this Elasticube.  This may only be 1 filter item (currency), but its listed as an array in case you have multiple types of exchange rates per currency
• dim - This is the table and column from the Elasticube.  This uses the following format  [table name.column name]
• label - The label to use for the filter within the dashboard
• datatype - the datatype within the Elasticube (text, numeric, datetime)
• default - The default value to use for this filter
• isCurrencyFilter - This dashboard needs to check the currency filter, in order to figure out which symbol to use in each widget.  This flag indicates that this is filter to check for
• formulas - An array of formula names that use the currency conversion.  The dashboard needs to dynamically display a currency symbol, but this only applies to formulas that use the exchange rate.  For example, you may want to count the number of orders or sum the quantity sold and neither one of these KPIs should display a currency symbol.
• symbols - A dictionary of the currency symbols, use the currency identifier as the key and the symbol as the value.  In this example the Exchange Rate table has currency codes, so these codes are used in the configuration to match.

Create the Dashboard
Now that the data model is complete and the plugin is configured, the last step is to create a dashboard that uses the exchange rates. Create a new Dashboard that uses the Elasticube with exchange rates.  When the dashboard is created, you should automatically see any required filters (specified in the settings.js file) added to the dashboard.  This ensures that dashboard only displays the revenue/costs for only 1 currency at a time (instead of summing for all currencies).  You can add your widgets like normal, but make sure to add in a formula that uses the exchange rate.  

In the attached example, the exchange rates represent the currency amount per $1 USD.  So the calculation below can be used to calculate the value based on a user selected currency (an rate type)

SUM([LineTotal All Items] / [Exchange Rate All Items])

When creating this formula, make sure the fields specified are of type = "All Items".  We want each value divided by its corresponding exchange rate, and then take the SUM of the results.  Save the formula (using the star icon), and ensure the formula name matches what was entered in the settings.js file. 

Notes

• The exchange rates included in this Elasticube were downloaded from https://www.oanda.com/currency/table
• The required filters specified in settings.js are automatically added to every dashboard based on the dashboard's Elasticube.  This is to ensure the formula(s) requiring an exchange rate only return 1 rate per revenue/cost amount.  Without these filters, the formula would return duplicates of the amount for each currency in the Exchange Rates table.  Since these filters are critical to the formula, the options to remove/disable these filters have been removed automatically.
• UPDATE Sep 10, 2016 - This plugin was updated to support Sisense version 6.4
• UPDATE Oct 3, 2016 - This plugin was updated to allow enabling/disabling of the plugin on a per-dashboard basis