PLEASE HELP - Database Date Functions VS Hard Coded Dates

building semantic layer
1

Hi,

I am using Webi XIr2 on top of Sybase OpenClient v12.5.

When I execute a query with fixed date values, or select the dates from the BO Calendar, the results are returned within 1 minute.

However, I need a rolling date range in order to schedule the report for nighly execution.

When I substitute the hard coded values for date objects defined in the Universe it takes more than an hour to run.
eg - getdate() and dateadd(dd, 21, getdate())

I have tried to define these date variables using a derived table with no improvement.

I have even tried to create a CASE statement to substitute a calculated date when a string value is selected in a prompt - this has not improved things greatly either.
eg.
CASE @Prompt(‘1. Select Start Date:’,‘A’,{‘Minus Four Weeks’,‘Current Date’,‘One Day’,‘Two Days’,‘One Week’,‘Two Weeks’,‘Three Weeks’,‘One Month’},mono,free)
WHEN ‘Current Date’ THEN convert(date,convert(varchar(10), getdate(), 101))
WHEN ‘One Day’ THEN convert(date,convert(varchar(10), dateadd(dd, 1, getdate()), 101))
WHEN ‘Two Days’ THEN convert(date,convert(varchar(10), dateadd(dd, 2, getdate()), 101))
WHEN ‘One Week’ THEN convert(date,convert(varchar(10), dateadd(wk, 1, getdate()), 101))
WHEN ‘Two Weeks’ THEN convert(date,convert(varchar(10), dateadd(wk, 2, getdate()), 101))
WHEN ‘Three Weeks’ THEN convert(date,convert(varchar(10), dateadd(wk, 3, getdate()), 101))
WHEN ‘One Month’ THEN convert(date,convert(varchar(10), dateadd(mm, 1, getdate()), 101))
END

The DBA has told me that the date functions (eg getdate() ) cannot be used in the ‘where’ clause of a query as Sybase cannot use the indexes.

If the query is written in Sybase SQL it is possible to declare variables, and then use the variables in the ‘where’ clause. However, I do not think this is possible in BO.

I have used the date functions in Oracle in the past without any problem.

Can anyone suggest a solution to this problem?

Your help is much appreciated.

Many thanks, John.

John Warne Smith (BOB member since 2005-04-18)

2

John,

You have my sympathy; we suffer very similar problems running Business Objects on top of Teradata. i.e. As soon as we start using dynamic date range Conditions using date functions, dynamic partition elimination is rendered useless and performance takes a massive hit.

One solution is to use the data returned by one query in the WHERE conditions of another. So, you create a very simple query using your date functions that just returns the required date range, and then use this as the input to the WHERE condition in your main query(ies). I have achieved up to 90% improvement in run times by doing this.

However, this solution may not appropriate for you as it only works in Deski, not Webi (and no, it won’t be available in XiR3). Yet another reason to retain the full client product!

anorak :uk: (BOB member since 2002-09-13)

3

Hi Anorak,

Thanks very much for your reply.

We are only licensed for WebI which is a shame, otherwise I could have tried your workaround.

I had asked the DBA to create a calendar table to provide the necessary date ranges and link this to the relevant tables, but he was not in favour of this.

As there appears to be no other alternative, I may try to change his mind.

If this does not happen, it will not be possible to schedule the report with a rolling date range and the users will have to enter the dates from the calendar on an ad-hoc basis.

Thanks again, John.

John Warne Smith (BOB member since 2005-04-18)