I have a Fact surrounded by many dimensions out of which calendar is one and the Fact along with joining to other dimensions does joins to this calendar dimension as well to fetch data for a particlualr time period. The data in the fact is maintained at the grain of Week. I mean all the records for the same week would have the same Ship_Date which would be equal to the Weekend date of that week.
The Fact is partitioned on the Ship_Date
The Fact and dimensions are exposed to the user through a BO universe so users have the independence to fetch data for a week or multiple weeks.
Now the situation is that when user selects a single Weekend Date from the Calendar dimension then the date filter is passed to the fact through DPE and i can get partitions eliminated but the moment the user tries to get the data for say current week and the week an year ago then BO creates a derived table to get those two dates and those dates are not getting pushed to the Fact table for DPE.
Also the below condition does uses DPE which i was just trying to analyse.
CALENDAR.Cal_Dt IN ( Select Cal_Dt FROM STARS_CALENDAR WHERE Cal_Dt = '2012-03-03' )
but the below does not
CALENDAR.Cal_Dt IN ( Select Cal_Dt FROM STARS_CALENDAR WHERE Cal_Dt
in ( '2012-03-03' , '2012-03-10' )
Would appreciate any comments as to how can I make the optimizer to use DPE in fact for any no of dates passed through BO.