Currency Conversion Data Connector

Introduction:

This article will explain how to make a REST API connection to Rates a public currency conversion API

Purpose/Benefits:

Currency conversion may not be available in your data.

With this connector you can convert currency on-the-fly in the ElastiCube.

How to Install and Configure the RateAPI Connector:

Step 1:

Download the connector and unzip the contents into your C:\Program Files\Sisense\DataConnectors\DotNetContainer\Connectors folder.

*You may need to move your Rest dll file into the connector (replace the current one) - you can do this by going to C:\Program Files\Sisense\DataConnectors\DotNetContainer\Rest and copying the .dll to the RatesAPI Connector

Step 2:

Restart the Sisense.Discovery and Sisense.CLRConnectorsContainer services on the Sisense machine

Step 3:

The default currency base (from-currency) is USD, if you would like to convert from another currency edit the config.json file:

Step 4:

Open the ElastiCube Manager, create a new ElastiCube and select the Rates Connector. You will see a table that include the conversions...

*Data modeling might be required to take full advantage of the currency conversion

You can use a cross join, leveraging this file

Bring that file into the ElastiCube along with your Rates table from the API. You can then use this SQL statement to pivot:

SELECT crjn.column AS "Conversion",
 CASE 
 WHEN crjn.val=1 THEN r.rates_AUD
 WHEN crjn.val=2 THEN r.rates_BGN
 WHEN crjn.val=3 THEN r.rates_BRL
 WHEN crjn.val=4 THEN r.rates_CAD
 WHEN crjn.val=5 THEN r.rates_CHF
 WHEN crjn.val=6 THEN r.rates_CNY
 WHEN crjn.val=7 THEN r.rates_CZK
 WHEN crjn.val=8 THEN r.rates_DKK
 WHEN crjn.val=9 THEN r.rates_EUR
 WHEN crjn.val=10 THEN r.rates_GBP
 WHEN crjn.val=11 THEN r.rates_HKD
 WHEN crjn.val=12 THEN r.rates_HRK
 WHEN crjn.val=13 THEN r.rates_HUF
 WHEN crjn.val=14 THEN r.rates_IDR
 WHEN crjn.val=15 THEN r.rates_ILS
 WHEN crjn.val=16 THEN r.rates_INR
 WHEN crjn.val=17 THEN r.rates_ISK
 WHEN crjn.val=18 THEN r.rates_JPY
 WHEN crjn.val=19 THEN r.rates_KRW
 WHEN crjn.val=20 THEN r.rates_MXN
 WHEN crjn.val=21 THEN r.rates_MYR
 WHEN crjn.val=22 THEN r.rates_NOK
 WHEN crjn.val=23 THEN r.rates_NZD
 WHEN crjn.val=24 THEN r.rates_PHP
 WHEN crjn.val=25 THEN r.rates_PLN
 WHEN crjn.val=26 THEN r.rates_RON
 WHEN crjn.val=27 THEN r.rates_RUB
 WHEN crjn.val=28 THEN r.rates_SEK
 WHEN crjn.val=29 THEN r.rates_SGD
 WHEN crjn.val=30 THEN r.rates_THB
 WHEN crjn.val=31 THEN r.rates_TRY
 WHEN crjn.val=32 THEN r.rates_ZAR
 END AS Rate
FROM [Rates] r
CROSS JOIN [CR_JN] crjn