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.
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..
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.
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.
Edited by: Maqsood Hussain on Nov 21, 2012 6:15 AM
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.
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.
Even better permanent solution is by using Materialized views with query rewrite enabled.
You can use a combination of these 2 approaches as per your requirement.
Hope this helps.