Imagine your company or client has business units in different countries (maybe you don't even imagine that as you're huge Rollerball-like corporate which rules the World!). And those countries can have also different currency.
Your goal is to have unified reporting for you whole company. At first you're going to convert monetary values from all the countries to a single currency - that's an easy step within the transformation when you have table with exchange rates for specific currencies and dates (or other periods of time).
As your BI solution is spreading through your company and everyone is enjoying your nifty charts and dashboards, some of the users from your foreign business units are starting to complain that they can't see the results in their local currency (like complaining about re-calculating from EUR to Puerto Rican peso).
You have an option to duplicate every single monetary column with values converted to desired currencies, and let the users choose in which currency they want to see values in thier report. But what if you have 30 monetary facts and 10 different currencies? That would be burden not just to the metric management, but to the data size as well.
Your second, and according to my opinion event the best, solution is to create new semi-disconnected dataset containing the exchange rates. I've used the word "semi" because this new dataset will be sharing the date dimension with your dataset containing facts.
Let's go step by step:
- You need to have a table with columns date, currency and exchange rate (in the table blow you see four different currencies). Maybe you want to add sorting column for currencies to determine their priority within others.
In GD Writer, set currency
as Attribute, exchange rate
as Fact, and to date
assign the same date dimension
used by your fact table. At least part of your LDM should look like the one on the next image.
When you have your new Exchange Rate
dataset in GD, it's time to create a metric for final calculation. You need to start with two inner metrics
which will calculate their values for a single date (because every day you can have a different exchange rate). The inner metric for Exchnage Rate needs to have BY ALL OTHER statement, which enables to slice it by the attributes from other datasets. Both metrics are then packed in another SUM (it's an outter metric). Why's that? When you're calculating i.e. Value for one month, at first the metric will make the inner calculation for every date, then it will sum it up by the time period you've selected (month in our example). If you wouldn't put an outter metric there, the same example would end in multiplying SUM of Value of Month and SUM of Exchange rates of Month which wouldn't reflect the Values and Rates on a daily basis.
Now just place Date filter
and Currency filter
on your dashboard. Thanks to the structer of your new dataset and your metric, values are responding to both date and currency selection. For example, if you have different rates for every day within the month, the monthly metric is sum of the daily multiplication results within that month.
Kaboom! No one can now accuse you from discriminating less known currencies in your reports...