Optimized Master Detail relationships with DQM, not really

Here's an extract from IBM site on optimized master detail relationship using DQM (http://publib.boulder.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.dyn_query.10.1.0.1.doc%2Fc_master-detail.html):

Optimized master detail relationships in reports

Master detail relationships allow you to deliver information that would otherwise require two or more reports.

You specify a relationship between the query in the master data container and the query in the detail data container. For example, you can combine a master list with a detail chart. The list can contain product lines and the chart can show details for each product line.

With the dynamic query mode, master detail relationships are optimized. IBM® Cognos® pushes the master query as a separate edge to the detail query. Therefore, instead of sending one detail query to the data source for each master value, only one query is sent for HTML, PDF, and Microsoft Excel spreadsheet software output formats.
That is not the case though when you check the profile tree logs. Master detail relationships, still execute the old fashioned way with DQM. I had this confirmed by IBM as well that this is as per design.
I am posting this so developers don't get misleaded by the article. Wish IBM removes the misleading article.

 

Master Query Cannot Be a Nested Query - Error Message

When you try bursting a report that has multiple pages with page breaks set on different queries, the report could throw errors "Master Query Cannot Be a Nested Query" unless you have master detail relationships set up between the Burst query and the Page Set Queries.


Master Detail relationships can be set up using the "Master Detail Relationships" property of the Page Set query.
 

Bursting Explained - Part I

I have been asked by so many users what happens behind the scenes when Cognos bursts a report? Why do burst reports take longer to complete? etc. And I have explained about master-detail relationships and its impact on Burst reports in an earlier post. In this post and the next couple of posts I am going to try and cover the various burst scenarios and how many SQLs Cognos fires against the backend in each scenario.

Bursting is made up of 3 components:

The Burst Query or Report Query: The report query that needs to be burst. The report could be a single query report or multi-query report. In case of a multi-query report, each data container in the report needs to have a master-detail relationship set up with the burst group query.



The Burst Group Query: The query that decides the burst group. For each record in the burst group query the report is sliced.

Example: A report needs to be burst for each Sales Regions. In this case the burst group would be the Sales Region. The report is sliced for each Sales Region record generated by the burst group query.

The Burst Recipient Query: The query that provides the recipient information.



Scenario 1: A single Burst Query, Burst Group Query, Burst Recipient Query.

In this case the list in the report, the burst group and the burst recipient information is fed by a single query.

Example: Burst a Sales report based on Sales Regions to be made available to users through Cognos Connection Directory.

Query 1 Data Items: Year, Sales Region, Sales, Recipient.

Recipient - CAMID('Everyone')

In the above Example, since burst group, burst recipient and list report are sourced from a single query, Cognos runs this query once, retrieves the entire data set and then slices the report output for the various sales regions. Hence only 1 query is fired by Cognos in this case.



Links to Part II, Part III

 

Burst Reports and Master Detail Relationships

Burst Reports with Master Detail relationships is not a good option to use in Cognos. This seems to be a major issue with Cognos and hope they rectify it soon. When you have reports that have master detail relationships and you need to burst the same, be aware that the report queries will be executed for each burst query group item.

The drawback is that you almost always end up with a report which requires a master detail relationship either because it has multiple pages with each page associated with a different query or some such reason. Its almost impossible to eliminate master detail relationships. And what you end up having because of this is a headache wherein either there is a huge number of queries fired against the DB one after the other or the scheduled process taking time due to the burst reports being executed one after the other.

What IBM needs to do is to have a property that report authors can set as to whether the detail query needs to be run for each master item or if the detail query needs to be executed once and then have it associated / sliced based on the master query.