and Im aware im going to need a currency conversion dimension.
Assuming I need a pivot Table with 8 currency calculations over millions of rows and a list box to select View currency as per John Witherspoons posted testcurrency example (not sure how to link to it here).
How can I avoid doing a conversioncalculation when the SourceCurrency is the Same as the DestinationCurrency.?
We have already found that loads of if statements in the pivot calcs adversly affects perfromance
We do that by building a cross rates table where we have each currency converted to each currency, obviously in this table when you have From GBP to GBP the FX Rate is always going to be 1.00 so then you don't have to worry about when you do or don't do the conversion, it's always done.
Have not done it, but should be achievable by loading conversion of currencies into e.g. USD. Then you need to select the "From" and the "To" currency as variables and do two conversions: amount into USD and then into the other currency.
I am guessing that your only choice then is going to be to execute the conversion in the load script.
I understand that you don't want to calculate where it's not necessary, however, an expression such as Sum(SalesValue * FXRate) will work much faster overall than something like IF(Currency=USD,Sum(SalesValue),Sum(SalesValue*FXRate)), so for me it would still make sense to create the cross table and implement the calculation on every field.
If however, you can work out how to achieve this in the load script then all the better, but I suspect you will then need to be creating fields such as SalesValueUSD, SalesValueGBP, SalesValueEUR etc. If you do this then you will probably have copies of charts and tables, each different chart/table would be set up to show the correct currency as selected using perhaps a button. If you can work this out then it would be the best in terms of end-user performance, but I don't know what impact that would have on the load time, you'll need to benchmark it.