Cognos report performance tuning - Use optional parameter to filter data in detail query for master detail report

Check list of all posts and the book 

The idea is to create a faked optional parameter in detail query, then detail data will be filtered from base, instead of filtering from sub-query. the sample below is used to demonstrate this idea.

The normal way
chart is generated by detail query, while the detail query is based on BaseDetailQuery.  Such a usage occurs in many situations

Then make a master detail relationship in report with region




When running report by using SQL profile, you can see it run through 5 regions


The generated query is displayed below, the big issue about this approach is that database will retrieve all data in sub query, and then filter out data based on region. If the data is big, then performance issue will occur.

SELECT Query313.year3,
      Query313.revenue
FROM   (SELECT coguda00.sales_region_en                          AS Region,
              Order_method10.order_method_code                  AS
              Order_method_code,
              TIME_DIMENSION11.current_year                     AS Year3,
              Sum(coguda30.quantity * coguda30.unit_sale_price) AS Revenue
       FROM   gosales.sales_region coguda00,
              gosalesrt.retailer_site coguda01,
              gosales.country coguda02,
              gosalesrt.retailer coguda03,
              gosalesrt.retailer_site_mb coguda04,
              gosalesrt.retailer_contact coguda05,
              gosaleshr.gender_lookup coguda06,
              gosales.order_method Order_method10,
              gosales.time_dimension TIME_DIMENSION11,
              gosales.order_details coguda30,
              gosales.order_header coguda31
       WHERE  ( ( TIME_DIMENSION11.day_key ) % ( 100 ) ) <> 0
              AND coguda01.retailer_site_code = coguda31.retailer_site_code
              AND Order_method10.order_method_code = coguda31.order_method_code
               AND ( Datepart(year, coguda31.order_date) * 10000 +
                        Datepart(month, coguda31.order_date) * 100 ) +
                      Datepart(day, coguda31.order_date) =
                  TIME_DIMENSION11.day_key
              AND coguda31.order_number = coguda30.order_number
              AND coguda02.sales_region_code = coguda00.sales_region_code
              AND coguda01.rtl_country_code = coguda02.country_code
              AND coguda01.retailer_site_code = coguda05.retailer_site_code
              AND coguda05.retailer_site_code = coguda04.retailer_site_code
              AND coguda05.gender_code = coguda06.gender_code
              AND coguda01.retailer_code = coguda03.retailer_code
       GROUP  BY coguda00.sales_region_en,
                 Order_method10.order_method_code,
                 TIME_DIMENSION11.current_year) Query313
WHERE  Query313.region = Cast('Americas' AS NVARCHAR(50))
ORDER  BY 1 ASC  


Please download report specification for review

Solution
To resolve this problem, you can define a faked optional filter in base detail query.




Then you can define the master detail relationship with this optional filter

Using SQL profile, all queries is generated below


Looking into the generated query, you will see the filter is done in subquery, which will have much better performance, when data is big

SELECT Query313.year3        AS levelkey,
      Sum(Query313.revenue) AS Revenue
FROM   (SELECT coguda00.sales_region_en                          AS Region,
              Order_method10.order_method_code                  AS
              Order_method_code,
              TIME_DIMENSION11.current_year                     AS Year3,
              Sum(coguda30.quantity * coguda30.unit_sale_price) AS Revenue
       FROM   gosales.sales_region coguda00,
              gosalesrt.retailer_site coguda01,
              gosales.country coguda02,
              gosalesrt.retailer coguda03,
              gosalesrt.retailer_site_mb coguda04,
              gosalesrt.retailer_contact coguda05,
              gosaleshr.gender_lookup coguda06,
              gosales.order_method Order_method10,
              gosales.time_dimension TIME_DIMENSION11,
              gosales.order_details coguda30,
              gosales.order_header coguda31
       WHERE  coguda02.sales_region_code = coguda00.sales_region_code
              AND coguda01.rtl_country_code = coguda02.country_code
              AND coguda01.retailer_site_code = coguda05.retailer_site_code
              AND coguda05.retailer_site_code = coguda04.retailer_site_code
              AND coguda05.gender_code = coguda06.gender_code
              AND coguda01.retailer_code = coguda03.retailer_code
              AND coguda31.order_number = coguda30.order_number
              AND coguda00.sales_region_en = 'Americas'
              AND ( ( TIME_DIMENSION11.day_key ) % ( 100 ) ) <> 0
              AND coguda01.retailer_site_code = coguda31.retailer_site_code
              AND Order_method10.order_method_code = coguda31.order_method_code
               AND ( Datepart(year, coguda31.order_date) * 10000 +
                        Datepart(month, coguda31.order_date) * 100 ) +
                      Datepart(day, coguda31.order_date) =
                  TIME_DIMENSION11.day_key
       GROUP  BY coguda00.sales_region_en,
                 Order_method10.order_method_code,
                 TIME_DIMENSION11.current_year) Query313
GROUP  BY Query313.year3  

Please download report specification for review