Dynamic date ranges

[pimogo]

What techniques are you guys using to generate dynamic date ranges. The techniques i've been using have impacted performance substantially against a DB2 db using native drivers.

I've been using the native cognos functions:

Code Select Expand

[Namespace].[Calendar].[Start Date] between _add_days (current_date, -( _day_of_week( current_date,7 )+ 5 )) and _add_days (current_date, -( _day_of_week( current_date,7 )-1))

[cognostechie]

I have used the same functions in Relative time categories and it has not impacted the performance. You might want to check
the join between Calendar and Fact tables and the indexing on those.

Something worth noting -

As an example - I have Relative Time Categories in FM for YTD and Prior YTD. So when I make a report using these in columns (not as a filter in the Query), I would also set a filter on the Query itself to restrict the data for current year and last year only because there is no need to scan the fact table for more data than that. That would filter the query itself for two years and then pull the data in the columns for YTD and Prior YTD. That is better for performance.

You expression is to retrieve data for only last few days so try to set an additional filter on the query using extract function to get data only for the last 30 days.

[Namespace].[Calendar].[Start Date] > _add_days(current_date,-30)

Try also using 'extract' function with 'Month' and 'Year' instead of _add_days. That's the one I use.   

[pimogo]

Thanks for the info, Cognostechie.. that makes a lot of sense. ANother thing i just remembered is that currently dates are in the same table as facts. Poor design i know, but we're working from what we have. :(

[cognostechie]

In that case, why not filter the Fact table itself (instead of fltering the Calendar). That would reduce one join between Calendar and Fact.