Bursting Explained

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. 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.

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.

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.

How to burst a crosstab report

Resolving the problem

Add the burst key to a data container with one dimension, such as a list report. To burst a data container with multiple dimensions such as a chart or crosstab, create a master-detail relationship between this query and a single dimensional query, and then add the burst key to the single dimensional query.

Drop the crosstab within a list object that contains the burst key. Please see the attached Cognos 8.2 report spec going against the GO Sales and Retailers package.

1. Create a list report with the data item (Staff Name (Multiscript)) that you want to burst on as your first column. This will be the master list.
2. Insert your chart or crosstab as the second column of the list. This automatically creates a second query, which will be the detail query.
3. Add data items to your crosstab as needed. (Order Method as Rows, Order Year as Column, Quantity as Measure)
4. In the list, create a section with the item you want to group on. This is for formatting only.
5. Hide the list column title under the List property General -> Column Titles -> Hide.
6. In the Query Explorer, add the burst key data item that is in Query1 to Query2 (Staff Name and Email)
7. Select a crosstab cell and use the ancestor tool to move up to 'Crosstab' In the properties for the crosstab, edit the Master Detail Relationship property to link the burst key in both queries. (Staff Name)
8. Click on the row section of the crosstab (Order Method in the example provided). In the properties pane under Data, open the Properties dialog and click on the box for the burst key so it is checked.
9. Set Burst Options to burst on the list object's burst key.
10. Burst the report and view the results. 

Source: http://www-01.ibm.com/support/docview.wss?uid=swg21341874 

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.





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 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.



In the above case:

Master Query - Run Once, 100 records retrieved.

Recipient Query - Run 100 times and 1 record retrieved each time.

Report Queries - Run 100 times * 2 report queries

Bursting Cognos Report to a file system

here I am demonstrating how to set file system in cognos and get brusted there and also run a script so that bursted reports can be renamed.

1) Go to the dispatcher --  ( See the picture below for reference )

2) Click on the dispatcher link to see the content manager service.


3) Go to the settings tab and click on edit on advance setting line -

 

4) Create following  2 variables and provide the path where you want to get the files bursted.

5) Save it and go to cognos configuration to do the following setting.

6) Run the report that you have to burst with the following settings.

7) You will get the output in D:/Report folder, now you want that bursted reports should get proper names or they should get names according to there bursted key.

8) Create a notepad file giving it a name "burstKey.bat" and paste the following contents and save it in D:\Reports folder.

@echo off

Rem  this batch file is launched with a working directory of \bin  

set dest=D:/Reports

echo "BurstKey Script" >> %dest%/batch.txt
echo Batch File Log >> %dest%/batch.txt

rem       Set the values for the PDF file and XML 
rem       to environment variables
rem       the reason for this is the scripting language
rem       cannot read variables as parameters
set parameter_pdf=%1%
set parameter_xml=%2%

rem      Logs the variables to a batch log file
echo values set >> %dest%/batch.txt
echo Parameter_pdf: %parameter_pdf% >> %dest%/batch.txt
echo Parameter_xml: %parameter_xml% >> %dest%/batch.txt

rem      Calls the script file 
call %dest%/burstKeyRename.vbs

echo Completed >> %dest%/batch.txt

9) create a file "burstKeyRename.vbs" and paste the following contenets in there and save it in D:\Reports folder.

'

' Last updated : Sept 24, 2007
'
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set xmlDoc = CreateObject("MSXML.DOMDocument")
Set WshShell = WScript.CreateObject("WScript.Shell")
Set WshSysEnv = WshShell.Environment("process")

' Values for Environment can be "user", "system", "volitile" and "process
' The value "process" is the only valid one for this type of operation.
Set WshSysEnv = WshShell.Environment("process")

' To check the value for process environment variable 
' this can be tested at the command prompt. 
' Uncomment the next line and run this from command line
' WScript.Echo WshSysEnv("parameter_xml")

' Initializing Variables
xFile = WshSysEnv("PARAMETER_XML")    'Name of the XML file
sFile = WshSysEnv("PARAMETER_PDF")    'Name of the PDF file

' ***** Start override section for testing outside Cognos environment *****
'
' Set the following if-then condition to "true" for local testing using files that
' have already been burst.  If the if=then condition  is set to "false", then the
' environment variables must be set properly otherwise a  run-time error will occur.
'
If (false) Then        
    'The filename component of the output file. Do not include the extension.     
    tempName = "D:\C83Outputs\wottcattondxp\TestOutputs\911_1190657394280"
    ' Derive the name of the XML descriptor file     
    xFile = tempName & "_desc.xml"
    ' Derive the name of the new output file.
    sFile = tempName & ".pdf"
End If
'
' ***** End override section for testing outside Cognos environment *****

sMsg = ""
sNewFileName = ""

'
' Read the XML descriptor file created by CRN/Cognos 8 to get the value of the element. This
' value will be used as the new filename. If there is no value in the element, a new file will not
' be created.  It is assumed that the value of the element does not contain any of the
' reserved characters the underlying OS uses for filename. 
'
xmlDoc.async = False
xmlDoc.Load xFile

If (xmlDoc.parseError.errorCode <> 0) Then
    sMsg = xFile & " : Error parsing XML " + xmlDoc.parseError.reason        
Else
    Set rootXML = xmlDoc.documentElement
    Set oNodeList = rootXML.childNodes
    iNode = 0    
    
    For Each Item in oNodeList
        If (xmlDoc.documentElement.childNodes.Item(iNode).basename = "burstKey") Then
            sNewFileName = xmlDoc.documentElement.childNodes.Item(iNode).text
            If (sNewFileName = "") Then
                sMsg = xFile & " : element in '" & xFile & "' is empty. Unable to copy file."
            Else
                sMsg = xFile & " : Burst key value is '" & sNewFileName & "'"
            End If

            ' This if condition  was added to support the use of CAMID for the burst key
            if (instr(sNewFilename, "CAMID")) Then
                intFirstPosition = instr(sNewFilename, ":")
                intSecondPosition = instr(intFirstPosition, sNewFilename, ")")
                sNewFileName = mid (sNewFilename, intFirstPosition + 1, intSecondPosition - (intFirstPosition + 2)) 
                sMsg = xFile + " : Value extracted from CAMID is '" & sNewFileName & "'"                
            End If
        End If
        iNode = iNode + 1        
    Next
    
    Set oNodeList = Nothing
    Set rootXML = Nothing
End If

'    
' If there is a new filename, make of copy of it otherwise just log an error. A local log file with a name
' that is derived using the filename given to the output by CRN/Cognos 8 will be created so it is easy
' to determine if the file copied successfully  or not. Since this information will also be written to the
' persistent log file, writing to the local log file can be bypassed if writing it creates too much clutter in the
' location specified in CM.OUTPUTLOCATION.
'
if (sNewFileName <> "") Then
    sNewFileName = getPath(sFile) & sNewFileName & ".pdf"
    writeLocalLogFile sFile, sMsg & vbCrLf & "Copying file named '" & sFile & "' to '" & sNewFileName & "'" & vbCrLf
    objFSO.CopyFile sFile, sNewFileName
Else
    ' Set this variable to an error message so it will be picked up below when writing to the persistent log file.....
    sNewFileName = ""
    writeLocalLogFile sFile, sMsg & vbCrLf
End If

'        
' Update the persistent log file with the result. This log file will have data appended to it in order to keep the history.
'
sMsg = "----- Start of entry -----" & vbCrLf
sMsg = sMsg & "Date : " & date & vbTab & "Time : " & time & vbCrLf
sMsg = sMsg & vbCrLf & "Original Name :" & sFile & vbCrLf
sMsg = sMsg &  "New Name : " & sNewFileName & vbCrLf
sMsg = sMsg &  "----- End of entry -----" & vbCrLf & vbCrLf

sPersistLogFile = getPath(sFile) & "crn_pdf_rename.txt"
writePersistLogFile sPersistLogFile, sMsg
'
' All done.. Release references to the objects used in this app.
'
Set objFSO = Nothing
Set xmlDoc = Nothing
Set WshShell = Nothing
Set WshSysEnv = Nothing


