Tuesday, November 6, 2012

How to improve master detail report performance

Context
Please check the old post of report performance tuning (How to improve Cognos report performance), this document is focus on master detail relational report. Two approaches have been described:

Single query sent to database server for both master detail from IBM Cognos Proven Practices

Use faked optional filters instead of data item to build master detail relationship

Single query


I have tried to use a Cognos Proven Practices (http://www.ibm.com/developerworks/data/library/cognos/reporting/performance_and_tuning/page582.html ) to improve master detail report performance. The whole idea is to send a single query to database instead of sending 1 (master query) + number of master query records (detail query). In case when queries take most of report generation time, this method could be very helpful. The test result is very positive for a single master and detail; however, it won’t work against one master and multiple details, such as weekly inventory dashboard (list contains two different kinds of charts in two columns).

The process is documented below for your quick reference:


Step 1: Change Cognos server setting:
add <entry name="RequestHintLocalCacheHasHigherPriority" value="0"/> on a new line underneath the <section name="QueryEngine"> to CQEConfig.xml
and reboot Cognos server.

Step 2: Combine master and detail query as consolidated base query and define master and detail query derived from base query as diagram below



Step 3: set master query parameter as

Step 4: set Detail query parameter as

Step 5: optional:  Check SQL profile where you can see SQL command sent to SQL server. Check performance before and after.


Faked optional filter


Given a sample below


The 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


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

1 comment:

  1. give me one example on master detail relation ship

    ReplyDelete