Thursday, 27 October 2011

Bursting report views in cognos

Hi Guys

Below is bursting explained

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.

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

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.

2 comments:

  1. I am trying to do scenario1 but not able to do it in "Cognos Developer Edition 10.1"
    Please suggest i am using Recipient as CAMID('Everyone')

    ReplyDelete
  2. Hi Kapil,

    Developing new gen developer friendly BI framework with unique features.Like to give you access and know your opinion Please do let me know of how to reach out to you. Would be launching product in 3 weeks from now.

    Regards,
    Anugraha

    ReplyDelete