August 21, 2013 at 10:13 am
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!
August 22, 2013 at 3:38 am
Need far more info.
Show us roughly what the tables look like
Show us what (and why) you are trying to achieve
Cheers
E
August 22, 2013 at 8:33 am
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!
August 22, 2013 at 9:10 am
Ok,
I get it. I have done a very similar situation. The easiest solution is to include the employee id on the fact table.
DSV's require a straight forward join so there is no easy way there. The alternatives are to create another table to sit imbetween with each item, each day and the employee id. Which would then provide a straight forward join. There are alternatives but would suggest the best way is to put the employee id on the fact table in the warehouse.
E
September 9, 2013 at 2:04 am
You need to go back and think about your design. What you are trying to achieve from a Modelling point of view is correct but not the way you are trying to achieve it.
In your Dimension you should have a surrogate key.
When you join your fact table to your Dimension lookup the Surrogate key based upon the Employee and date that exist in the fact table that match against the Employee and date in the Dimension. Your Dimension correctly does not have overlapping dates as you are showing Type2 history. Therefore your fact record will along with the employee no., match only against one record. The surrogate key from the Dimension is then added to your Fact table. You now have a FK-PK relationship between the Fact table and Dimension. This should be done in the DW.
Paul R Williams.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply