I wrote about that some weeks ago (http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16195.aspx). Using a OLE DB command transform to find out a matching record instead of a lookup transform when you don’t have an exact match but i.e. a range to compare is very slow. Using a cross join might be a solution (I still didn’t have time to test it…), but produces huge datasets to load into memory. So no perfect solution…
Some days ago my colleague Markus was in the BI Migration Lab in Redmond. It was a great event and we got some very valuable input there. Runying Mao came up with a very neat solution for this issue…
Back to the example of the employee<->department relationship. You have a fact table with employee IDs and another table having the employee ID and the department the employee is working in. Since employees can change their department you have a start and end date for this employee<->department assignment. Now you want to know for each fact record in which department the employee was at the given time.
Runying’s solution is to do an inner join between the fact and the relationship table. If you have an employee who was in different departments you get the same fact record for each department the employee ever was. So the only thing you have to do is filter out (by a conditional split) the “non fitting” records, what means the records where the date from the facts doesn’t fit into the data range coming from the relationship table.
Cool solution… Perhaps this is a good scenario for my next snippet…
Thomas
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