Cognos report performance tuning - Use parameter maps based on existing query

Check list of all posts and the book 

Instead of dynamically determining date range in all reports, it is the best solution to use query based parameter maps to pre-calculate all date related parameters. This approach can dramatically improve performance as the query sent to database is with “static” value as where clause. This solution is applicable for both relational and DMR.

Use dynamic parameter maps for relational mode

The following sample is used to demonstrate this idea. Please see report below

The main challenge for this sample is to find the right week number when the week is cross year end.  Can we build such report in report studio? The answer is YES, but it will result in a very big effort to make query very complicated and have very bad performance. The detail implementation is listed below

Step 1.  Build query to get relative time. Note that all detail query result should be converted as consistent type.

select convert(varchar(50),'CY_CurrentWeek_SD') as PKey, convert(int,DAY_KEY)  as Value from gosales.TIME_DIMENSION where DAY_DATE = '2007-07-15'

UNION

select convert(varchar(50),'CY_CurrentWeek_ED') as PKey, convert(int,year(DATEADD(day, +6,DAY_DATE))*10000 + month(DATEADD(day, +6,DAY_DATE))*100 +  day(DATEADD(day, +6,DAY_DATE)))  as Value from gosales.TIME_DIMENSION where DAY_DATE in (select DAY_DATE from  gosales.TIME_DIMENSION where DAY_DATE = '2007-07-15')

UNION

...............

Step 2.  Load query above into database layer, SQL setting is native. This setting will give you flexibility to get complicated logic.

Step 3. Create parameter map based on query Relative time

Step 4. Create all calculations based on parameter map

Step 5. Define query

Step 5. Define report

Use dynamic parameter maps for DMR mode

Another sample is to use DMR data model. The following step uses Break Aggregate table to demonstrate the solution.

Step 1 Create query subject

Step 2 Define parameter map based on query subject

Step 3 Define Calculation in dimension view

Step 4. Move it to presentation view

Step 5 Publish the calculation, along with Query subject (Hide column)

6. Use Current report date in daily break report

Special note   

In some cases, the logic to find the relative time periods is too complicated to use simple sql statement. Instead, you need to write complicated logic with complicated T-SQL.  Such as screenshot below

In this case, you can’t use standard Cognos SQL, You can use Pass-Through as indicated below

This pass-through doesn’t cause any problems with local processing, as it this data set is independent from SQL query generated for report.