Need help to have a join in between condition of Date dimeniosn and fact table in ssas

• venkateshkoka

  SSC Rookie

  Points: 30

  More actions

  December 13, 2014 at 1:05 am

  #150257

  Hello Team

  Need a help in resolving issue in ssas cube related to join in between condition dimension and fact table. Below is the dimension and fact table structure with sample data.

  While having a relation in data source view of date dimension with fact table, Date from Dimension table should check in between Start_Dt and End_Dt in fact table and should give the result. In Fact table we dont have single date attribute to join, we have date range with start_Dt and End_Dt.

  for example: If a user want to see the the volume of sales for the date 2011-02-01 and productkey 10, the date should check in between the fact table start_Dt and End_Dt and get 300

  How to build a relationship for dimension and fact table with in between condition in data source view

  Dimension table Date

  DateKey Date Month Semister

  20110101 2011-01-01 Jan H1

  20110201 2011-02-01 Jan H1

  20110301 2011-03-01 Jan H1

  20110401 2011-04-01 Jan H1

  20110501 2011-05-01 Jan H1

  20110601 2011-06-01 Jan H1

  Fact Table Inv

  Start_Dt End_Dt ProductKey volume

  2010-01-01 2015-12-31 10 300

  2010-01-01 2013-12-31 09 599

  2008-01-01 2019-12-31 01 1000

  2009-01-01 2019-12-31 01 1000