I was conducting training recently where the participants asked for a solution on how to convert data from many different currencies into a target currency using an exchange rate table. For those building enterprise cubes this might be a familiar problem since consolidations of amounts across reports is usually a challenge. I remember seeing an example of this implementation in Adventure works database but figured I should do a cut down version focusing just on the above topic. So here is how I did it.
The first step was to create the required tables so here is a screenshot of my main tables and the data in them.
The first table is a date table to capture exchange rates for different days.
The next table is a forex table which contains rates for different weeks, you can easily extrapolate this for a daily rate.
The next table I needed to create was a join between the Forex table and the date table to get rates for each date. Ideally here I am creating a list of all possible rates for all possible dates. If you look closely at the data you will notice that I am converting between USD to GBP < INR < FRF and EUR and vice versa. Naturally you need to keep in mind the conversions you require and create the table accordingly. If the source and target currencies are the same then you will need to add a row for USD to USD conversion with a Exchange rate of 1 ( I haven’t included it in this example).
The last table I have is my sales table where the data is currently stored.
Now I move on to my cube Datasource view.
In my cube I have created appropriate dimensions and measures. And an calculated measure called conversion which is amount* value.
With the dimensions and measure created here is what my Mapping looks like
Notice that date and currency dimensions access the sales fact table via a factless fact table called Measuretable.
Deploy and process the cube and the result will look as below. Notice the parameter for Target Currency shows as INR. Essentially picking up only the exchange rates where the destination currency is INR , the source could be any Currency whose amount is then multiplied by the target exchange rate to give the converted value.
And that’s all it takes to convert currencies across different countries. Hope you found the blog useful , please feel free to comment if you have any queries.