Posted - 08/22/2013 : 10:36:25
| I will try to explain my topic 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.