December 13, 2014 at 1:05 am
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply