Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


fact.date BETWEEN dimension.date1 and dimension.date2


fact.date BETWEEN dimension.date1 and dimension.date2

Author
Message
michielbijnen
michielbijnen
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 145
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!
Ells
Ells
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 Visits: 931
Need far more info.

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

Cheers
E
michielbijnen
michielbijnen
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 145
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!
Ells
Ells
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 Visits: 931
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
Paul Williams
Paul Williams
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 864
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search