I have requirement to use a Fact Column (Eg: Control flag) to be used in the Column Prompt. What is the best way of using it such that query performance will improve. If I use it directly it might have scan through my entire fact table which will have a performance impact.
Is there a best way to do it?
The "proper" way to model this in the RPD is to add the column on to a dimension vs. leaving it on the fact table. i.e. the underlying physical fact table becomes a source for both a logical dimension and the logical fact. OBIEE smart enough to sort everything out.
Having said that - the real performance issue could be because, in a prompt, it would have to scan a potentially huge fact table to get the distinct values of the control flag. If that's the case, then you should either 1) build a real dimension table to make it faster (this would be preferable), or 2) add a bitmap index to the control flag column on the fact table, which would also greatly improve the performance of the "select distinct control flag from fact table" query.
Hope this helps!
GJ, that really won't improve performance. Whether your view fires of the "select distinct control_flag from fact_table", or it's done by OBIEE, it'll still take a long time (assuming the fact table is large). Of course, using cache will help performance on this.
One other point - just to be clear - is that there is no need to move the control flag off of the fact table on the PHYSICAL side, but the proper way to model it in the business layer is on a dimension. The only reason to put on a dimension table on the physical side is to improve performance - but if you're not allowed to do that, using a view won't help in any way.
Hope this helps!
Sorry to break others posting.
Approach 1 :
How many distinct values are availbale in Control flag ??? if its is less and fixed ( suppose A,B,C).
Then create variable prompt in 'Dashoboar Prompt' instead of Column Prompt. and put the value in presentation variable.
use it as filter in the report.
If the Values is not fixed , In the variable column use the option 'Limit the values by' other column (may be month or day prompt u have already used to limit the Fact data).
Let me know You can use any of the above case. OR if you have any issue in using above approach.