Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
michielbijnen
Starting Member
2 Posts |
 Posted - 2013-08-22 : 04:07:25
|
| Hi,I am relatively new in Analysis Services and building my first cube from a DWH.I have a fact table in my DSV with a date column. I need to create a relationship to a dimension table in the DSV that has two dates. The relationship between the two tables is not based on equality but on fact.date between dimension.date1 and dimension.date2. Is it possible to do this in the DSV? Or is there another trick in SSAS to accomplish this?I use 2008 R2.Hope someone can help me.Thanks! |
|
|
michielbijnen
Starting Member
2 Posts |
Posted - 2013-08-22 : 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. |
 |
|
|
|
|
|
fact.date BETWEEN dim.date1 and dim.date2