Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

fact.date BETWEEN dimension.date1 and dimension.date2 Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 10:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 12:50 PM
Points: 42, Visits: 130
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!
Post #1486794
Posted Thursday, August 22, 2013 3:38 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:32 AM
Points: 705, Visits: 772
Need far more info.

Show us roughly what the tables look like
Show us what (and why) you are trying to achieve

Cheers
E
Post #1487140
Posted Thursday, August 22, 2013 8:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 12:50 PM
Points: 42, Visits: 130
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!
Post #1487305
Posted Thursday, August 22, 2013 9:10 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 4:32 AM
Points: 705, Visits: 772
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
Post #1487340
Posted Monday, September 9, 2013 2:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:40 AM
Points: 541, Visits: 722
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.
Post #1492682
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse