Cognos report performance tuning - Use Prompt Token macro in report studio to enhance performance

Check list of all posts and the book 

The idea is to save date expressions in parameters, then use #Prompt(‘ParameterX’, ‘token’)# in query items or filters. When changing date logic, you will change it in single place. At the same time, you can get good performance as well, because the filters are built into query. It is generic and applicable for all models.

Most of reports is involved with complicated dates. The common practice is to create a date control query to get all dates, and then use this query to join with main query to filter out data. The major advantage is to change date logic in single date control query. Once changed date logic, all main queries joined with date control query will be changed accordingly. The main disadvantage is that performance could be an issue, as data is filtered out by join condition.  to resolve performance issue, you need to add date logic in all main queries. However, this brings a main disadvantage of maintenance. When date logic is requested to change, you need to change many places. To get better performance and maintenance, we can use prompt token to resolve this issue.  

This document is intended to demonstrate this idea by getting sales for Current Half Year to Date (CHTD) and Prior Half Year to Date (PYTD) based on last complete month, assume there are many pages, and data containers. Please see screenshot below, and download report specification (version 10.2) for reference

Solution option 1:  Embed date logic into main query - good performance, difficult to maintain

Query option 1:

• Region: [Sales (query)].[Retailers].[Region]
• Period: case when ( [Sales (query)].[Time].[Date] between  [CHTDFrom] and [CHTDTo] )  then ('Current HTD') when  ( [Sales (query)].[Time].[Date] between  [PHTDFrom] and [PHTDTo] )  then ('Prior HTD') end
• Revenue:[Sales (query)].[Sales].[Revenue]
• CHTDFrom: case when ([CHTDTo] >_make_timestamp (year([CHTDTo]),07,01) )  then  _make_timestamp (year([CHTDTo]),07,01) else _make_timestamp (year([CHTDTo]),01,01) end
• CHTDTo*: if (_last_of_month ( 2012-04-05) = 2012-04-05) then (2012-04-05) else (_last_of_month(_add_months (2012-04-05,-1)))  *assume current date is 2012-04-05
• PHTDFrom: _add_years ([CHTDFrom], -1)
• PHTDTo: _add_years ([CHTDTo], -1)
• DateFilter: ( [Sales (query)].[Time].[Date] between  [CHTDFrom] and [CHTDTo] ) OR  ([Sales (query)].[Time].[Date] between  [PHTDFrom] and [PHTDTo] )

This solution can achieve a very good performance. Assume that this data logic is used in many pages, and their data containers, it becomes very difficult to maintain. In case when there is small change of data logic, you have to have many times through all data items.

Solution option 2:  Use Join with centralized date logic query - poor performance, easy to maintain

To resolve the issue of option 1, we can create date logic into a query and then use it to join all main queries. In case when there is a small change, then we need to only change the date query.

• Region: [AllData].[Region]
• Period: case when ( [AllData].[Date] between  [CHTDFrom] and [CHTDTo] )  then ('Current HTD') when  ( [AllData].[Date] between  [PHTDFrom] and [PHTDTo] )  then ('Prior HTD') end
• Revenue: [AllData].[Revenue]
• CHTDFrom: [DateParameters].[CHTDFrom]
• CHTDTo: [DateParameters].[CHTDTo]
• PHTDFrom: [DateParameters].[PHTDFrom]
• PHTDTo: [DateParameters].[PHTDTo]

This solution resolves maintenance issue. However, it could be a very big problem with performance when the dataset is very big.

Solution option 3:  Use Prompt Token (#Prompt(‘ParameterX’, ‘token’)#) - good performance, easy to maintain

Is there an idea to resolve both performance issue and maintenance issue? The answer is yes, using Prompt Token.

• Period: case when ( [Sales (query)].[Time].[Date] between  [CHTDFrom] and [CHTDTo] )  then ('Current HTD') when  ( [Sales (query)].[Time].[Date] between  [PHTDFrom] and [PHTDTo] )  then ('Prior HTD') end
• Revenue:[Sales (query)].[Sales].[Revenue]
• CHTDFrom: #prompt (' CHTDFrom','token')#
• CHTDTo #prompt (' CHTDFrom','token')#
• PHTDFrom: #prompt (' PHTDFrom ','token')#
• PHTDTo: #prompt (' PHTDTo','token')#
• DateFilter #prompt ('DateFilter','token')#

All prompt values can be specified on the page, and make them hidden.

If we need to change date logic, then we can go prompt and change its value. There is NO need to change query items for all queries.

Please refer to ex blog make expression reusable for detail