KPI Partners Blog

Evaluate With Date Functions in OBIEE

Posted by KPI Partners News Team on Fri, May 24, 2024 @ 01:56 AM

by Kurt Wolff

If you’re not concerned about database portability – for example, you use Oracle and that’s that, forever – then the evaluate function in OBIEE can be useful. However, using the evaluate function can be tricky, and the documentation could be better.

Here’s an example of using Evaluate with the Oracle next_day function, which returns the date of the next specified day of the week following any given date. This function is useful for calculating the week ending date for any given date. If your standard week ends on Saturday, then the expression you would use would be next_day(<date>, ‘saturday’).  This is quite simple, but how do you use it within OBIEE?

Here’s a simple subject area in OBIEE which I used to test how to use next_day( ). It contains two logical tables: a Days table and a Facts table. The facts table shows how many calls occurred on any day.

Function   Picture1

 

 

 

 

 

As these query results show, there is one call per day.

Function   Picture2

 

 

 

 

 

 

 

The objective was to sum up the number of calls for each Saturday-ending week using the Oracle next_day function.

When you use the evaluate function in OBIEE, the expression builder presents you with this template:

Function   Picture3

 

 

 

Following the template, you would write this:

Function   Picture4

 

However, this syntax produced a “Union of non-compatible types” error.

Function   Picture5

 

 

 

 

 

I do not know what this error is really saying. Does the column name have to be enclosed in single quotes to make it a string?

Function   Picture6

 

 

The administration tool accepts this, but you get a SQL error when you use it in a query.

ORA-01858: a non-numeric character was found where a numeric was expected

The reason is obvious when you look at the SQL generated:

select next_day('"XE".""."XE"."DAYS"."DATE1"','saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day('"XE".""."XE"."DAYS"."DATE1"','saturday')
order by C1

Instead of providing a column identifier that had a date data type as an argument in the next_day function, the SQL contained a string value.

My next thought when I encountered this was to include the column name as part of the first “str_Expr”.

describe the image

However, this produced another Oracle error:

ORA-01741: illegal zero-length identifier

So to fix this I edited the column identifier.

describe the image

 

The administration tool accepted this, but it produced a different SQL error at run time:

ORA-00904: "DAYS"."DATE1": invalid identifier

The reason this is an invalid identifier is that the SQL OBIEE generated included a table alias for the DAYS table, T3392.

select next_day("DAYS"."DATE1",'saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day("DAYS"."DATE1",'saturday')
order by C1

To fix this, I deleted the DAYS identifier in the function. This might work, but not if you have a DATE1 column defined in more than one table in the FROM clause. This data contained a DATE1 column both in the DAYS table and DAYSCALLS table. Therefore, the query produced another Oracle error.

ORA-00918: column ambiguously defined

To fix this, a table identifier was needed. Since OBIEE used T3386, I included this in the evaluate function.

describe the image

 

The administration tool accepted this, and OBIEE generated correct SQL that Oracle accepted.

select next_day("T3386"."DATE1",'saturday') as C1
,sum(T3392.CALLS) as C2
from DAYS T3386
,DAYSCALLS T3392
where (T3386.id = T3392.id)
group by next_day("T3386"."DATE1",'saturday')
order by C1

However, the results in Answers were not correct.

describe the image

 

 

 

 

 

 

 

Notice that instead of getting a week ending date, we see something that looks like a month, with the sum by week presented as separate rows within the month. Even though the function next_day returns a date, OBIEE treated it as text.

describe the image

 

 

 

To fix this, I added AS DATE in the formula to declare that the returned value was a date.

Function   Picture12

 

 

 

 

After I refreshed metadata on the presentation server, the query now produced the desired results.

describe the image

 

 

 

 

 

 

 

Looking at column properties, everything seemed correct. The column was being treated as a date.

describe the image

 

 

 

But look what happened – see the X axis -- when I graphed this.  The X-axis showed question marks instead of week ending dates.

Function   Picture14

 

 

 

 

 

After adding the graph, I examined the column properties.

Function   Picture15

 

 

 

 

The column had reverted to text!

To get OBIEE to recognize the results of the evaluate function as a date, one solution that seemed to work was to use the Oracle to_date function along with the next_day function.

describe the image

 

Now the graph showed week ending dates on the x-axis.

describe the image

 

 

 

 

 

 

 

 

 

Tags: Kurt Wolff, Oracle BI, Blog



Subscribe to the KPI Blog