Forum Stats

  • 3,852,000 Users
  • 2,264,055 Discussions
  • 7,904,928 Comments

Discussions

Dashboard prompt performance

DriesC
DriesC Member Posts: 5

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

Best Answer

  • Martin van Donselaar
    Martin van Donselaar Member Posts: 307
    edited Mar 2, 2018 10:44AM Answer ✓

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

    pastedImage_0.png

    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.

Answers

  • Martin van Donselaar
    Martin van Donselaar Member Posts: 307
    edited Mar 2, 2018 10:44AM Answer ✓

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

    pastedImage_0.png

    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
    DriesC Member Posts: 5
    edited Mar 2, 2018 11:00AM

    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
    Martin van Donselaar Member Posts: 307
    edited Mar 5, 2018 5:08AM

    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
  • DriesC
    DriesC Member Posts: 5
    edited Mar 5, 2018 11:07AM

    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

    Martin van Donselaar
This discussion has been closed.