SQL Centre of Excellence

 

Currency conversion can be a PITA. As you may know there are three standard requirements that pop up:

  1. One to Many. Where the database is in one currency and it needs to be reported in many currencies.
  2. Many to One. Where different rows are in different currencies and need to be converted into a standard currency.
  3. Many to Many currency. Different rows are in different currencies and need to be reported in many currencies.

This is slightly difficult in PowerPivot due to the lack of “Many to Many” support” and also no support for “default members” to have a default currency.

In this example we have the following tables (example PowerPivot Workbook attached). One initial solution is to use a “calculated member in the PowerPivot Window to convert to a standard currency (EUR) and then apply a one to many relationship for the reporting currency. For a lot of customers that won;t work as this results in a double conversion and therefore errors in the maths.

image Fact Table

.image

ExchangeRate Table

My solution is not elegant. In fact, maybe it will appear in the sqlcat site of “worst practices”, but it works like a dream so far…

The Solution
1. First add a sheet for your FromCurrency and your ToCurrency. Its quiet common that your source currency may be in 10-20 currencies but you only want 2-3 reporting currencies

2. Decide on your “default” currency. in my case EUR

3. do NOT add a relationship between the Facts and the “To Currency”, when a To currency is selected we need to use a calculated measure to do the conversion dynamically as many to many relationships are not supported by default in PowerPivot.

4. Add a slicer for the “To Currency”

5. Add a calculated measure for the Amount.

=if(countrows(ToCurrency)=1,sumx(FactTrans,sumx(filter(ExchangeRates,ExchangeRates[FromCurrency]=FactTrans[Currency]),FactTrans[Amount]/ ExchangeRates[MidRate])),sumx(FactTrans,sumx(filter(filter(ExchangeRates,ExchangeRates[FromCurrency]=FactTrans[Currency]),ExchangeRates[ToCurrency]="EUR"),FactTrans[Amount] / ExchangeRates[MidRate])))

 

How does it Work?

The trick is the “sumx” function effectively iterates through every row, finding the related conversion rate and applying the conversion and summing the results. The IF statement detects of we have not selected only one currency and if so, applies a default one.

How does it compare to Analysis Services Currency Conversion?

The formula looks  lot more complex than the SSAS MDX equivalent technique, and SSAS has the benefit of also being able to do formatting with “Locales” to change the currency symbols dynamically too. But its quick and easy-ish and it works…

image

Currency_Conversion_Challenge_2.0.xlsx (274.78 kb)

Were Back baby

by Bob Duffy 30. June 2010 23:30

Ok, bummer. My old blog site at http://blogs.msdn.com/b/boduff/ is no more now that I am no longer a Microsoft badge holder and *.microsoft.com email owner. Minor sore point in that Carmel is now a MS badge holder so has to sign me in and hold my hand when we into MS for meetings.

Thanks to Paul Randal and local sql folk for giving me lots of hints to sort a blog out. Kudos as well to Danny, for the hard work in setting up the blog engine, server, etc etc

Down to business. I have been lucky enough to have been engaged with Microsoft Consulting Services on some really nice PowerPivot Projects.  It is soooo nice to cut me teeth on some live projects after spending like nearly a year touring talking about it and messing around.

So I am going to blog about some of the challenges and fun we have had over the next few weeks. Of particular interest is solving common business problems like weighted averages, dynamic many to many currency conversion and solving the architectural question of should I use PowerPivot or should I use Traditional Analysis Services.

Page List

Page List