I will try to explain and will only stick to the relevant tables and fields.

In my DSV fact-table I have got a transaction date, itemcode and a sales amount.

In my DSV dimension-table I have got an itemcode, employeeno, startdate and enddate.

This table shows the sales-items where the employee is responsible for but ONLY between the start and enddate.

So as an example in the dimension-table:

Item A has employeeno 273 between 2012-01-01 (startdate) and 2012-09-30 (enddate)

Item A has employeeno 102 between 2012-10-01 (startdate) and null (enddate)

Item B has employeeno 365 between 2013-03-15 (startdate) and null (enddate)

Item C has employeeno 273 between 2011-01-12 (startdate) and 2012-11-30 (enddate)

This is a slowly changing dimension, but the table does not have overlapping date ranges per item so there is only one valid item A on a certain date. So only one valid employeeno for an item.

When I have, let's say 2012-08-28 as transaction date and A as the item in my fact-table, it should be linked to the right employee depending on the itemcode in the fact-table. In this example employee 273 because 2012-08-28 is between 2012-01-01 and 2012-09-30 and the itemcode is A.

So in my opinion the transaction date in the fact-table has to be linked to the dim-Itemtable in a way that it evaluates fact.transactiondate >= dim.startdate and fact.transactiondate <= dim.enddate (apart from the second link fact.item = dim.item, but that no big deal). As far as I can see I can only link fact-table and dim-table based on equality, not based on BETWEEN.

BTW: The fact-table doesn't have the employeeno stored.

Of course I can add the employeeno to the fact-table when populating the DWH, but I wonder if there is a solution to this in SSAS.

I hope this makes it a bit more clear.

Thanks again!