Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008) BETWEEN dim.date1 and dim.date2
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 08/22/2013 :  04:07:25  Show Profile  Reply with Quote

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 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.


Starting Member

2 Posts

Posted - 08/22/2013 :  10:36:25  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000