Friday, July 24, 2009

OBIEE LOGLEVEL session variable problem

If you read my post External table authentication and row-wise initialization in OBIEE , in Part1 - External table authentication I'm using external table for authentication of the user, group, displayname and loglevel.

I'm using b_ext_tab_auth_all block:

select username, groupname, displayname, loglevel
from HR.OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'

The table OBIEE_EXT_TAB_AUTH has loglevel column number datatype in the database and when you import it to a repository it has double datatype:



Logon with some user (UserC, which has loglevel 5 in the database) and make a simple request and see NQQuery.log.

You'll see that the log in the NQQuery.log does not exist.

Try to put first in the column in the report VALUEOF(NQ_SESSION.LOGLEVEL) to see what is populated:



Ok, this is because decimal is populated and LOGLEVEL need to have integer as input to work properly.

Solution:

Leave datatype of imported table to DOUBLE and do a explicit TO_NUMBER(loglevel) conversion:

select username, groupname, displayname, to_number(loglevel)
from HR.OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'

Now it works and you'll get log in the NQQuery.log.

*Note
If you just change datatype of imported table from DOUBLE to INT and leave block without change:

select username, groupname, displayname, loglevel
from HR.OBIEE_EXT_TAB_AUTH
where username=':USER'
and password=':PASSWORD'

it does not work, you can't see logs in NQQuery.log.

If you are using character for LOGLEVEL column in the database then you need to put TO_NUMBER(loglevel) also.

3 comments:

gerardnico said...

Hi Goran,

I assume that you use Oracle as database.

If you set your loglevel column with a NUMBER(1) in your table HR.OBIEE_EXT_TAB_AUTH, you should have an INT in OBIEE.

Cheers
Nico

Goran Ocko said...

Very interesting. If we change DOUBLE to INT in the RPD and leave NUMBER in the database it doesn't work without to_number() in the block. If we change NUMBER to NUMBER(1) in the database then this works without to_number().

Thanks

Anonymous said...

hi,
Good one! I found some more details on
http://unleashobiee.wordpress.com
bit more information on the obi log levels.
- Mark