Recently working upon a project I was faced with a challenge to provide a multi-currency analysis of the sales data. Though a straight forward task for SSAS experts but with a little sneaky trick, which I would like to high light in this post to make your lives easier when faced similar situation:
(For the explanations I will be using the AdventureWorks2012DW for illustration, and I have set all the dimension types representing their role like time, currency and similar)
So the overall data source view of the system is as follows:
And the dimension usage (Only Regular) is as follows:
Now the fun part when you try to establish the Multi-currency dimension usage so that you’re computed measure “Sales Amount_CUR”
So again going back to dimension usage, and when you try to link up the “InternetSales” measure group with “CurrencyRate” measure group with Many-To-Many relationship !!!….
You never get beyond this…
The main cause being for the above is that is that you “InternetSales” is trying to marry with “CurrencyRate” only on the basis of “CurrencyKey” but what about the dates ??
And that is what is critical in the above problem where the “Date” dimension of exchange rate has to be mapped with a concerned key in the “Sales” so when the “InterSales” start looking in to “CurrencyRate” rate measure group, it can figure out that for my order on certain date had the corresponding exchange rate !!!
And thus a small relationship can drive bigger complex relation’s !!!
So continuing… We go ahead and establish the “Date” relation with the “OrderDateKey” in the “InternetSales” and move over to Many-To-Many relations to DestinationCurrency and Voila !!
We have out intended and results are pretty much we expect…
Hope it helps…