MY Prompt Pages takes to Long to load for one Report

[richiearora]

Hi All,

I am facing this issue off late on my Environment that when i try to fetch my one Particular report, it takes too long to render Prompt Page.

Please Help.

Regards

[rockytopmark]

Please send us more information so we can give a somewhat educated answer.

The main questions I have are:

1. Version of Cognos?
2. How many prompt controls?
3. What is the source of the prompt data... pure dimensions or DISTINCT'ed Facts?
4. How many overall queries and report objects in the report?

If you are on Cognos 8.2 or higher, there is a RS Query setting that allows you to tag queries that should be used to determine prompt data types.  If 1 query has all the parameters in use in it, and there are many other queries, you can specify that 1 query to be used only, for determining prompt data types, and have Cognos avoid all the other queries.

If you have many prompts, you will undoubtedly have many queries to run to populate those prompt controls.  Best Practices is to use a pure dimension (when available) to source prompt controls, versus getting the distinct values from a fact table.

Lastly, when running a report, Cognos will first validate the report before executing.  The more queries you have defined, and the more report objects you have using those queries, the longer the validation will take.

[Arsenal]

you can try the settings below on your prompt queries only (rockytopmark talked about the first option below):

use for parameter info: Yes
Execution Method:Concurrent
Use local cache: Yes
Execution Optimization: First row

[richiearora]

Hi Guys,

thanks for the reply.

1. Version of Cognos?  --> 8.3
2. How many prompt controls?--> 4 Value Prompt(All using Seperate Queries to load), 2 Select And Search prompt and Date Prompt
3. What is the source of the prompt data... pure dimensions or DISTINCT'ed Facts?-PURE DIMENSION
4. How many overall queries and report objects in the report? Queries: 13.. 5 Purely used for prompts, and what do you mean by report Objects?


Another thing i have noticed is, This same report works or rather Loads quickly on other environment which has Very less data when compared to the Environment which it takes time.

[richiearora]

Hi Guys,

An update, We have ruled out the abouve Properties Mentioed to Cache the Queries.

because we have Distributed Architecture and would not be able to Do the same, Because this will affect other Applicartion team.

However, I have come across the new thing for Cacheing the Prompt everyDay As a Job.

I have tried the Same , But it has Not Helped.

Please Advise.

Regards,
Richie

[cognostechie]

Hi Richie -

Since you said that your prompt source is a dimension, have you considered creating a Queru Subject just with the distinct values required for the Prompt (Ex: Query Subject for Customer Master table) and use that to populate the prompts?

I usually create seperate query subjects that are based on Master tables in the databse and then use them to populate the prompts. That makes it very fast. Moreover, it ensures that there will be either none or minimal difference between Development and Production databaes so the testing is real time. Differences are usually in transaction data volumes.

[sir_jeroen]

When the prompt page is loading what is the CPU usage (open task manager) of your local Webbrowser (e.g. Internet Explorer).
This because if your prompt page contains contains a lot members, it's could be that your browser is busy generating the page while Cognos is already finished.

For example:
If you have a query that returns e.g. 2000 items (it's unlikely that you would use a prompt like that, but it's just for illustration) which are made available for selection using a listbox.
This query can be finished in the database in e.g. 14ms, but your browser can be busy e.g. for 10 seconds because the browser has to convert the html to a visual gui (in this case a listbox). Your browser might be consuming up 100% of local cpu power.

[Arsenal]

Hi Guys,

An update, We have ruled out the abouve Properties Mentioed to Cache the Queries.

because we have Distributed Architecture and would not be able to Do the same, Because this will affect other Applicartion team.

However, I have come across the new thing for Cacheing the Prompt everyDay As a Job.

I have tried the Same , But it has Not Helped.

Please Advise.

Regards,
Richie

The architecture you have shouldnot impact what settings you use within RS. Granted that using local cache "may" affect the application server depending on how much data your prompt brings back, but it should not significantly impact the app server to such an extent that other applications on the server start being affected.
You can give it a shot and see. Your admin can also monitor resources on the app server while you run the report with the local cache turned on to see how much resources are being consumed

The storing of prompt cache by running a job is a pretty useless option in most cases IMHO. For one, if you have row level security, then it doesn't matter whether you store the cache or no, the cache will be refreshed everytime someone runs the report because each person running the report might have different access levels.

For another, users will have to be granted write request on this report which I personally don't like.

I suggest you read the document below. It discusses prompt performance in great detail and many of the points mentioned to you by the posters here have also been mentioned in this document. Additionally, the document also contains some system level settings in Cognos that can improve prompt performance.

http://public.dhe.ibm.com/software/dw/dm/cognos/reporting/performance_and_tuning/advanced_prompting.pdf

[richiearora]

Thanks Guys,

for all the suggestions, it was a Hidden query that was taking too long to retreive data, when we changed the prompts to cascading prompts we were able to reduce the time to Load Prompt Page.

This is not a solution, however a definaetl workaround.

Thanks for Support again!!.

Regards,
Richie