Cognos Know How: How to improve master detail report performance
I was under the impression that both the Master query and the Detail query will be fired only and then cognos will do some processing locally to display the detail data for every distinct. We have already created indexes on the database table. . performance gain is sacrificed when using master-detail relationships ( this. These are some general guidelines for performance tuning of Cognos Reports 1. When using direct facts in the report do not create shortcuts, use directly in facts under 12 Report tier - Avoid using master detail relationship if possible. Report Performance after creating Master detail relationship. on: 09 Aug am». Hi Everyone, I am facing performance issues after using master detail relationship between 2 queries in Cognos I have inserted a chart.
If we look at the trace, we see exactly that: If we were using DQM, and if the master-detail optimization governor was enabled, Cognos might be able to drop the number of queries down to 3. One for the list of regions, one for the crosstab, and one for the graph. The queries would be cached and Cognos would intelligently split the data there. In addition to sectioning data, we can pass values to parameters.
This allows us to solve complex requirements.
Improving IBM Cognos Report Studio Master Detail Report Performance
Dynamic Filters By populating a table with filter expressions, we can generate different pages with the same crosstab but different filters. This happens more with operational systems than data warehouses, but there are some cases where it is useful.
In this example, we are looking at specific managers working against specific promotion campaigns. Each details would have different filters applied. This does not need to be limited to specific campaigns.
This is not limited to filters, columns can be applied as well. By using the token prompt inside a field, and populating that with a field reference, it is possible to dynamically modify a list.
As a practical example, consider a scenario presented by one of my past clients. A series of reports needed to be bursted to a number of managers. Each manager had a unique set of filters to be applied. Some of them needed to see data at a high level of granularity, and others needed to see detailed data. The standard method, by joining the burst table to the necessary fact tables, would not work here due to the vast range of requirements. This relationship is used to filter the detail query for every record in the master query, thus resulting in multiple detail queries being issued to the underlying database.
As the master query record set increases, the number of detail queries increases as well, slowing down the overall report performance. The report has been sectioned by Order method.
Master-Detail Relationships in Cognos | PerformanceG2
This report layout is depicted by the following screen capture. For each of these headers, it also displays a crosstab with Product line on the rows, Year on the columns and Quantity as the measure. As there are seven order methods in total, the detail query will be sent down to the database seven times.
Forcing a Single Database Query in a Master-Detail Report In order to send a single database query for a master-detail style report, the report needs to be authored a certain way and a configuration change needs to be applied to the CQEConfig.
The following sections will provide the steps required for both of these items. To add this parameter: Make a copy of this file and rename it to CQEConfig.
When completed the entry should read as follows: The changes to this file will take affect once the IBM Cognos service has been stopped and restarted.
This section will use the report referenced in Section 2 as a starting point and provide the required changes. Rename one of the Query objects to Master and the other to Detail.
The BaseQuery which consists of the original data items used to run the report and two other Query objects named Master and Detail.