SSAS: News about Currency Conversion
OK, I really don’t know why, but my statements about currency conversion (http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16196.aspx) weren't 100% correct… Or to be honest, some were 100% scrap…
Mosha told me that calculated measures based on measures affected by currency conversion scripts should work perfectly, I didn’t believe that. I have screenshots for that in case you think that I have to be wrong… However after a new deployment of the Analysis Services database all the measures work perfectly now… So Mosha, you (certainly) we right and there was just something screwed up in my cube…
Another thing I was complaining about is the performance of the currency conversion scripts. I did quite a lot of tests in the last weeks and I’m not finished, yet. We saw these performance problems mainly when we use the cube in Reporting Services (SSRS). So I tested the cube in the Server Management Studio (SSMS) and did the same queries we do with SSRS and they performed very well. So where’s the difference between SSRS and SSMS? When you filter a cube in SSMS you use subcubes by default. When you do the same in SSRS you use “standard” filters. So I changed my queries in SSMS to use filters and – poor performance. I investigated a little bit to find out if you can change SSRS`s behaviour to use subcubes instead of filters but you can’t…
Another approach would be to test exchanging the currency conversion scripts to simple measure expressions… Mosha told me that there might be quite some performance improvement… I didn’t have time to test it (OK, I tried by my Analysis Server started throwing memory errors when I changed my measures) so I can’t confirm that…
Watch out for further updates, I hope that there will be further improvements in upcoming releases (damned, when will the next CTP arrive?!?!)…
Thomas