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
give me one example on master detail relation ship
ReplyDelete