Caching in Cognos 8

[kalyan_y]

I was searching for 'Cognos Caching' and have found some info related to Prompt page caching, which can be achieved by creating a job with 'Refresh Cache' option set.

I have a report with prompt page taking longer time to populate (more than 15 min) and hence created a job with this report as an entry with 'Refresh Cache' option set.
I executed the job and the status showed was succeeded.

I executed the report, but still the prompt page is taking ages to display.

Has anyone tried this before? If so, Could you please suggest if I'm missing anything?

[blom0344]

Is there no another fundamental issue at stake here? Prompt queries in most cases fetch distinct values from relatively small dimension tables. Such a slow opening prompt page would point at very inefficient (set of) prompt query(ies)

[kalyan_y]

Hi,

There are 19 dropdowns in the report. Some of these are associated with queries which retrieve data from dimension tables which contain few records. There are some prompts which are retrieving data from fact table itself, as there are no dimension tables for these.

[RobsWalker68]

Hi,

I think you have answered your own question here.  Retrieving data from the fact table for the prompt population is causing you issues and prompt caching will not help in this instance.

Out of curiosity why is some information coming from the fact table instead of a dimension. Are these, degenerate dimensions such as invoice or order number that you are holding in the fact table?  If so can you use a text box prompt i.e. have the user manually enter the value instead of a drop down?

Rgds

Rob

[blom0344]

Hi,

There are 19 dropdowns in the report. Some of these are associated with queries which retrieve data from dimension tables which contain few records. There are some prompts which are retrieving data from fact table itself, as there are no dimension tables for these.

A technical solution would the addition of indices to the fact table. The database optimizer is smart enough to read the index values if you scan the table for a column not covered by a dimension. Reading the distinct values from the index can be blistering fast compared to performing a 'select distinct' against a fact.

However this may be undesirable for other reasons