4 Replies Latest reply: Apr 2, 2014 5:17 AM by Orcl01 RSS

    OBIEE 11g Prompt not loading all values.

    Dev O



      Prompt wont load all values.




      1) It populates first 1-256 values. When I click 'More' its shows 'Loading' for hours.

      2) If searched for individual value it shows in the result.

      3) This behavior It is only for this prompt, other prompts shows 1000+ values.

      4) Tried on both IE and chrome

      5) If i run a request in analysis i see all 588 values but not on prompts.

      6) If i run below sql it sometimes returns results in few seconds and sometimes it takes hours.


      What can be the issue:


      1) If DB issue then how come it loads first 256 values.

      2) Is it a bug with OBIEE?




      select D1.c1 as c1 from ( select distinct T393313.HOU_NME as c1

      from obia.WC_FAX_F T393313 order by c1 ) D1 where rownum <= 65001




        • 1. Re: OBIEE 11g Prompt not loading all values.

          Hi Dev,


          What do you want to achieve by loading all values in single instance. In my context I would say its not a bug, its working properly/correctly.

          • 2. Re: OBIEE 11g Prompt not loading all values.

            Default behaviour of OBIEE to display 256 records in prompt for USER INPUT : Choice List. Then you click it will bring 256 more. If your data set is constrained with other columns or column itself comes from huge table it will load for endless time.




            If you need to do this, edit the instanceconfig.xml file.  This file is located at …

            Add the MaxDropDownValues parameter to the file.  If you do not already have a Prompts section, add it, and then add MaxDropDownValues between the Prompts tags.  And make sure that all this is between the ServerInstancetags which will already be there in the file.

            Your config file will look something like this …




            Source biepedia



            • 3. Re: OBIEE 11g Prompt not loading all values.
              Dev O

              Hi All,




              Of the 500 LOVs in my prompt, 15 records have special characters (ä, ö, ü).




              1) When I try to select all values in the prompt it shows around 256 values then later if I click 'More' it shows 'Loading' and keep showing the same for hours.

              2) The session log shows all 500 values retrieved. so does this mean the prompt is not able to render all values.

              3) When i run a ad-hoc analysis I see all 500 values retrieved with special characters as is..

              4) While in prompt out of the 256 values displayed on the prompt, which ever record has special character (ä, ö, ü) is replaced with ??? in the prompts selection window.

              5) I have other column prompts which when clicked on more shows 1000+ (all records) in the selection pane.


              Is this something to do with Order by Clause which causes performance degradation? Any workaround?





              • 4. Re: OBIEE 11g Prompt not loading all values.

                Please have a look at this.


                1)Check whether your prompt column is constrained with other columns in the dashboard prompt

                2)make sure that it is not pulled from a fact table

                3)If both of the above statement is good in your case , query the data base with "Select distinct column from table and not down how much time it is taking to retrieve all values.

                4)I don't think that first 256 comes faster and next 256 is very slow. Clear all your cache and execute it again. if it is slow it will be slower for first 256 as well.

                5)Check for any complex SQL written in Prompt select list or default value box.


                This much only i could think of now.

                Mark if it helped you