Dashboard Prompt values renderring slow with views

Question

Hi Folks,

Our whole report and prompts are based on view created based on EBS source tables as part of real time reporting, Here the problem is the delayed response in populating the values in prompts.

So is there a faster way to see the prompt values in drop down for users?

I appreciate to get response or links to share for this kind of issue.

Regards

DJ

FPonte · Answer

Hi DJ.

It all depends on the how complex is the view (shouldn't be for Prompts) and the cardinality of the underlying tables.

Give some more information so we can help.

Materialized Views as suggested by Gianni are a good approach and will save time if well built.

Cheers

Fernando

Gianni Ceresa · Answer

As there is no explicit reference to any tool/version the reply will be fairly generic too.

You are saying your prompts are slow because of views: did you build a time profile to clearly identify what takes time?

Assuming it's because of views, to make it "faster" (it's all relative and by far not a universal solution) materialize the result of the view.

A prompt generally perform a kind of SELECT DISTINCT <column> query, therefore on a view it will go over the whole result of the view to get the values.

If you have those values materialized the query will be faster as there will not be a bunch of logic to execute anymore.