Wednesday, October 1, 2008

OBIEE the "developersprompt"

When I'm developing a dashboard I often want to play around with some basic settings like cache hit & seed and loglevel. Normally you have go back to answers or your administration panel to change these settings (temporarily). I always advice my developers to make a 'developersprompt'......It's a simple LOV prompt which returns me 3 presentation variables (dp_cache{0,1}, dp_seed{0,1} and dp_log{0,1,2}.




In the prefix of each report we default add:


(you can find the postfix block in the advanced tab of your report)

When developing your dashboard you can playaround with different settings to find the optimum. Leave the prompt on the dashboard and make in only 'available' for administrators or developers, so that in time of "trouble" your are quickly able to bypass the cache or turn on logging.

Till Next Time

13 comments:

S said...

Hi,
Would you please tell me how to get it in more details, I'm not able to get where to add the code to clear the cache or logs

John Minkjan said...

Hi,

You can find the postfix block in the advanced tab of your report. (You might have to scroll down a bit.)

Kumar said...

Hi John,
When i use the below syntax
SET VARIABLE LOGLEVEL = @{dp_log}{0,1,2};
in Advanced tab of a report it is giving this error.

[nQSError: 10058] A general error has occurred. [nQSError: 27002] Near <1>: Syntax error [nQSError: 26012]
Also i have a doubt....the LOV's will be displayed in the dashboard prompt, but we are using this syntax in the report, how the dashboard prompt will get these values....

John Minkjan said...

Hi Kumar,

You can only define 1 default value for the variable =>
SET VARIABLE LOGLEVEL = @{dp_log}{0};
Your dashboard prompts gets the values from your LOV table.

regards

John

Kumar said...

hi john,
So you mean to say, we need to create a table with column having values like 0,1,2,3.....and then use this table in the dashboard prompt.
Can you be bit more brief on how the variables defined in the report be used with the dashboard prompt.

John Minkjan said...

Hi Kumar,

In all my repository I have a LOV table (a small XLS) with values like YES/NO, RED/GREEN/ORANGE, 1..10. That why I don't have to use a seperated dimension when making a prompt which doesn't have a direct connection to the table used in the report of the dashboard. The result of this prompt I then put in a presentation variable.

Regards
John

Kumar said...

Hi John,

Now I got your point. But I feel that you should have added the point about the custom LOV table(with the values 1...10, YES/NO etc) need to be used to achieve this, in your explanation.

Regards,
Kumar

Andreas Nobbmann said...

Hi John,

thanks for your post, this is extremely helpful.
Just found it a couple of minutes ago and it solved already one of my problems.

Best regards,
Andreas

Anonymous said...

John,
Does the variable Disable_cache_hit and Disable_cache_seed affect PS cache and BI Server cache or just one of the two?

John Minkjan said...

@Anonymous,

It's only the BI-server Cache.

regards

John

Venky said...

Hi John,

I set the variable as said in prefix area in Advanced tab and using the variable in the same report for filtering the records:

SET VARIABLE p_s_dt= select max(Booking ."PDate" ) from BK where "PDate"< CAST( '@{s_dt}{2099-01-01}' AS DATE)

but get an error:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near < select >: Syntax error [nQSError: 26012] . (HY000)

Not sure what is causing this.

John Minkjan said...

@Venky,

Looks like your missing a subject area
try:
SET VARIABLE p_s_dt= select max(Booking ."PDate" ) from BK where Booking."PDate"< CAST( '@{s_dt}{2099-01-01}' AS DATE)

TechDoer said...

John,
thanks for the post.
i am getting an error when i try below.
Appreciate any feed back.

when i am setting a variable in Prefix.

SET VARIABLE=SELECT CAST(ABS(Position."Hierarchy Gap") AS Integer) saw_0 FROM "Sales - CRM Pipeline" WHERE Position."Current Base Login" = 'Abcd';

Below is the error:

SQL Issued: {call NQSGetLevelDrillability('SET VARIABLE=SELECT CAST(ABS(Position."Hierarchy Gap") AS Integer) saw_0 FROM "Sales - CRM Pipeline" WHERE Position."Current Base Login" = ''Abcd''; SELECT Employee."Employee Login" saw_0, Opportunity."Opportunity ID" saw_1 FROM "Sales - CRM Pipeline" WHERE Employee."Employee Login" = ''Abcd''')}