Oracle Business Intelligence Applications

Products Banner

Dashboard prompt performance

Received Response


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!



  • 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.

  • 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).


  • You may achieve this by using presentation variables as described here:

    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.

  • 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!