Dashboard prompt performance

DriesC

Hi,

We have one choice list prompt that is exceptionally slow. It will take over a minute to show some values.

I've tried recreating the query I expect the choice list would run but that returns the desired result in a few milleseconds.

So what I would like to do in the first place is determine what is happening when BI is loading this prompt. What query is being executed.

In the nqquery log I can see the analysis query, but not the one for the prompt.

An analysis filter you can "convert to SQL".

But for a prompt, I can't find the query anywhere.

Is there a way to find this query or another way to go about improving this prompts performance?

Been searching my n*ts off. Asking a question to the community is always my last resort.

We're on OBIEE 12c / database is also 12c.

Thanks in advance!

Dries

Martin van Donselaar

You can override the logical sql used by selecting 'SQL results' for Choice list values:

Is that what you are looking for?

EDIT: also, the physical sql issued by the prompt will show in your session log with type ValuePrompt, or you can issue the logical sql yourself on the administration tab to get physical sql.

DriesC

Thanks Martin!

Looks promising.

I noticed the "Limit values by" checkbox disappeared, which is understandable.

Do you know how I reference these other prompt values in the (logical) SQL query?

For example: this choice list is limited by a date prompt (between mindate and maxdate).

Dries

Martin van Donselaar

You may achieve this by using presentation variables as described here: https://myobieespace.wordpress.com/tag/cascading-prompts/

However I think there maybe some issues with this, like it requires you to hit apply before the dependent prompt will refresh if I remember correctly

HOWEVER...You should first look on how to fix your model / Prompt definition so it will generate the correct query by itself. Having to use customized Logical sql usually means your model isn't correct.

The Logical sql and/or Physical sql  that the prompt generates should give you a hint on what is going on.

DriesC

Hi Martin,

Found the logical SQL in the sessions window. Executed it as you proposed and found the SQL in the log.

Something seems to be off with the filters indeed so we'll look into the repository.

Thanks for your assistance!

Dries