Thursday, January 6, 2011

Caching Prompts

Started the new year with a Cognos job failure for refreshing the prompt cache due to server issues. I am now trying to think of an effective failure/re-start strategy.

Caching Prompt values makes sense when you have huge dimensions in your warehouse that change slowly over a period of time. In my case we have dimensions that have SCD Type 2 implemented in them requiring us to cache prompts.

But one needs to keep in mind that the data you see in your prompts are not live data and are not security oriented. One also needs to monitor the prompt cache refresh jobs to ensure they run successfully else users may end up seeing old data in the reports.

To cache report prompts you need to create a Cognos job. Select the report whose prompt values need to be cached.Under report options, choose "Refresh the report cache" option for "Run the Report to" property. The clear cache option is available under the report properties section. You can also set properties to clear the cache in x days.


Anonymous said...

Caching prompts is a good practice. I wasn't aware though that you could clear the cache. What might be a reason for doing so?

Zephyr said...

A couple of reasons you would like to clear the cache would be - let's say your cache refresh job is failing or goes into pending state due to server reasons or due to a huge number of other jobs running and you do not want users to run reports against an outdated cache and don't mind the prompt page taking a long enough time till you can get the cache set up. In that case you would need to clear the cache.

Or in the future, you decide to build a new dimension table that holds only current data. You change your report to pick up values from this table and hence you no longer need a cache.

Also, the prompt cache is refreshed automatically when a report runs interactively and the cache is expired. You can get the cache to expire by setting the clear cache property to x number of days.

Anonymous said...

hey how to set that clear cahe property in x days

Zephyr said...

You will find it in Report Propeties page,on Report tab, under Advanced options.