Function getPath(sFileName)
    sPathOnly = ""
    lastSlashPos = InStrRev(sFileName, "\")

    if (lastSlashPos > 0) Then
        getPath = mid (sFileName, 1, lastSlashPos) 
    End If
End Function

'
' writeLocalLogFile
'     Create a log file using a name derived from the filename that was generated by
'    CRN/C8 before it was written to the location specified by CM.OUTPUTLOCATION.
'
Sub writeLocalLogFile(sFileName, sData)
    sLogFile = left(sFileName, instr(sFileName,".")-1) +   "_log.txt"
    writeLogFile sLogFile, sData    
End Sub

'
' writePersistLogFile
'     Write a record to the persistent log file
'
Sub writePersistLogFile(sFileName, sData)
    writeLogFile sFileName, sData
End Sub

'
' writeLogFile
'     Generic routine to open a file, append a record to it and close the file
'
Sub writeLogFile(sFileName, sData)
    If (objFSO.FileExists(sFileName)) Then
        Set logFile = objFSO.GetFile(sFileName).OpenAsTextStream(8)        
    Else
        Set logFile = objFSO.CreateTextFile(sFileName)
    End If

    logFile.Write sData
    logFile.Close
    
    Set logFile = Nothing
End Sub

now follow the 6th step again and you will get proper pdf output with name same as burst key.

Parameterized URLs for Retrieving Burst Outputs

Many of you must be aware of using URLs for inserting Cognos content in external applications. How do you frame the URL for retrieving bursted report outputs?

To retrieve burst output of the report view TEST stored in the folder TEST under Public Folders with burst key 123:

http://< cognos environment>?b_action=cognosViewer&ui.action=view&ui.object=/content/folder[@name='TEST']/reportView[@name='TEST REPORT']/reportVersion[starts-with(@name,'20')]/output[contains(@burstKey,'123')]

Note that the above URL will look through all burst outputs generated in the year starting with "20" i.e. 2000 - 2999.

You can use starts-with or contains for the reportVersion and output options.

Example: We have a report Burst PoC with Burst outputs.





The URL to retrieve the burst output for Americas would be:

Parameterized URLs for Retrieving Burst Outputs - Part 2

I had long written about using Parametrized URLs for retrieving burst outputs (Parameterized URLs for Retrieving Burst Outputs). The solution proposed works when your burst Ids are unique across all report versions as was in my case. But what if the report generates the same set of burst outputs each time but with different data and you have report versioning property set to more than 1?

In this case, you need to modify the URL as shown below:

http://< server>?b_action=cognosViewer&ui.action=view&ui.object=/content/folder[@name='TEST']/reportView[@name='TEST REPORT']/reportVersion[last()]/output[contains(@burstKey,'123')]

The "last()" keyword over here ensures that Cognos looks through the latest report version to get the burst output with burst key 123.

Creating Drill-Through Style Reports using Parametrized URLs

Here's an example of how you can create Drill Through Style Reports using Parametrized URLs against saved report outputs.

Requirement: Create a parent report displaying various Product Lines. Create a child report that displays individual Product Line details. Both the reports need to be scheduled and saved. Users should be able to navigate from each parent Product Line record in the parent report to the corresponding product line child report output.

The requirement to have the reports scheduled and saved makes sense when you are dealing with either huge amount of data being pulled into these reports or being rendered in the reports causing them to run for a long time when run on-demand. Hence it makes perfect sense to have these reports scheduled to be run once a day, burst and saved.

Solution:

Step 1: Create the parent report by dragging in the Prod Line data item.

Step 2: Create the child report by dragging in Prod Line and the other required data items. Set the bursting options on this report to produce burst outputs by Prod Line.

Step 3: Now to create the navigational links, look up the search path for the child report.

Step 4: In the Parent report, create the HTML items as shown below:



HTML Item 1: <a href="../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=view&ui.object=/content/folder[@name='Reports']/report[@name='Dim - Parm URL 2']/reportVersion[last()]/output[contains(@burstKey,'

HTML Item 2: Set the source type to Data Item Value and choose the Prod Line data item.



HTML Item 3: ')]&cv.header=false&cv.toolbar=false">

Use the cv.header and cv.toolbar if you need to hide the Cognos Viewer header and toolbar.

HTML Item 4: </a>

Step 5: In the child report, create the link to the parent report as shown below:



HTML Item 1: <a href="../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=view&ui.object=/content/folder[@name='Reports']/report[@name='Dim - Parm URL 1']/reportVersion[last()]/output&cv.header=false&cv.toolbar=false">

HTML Item 2:</a>

Step 6: Run and save the output for the parent report. Run and burst the outputs for the child report.







Clicking on Camping Equipment in the above saved output displays the below saved Camping Equipment output in the same window.



And clicking on the Camping Equipment link in the child output takes you back to the parent report displayed in the same window.

You may notice that as you navigate back and forth between the 2 reports, the left side margin space increases pushing your report to the left and scroll bars appear in your reports now. To avoid this use a target="_parent" tag in your HTML items. If folks are interested then I will blog about why this seems to be happening.

Using CAMID for Bursting

To burst reports to Cognos Directories that need to be visible to all users with access to the respective folder / report, you would need to burst to the Everyone group.

Retrieve the CAMID of everyone group and create a data item with the CAMID included as an expression. Set this as the recipient data item.

'CAMID("::Everyone")'


Likewise you can burst to various groups of users by retrieving their CAMIDs and setting them up as recipients in your database recipient table.