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.
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.
@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.