HOW TO SPEED UP TIME FOR PROMPTS TO LOAD 2

[benbotektips]

If report prompts are loading too slowly when opening an impromptu report, they may be 'taking the long way home' (from Supertramp-1980). There are 2 things to do about this.
1) avoid report picklists, go with catalog picklists. While report picklists are more flexible and require no database editing, 9 out of 10 data situations I've encountered can be addressed via the database, either inserting into or updating tables, or creating views and catalog prompting on them. Impromptu treats a view just like a table, including joins.
2) the 'use' and 'display' buttons matter. Let's say there's a numeric, length 4 employee_id field, and a character employee_name field, 25 bytes. When building the catalog prompt in prompt manager, call it 'employee', throw the employee_id field into the 'use' box, and the employee name into the 'display'. In the report filter, simply code for the 'use field', i.e. ---> employee_id in
?employee?. The list of employees displayed will still be by name, not id. You can also build the prompt in the catalog, vs. the report, especailly if it's a common prompt used in > 1 report. The principle is the same, 'use' (or build the prompt list) on the code, 'display' (have the report print) the decode. Especially on numeric code fields, prompt appear performance approves, sometimes dramatically.
As the cherry on top of the sundae, this works very well for shortening cube build time in Powerplay, make the 'source' the short numeric code, and the 'label' the longer decode whcih will appear as the dimension

 

[griffindm]

Ben,

This is a nice summation of optimizing picklist prompts. One thing I would add (and is also in the Prompt FAQ) is that you will greatly speed the load time for prompt picklists (either catalog or report types) if you make the source a reference table rather than a fact table. Impromptu will run a distinct query to arrive at a unique list of values. If the prompt column is not indexed, this can take a loooong time for a large fact table.

Cheers ,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[benbotektips]

Very good point. I was starting off under this assumption, that all the long-character decodes would be in the dimension tables (we call them transaction & dimension), and the database was modelled on a star schema, but it bears mentioning. In general, I would take care of the database before taking care of Cognos business.