4 Replies Latest reply: Nov 22, 2012 7:52 AM by Maqs-Oracle RSS

    Performance issue

    Diney
      Hi,

      We are facing a performance issue for a prompt. We are having a dimension table having 30lakh records.In the prompt we are showing a column from this table and the distinct values for this column is only 40 out of 30lakhs. So it is taking time to fetch the distinct results from this column. Is there any way to improve the performance of this prompt?

      Note:Already We have applied index for this column in database.

      thanks in advance.

      Regards,
      Diney
        • 1. Re: Performance issue
          prassu
          Hi,
          Assign a default value to the prompt .Use the column from the dimension then you can get very less no.of records.Your scenario the dimension has only 40 records then it will increase the performance.

          mark if helpful/correct..

          thanks,
          prassu
          • 2. Re: Performance issue
            Maqs-Oracle
            Hi,

            You can seed cache for that column

            for example if you want to seed cache for Year column you can create a text file with logical sql as

            SET VARIABLE QUERY_SRC_CD='Seed';SELECT "Calendar"."Calendar Year" saw_0 FROM "SH" ORDER BY saw_0;

            save this file at the following path C:\middleware1\Oracle_BI1\bifoundation\server\bin>

            then seed cache using command prompt

            C:\middleware1\Oracle_BI1\bifoundation\server\bin>nqcmd -d coreapplication_OH911284761 -u weblogic -p Admin123 -s cacheseed1.txt

            Create a batch / Shell script file for running this command, and schedule it so that next time when you run the prompt the cache is seeded.

            OR

            You can also create a thin dimension using columns having less distinct values and create an aggregate table at the level of these columns.

            This will increase performance of prompt as well as reports.



            Hope this helps.


            Thanks

            Edited by: Maqsood Hussain on Nov 21, 2012 6:15 AM
            • 3. Re: Performance issue
              Diney
              Thanks Prassu and Maqsood for the quick response.

              @Prassu: Assigning a default value for the prompt is not a part of our requirement.So we cant assign a default value.
              @Maqsood : Already we have scheduled an ibot for this column to seed the cache.But it is not the permanent solution. So can you please explain in detail about the thin dimension and how can we design it to bring only the distinct columns in that new table.

              Regards,
              Diney
              • 4. Re: Performance issue
                Maqs-Oracle
                Hi,

                Suppose you have a location dimension with millions of records but Region column in this dimension has only 4 distinct records. You can create a thin dimension called REGION using ----> select distinct Region from Location.
                You can add this as a new LTS in your existing Location logical table.
                Now when you select Region in prompt the OBIEE selects REGION table with only 4 distinct values.


                The details steps are mentioned in the blog below.

                http://www.rittmanmead.com/2006/11/aggregate-navigation-using-oracle-bi-server/


                Even better permanent solution is by using Materialized views with query rewrite enabled.

                http://108obiee.blogspot.in/2009/11/aggregates-outside-obiee-materialized.html

                You can use a combination of these 2 approaches as per your requirement.


                Hope this helps.


                Thanks