Bursting Explained - Part I
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.
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.
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.
Link to Part I, Part III.
Scenario 2: A single Burst Group and Burst Recipient Query but multiple report queries.
In this case the report has multiple queries. The burst group and burst recipient information is fed by a single query.
Example: Burst a Sales report that has 2 lists based on Sales Regions and make the report available to users through Cognos Connection Directories.
RecipientQuery (Master Query) - Sales Region, Recipient
ListQuery1 (List Query 1) - Year, Sales Region, Revenue
ListQuery2 (List Query 2) - Year, Sales Region, Sales Target
You would need to create master-detail relationships between the Master Query and the 2 List queries. The Burst Group and Recipient query would be the master query.
In the above example, since the burst group and burst recipient is sourced from a single query, this query is run once before the start of bursting process. Then for each record retrieved by the burst query the detail queries are fired once each.
Say we have 100 sales regions. The master query is fired once and the 100 records are retrieved. Then for each Sales region the 2 detail queries are fired. So you would see a total of 2 * 100 queries fired against the DB in sequence.
It is in the above scenario that you would see the entire burst process taking time to complete. Assume each report output is generated in 1 minute. The entire burst process in the above example is completed in 100 minutes and that is like more than an hour.
Scenario 2: A single Burst Group and Burst Recipient Query but multiple report queries.
In this case the report has multiple queries. The burst group and burst recipient information is fed by a single query.
Example: Burst a Sales report that has 2 lists based on Sales Regions and make the report available to users through Cognos Connection Directories.
RecipientQuery (Master Query) - Sales Region, Recipient
ListQuery1 (List Query 1) - Year, Sales Region, Revenue
ListQuery2 (List Query 2) - Year, Sales Region, Sales Target
You would need to create master-detail relationships between the Master Query and the 2 List queries. The Burst Group and Recipient query would be the master query.
In the above example, since the burst group and burst recipient is sourced from a single query, this query is run once before the start of bursting process. Then for each record retrieved by the burst query the detail queries are fired once each.
Say we have 100 sales regions. The master query is fired once and the 100 records are retrieved. Then for each Sales region the 2 detail queries are fired. So you would see a total of 2 * 100 queries fired against the DB in sequence.
It is in the above scenario that you would see the entire burst process taking time to complete. Assume each report output is generated in 1 minute. The entire burst process in the above example is completed in 100 minutes and that is like more than an hour.
Links to Part I, Part II.
Scenario 3: Separate Burst Group, Burst Recipient and Report Queries.
In this case all 3 queries are separate.
Example: Burst a Sales report based on Sales Regions that has 2 lists in them and make all the sales reports available to all users through Cognos Connection Directories.
BurstGroupQuery (Master Burst Group Query) - Sales Region
ListQuery1 (Detail List Query 1) - Year, Sales Region, Revenue
ListQuery2 (Detail List Query 2) - Year, Sales Region, Sales Target
RecipientQuery (Detail Recipient Query) - Sales Region, Recipient
Create Master-Detail Relationships between Master Burst Group query and Detail List Queries. Create Master-Detail Relationship between Master Burst Group query and Detail Recipient Query.
In this example the master burst group query is run once, the data set is retrieved, for each record retrieved the recipient query is run once and the data set is retrieved. Again, for the master query the detail list queries are run once each for each record and data is retrieved.
Defect - Burst Report Views through Event Studio
Scenario 3: Separate Burst Group, Burst Recipient and Report Queries.
In this case all 3 queries are separate.
Example: Burst a Sales report based on Sales Regions that has 2 lists in them and make all the sales reports available to all users through Cognos Connection Directories.
BurstGroupQuery (Master Burst Group Query) - Sales Region
ListQuery1 (Detail List Query 1) - Year, Sales Region, Revenue
ListQuery2 (Detail List Query 2) - Year, Sales Region, Sales Target
RecipientQuery (Detail Recipient Query) - Sales Region, Recipient
Create Master-Detail Relationships between Master Burst Group query and Detail List Queries. Create Master-Detail Relationship between Master Burst Group query and Detail Recipient Query.
In this example the master burst group query is run once, the data set is retrieved, for each record retrieved the recipient query is run once and the data set is retrieved. Again, for the master query the detail list queries are run once each for each record and data is retrieved.
Defect - Burst Report Views through Event Studio
With 10.1.1 developers no longer see the "Burst Report" checkbox when trying to burst report views through Event Studio.
Create Jobs to burst report views and then use the Job inside of Event Studio to work around this.
Create Jobs to burst report views and then use the Job inside of Event Studio to work around this.
No comments:
Post a Comment