In this post I would like to speak about currency rates calculations in standard dimension model, and in the next post I would return to Tabular model, DAX language and perform the same calculations there.
It’s quite a common business task when you have to provide business with prices and sums in different currencies. The task is quite actual for business intelligence systems based on SSAS technology.
You can create currency conversion model using Business Intelligence Wizard or manually. This exercise is described in details in Christian Wade blog: http://consultingblogs.emc.com/christianwade/archive/2006/08/24/currency-conversion-in-analysis-services-2005.aspx. But following this guide you would face the problem: sum of values on day level would not be equal on month/year level as for these levels last currency rate is using.
For example:
- Converted price for 2013-01-01 is 90.9,
- Converted price for 2013-01-02 is 1666.7.
- The sum is 90.9 + 1666.7 = 1757.6.
But pivot shows 1750 as a result of conversion if original price using last rate 1.2. Let’s make the situation more complex and for each position in fact table create three different dates. And the task is to calculate the same sum for each date based on the currency rate for that date. The fact table would contain data in different currencies and we have to calculate all sums in USD.
First of all we have to create a view, based on the fact table, in which all rates and sums must be calculated. The text of the view can be the following:
In this case the Cube structure will look like:
Forecast basis – is a dimension to analyze facts. Also, we do not need Currency rate table in cube structure as we have performed all needed calculations in the view.
If we have only one date dimension, the task will be already solved, but in our case we have just started))). If we create one Measure group, users would have to select new price when they want select other date. It they forget to change measure – the calculation would be incorrect.
This is rather not user friendly and contains high risks of wrong data analysis. On the other hand, we cannot create new measure group based on the same fact table getting the following blocking message:
But what is blocked in designer can be done in XML. This is rather flexible tool and you can (of course with some cautions) create required measure groups. I’ve duplicated code of the existent measure group three more time, changed its GUID and name, and then cut price measures from original measure group to new groups. Then I come back to designer and see the following structure:
Now let’s go to dimension usage tab and set relationships to get the following structure:
As you can see, I link each measure group only with date that determines its currency rate. Also, to prevent including members of measure groups to show top level values for the unrelated dimensions, it’s required to set IgnoreUnrelatredDimensions property to False.
As you can see, I link each measure group only with date that determines its currency rate. Also, to prevent including members of measure groups to show top level values for the unrelated dimensions, it’s required to set IgnoreUnrelatredDimensions property to False.
The same calculation must be created for Rate measure.
And that is – at last we’ve got the required result:
