Performance Tip: Use Static Prompts

If you have any prompts on a report, chances are that they include a time reference prompt variable like year and/or month. In most cases they refer to a time dimension table or similar in the data source. This implies that every time a report with time prompts is executed, the corresponding queries need to be processed over and over again. The query will be sent to the database, the database engine will perform a distinct on the months and the years in the time table, send the result back and so on.

This is a waist of time and server resources. In the case of year and month this can be avoided by using static prompts, as, well euh .. , for the foreseeable future we do not expect these concepts of months and years to change. The same might be true for other items, like

  • Product groupings
  • High level categories
  • Countries
  • Regions
  • Business units
  • Time dimension items
  • .. and so on

Every time you encouter a fairly static prompt that you currently use queries for, check if you can turn it into a static prompt and boost performance.

By defining these types of prompts as static prompts the number of queries sent and processed can be reduced. This increases performance and can usually be applied in many situations.

In this example prompt you have 2 cascading prompts, 1 for year and 1 for month, with their associated queries. Listing the same years and the same 12 months every time. This not only decreases performance, it also reduces the efficient use of the available resources. This can be avoided by replacing them with static choices.

Cognos Static Prompts

Cognos Service