This discussion is archived
4 Replies Latest reply: Nov 22, 2012 5:52 AM by Maqsood Hussain RSS

Performance issue

Diney Pro
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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
    Maqsood Hussain Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    Maqsood Hussain Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points