A techy guide to implementing bursting and overcoming the pitfalls to ensure you are bursting with success!
- What is “bursting” reports in IBM Cognos Report Studio?
- How to burst a crosstab or chart (without nesting inside a list!)
- Bursting on more than one key
- Varying the Burst Key (Using a Macro Prompt)
- Bursting on CAMID
- Bursting with an email address from the package
- Bursting with an email address not from the package
- Bursting each file to more than one recipient
- Renaming the email attachment file to include the burst key name
- Have each burst recipient getting multiple different attachments on the same email
- Bursting Output Only When There is Data (Useful for Exception Reporting)
- Bursting Capability Considerations
- Speeding up a burst report
- Bursting outputs to a printer
Bursting allows you run a report once and have the results distributed into separate files, one file for each value in a given field. For example, it allows you to run the sales report once and have the results automatically output to one file per sales area.
This process requires…
- Deciding which field will form the basis of separating the files (e.g. one file per sales area? product grouping? cost centre? business unit? etc.). This is called a “burst key”.
- If bursting the reports to an email address or CAMID (CAMID is a method that restricts which users can see which output when they view it within Cognos Connection, based on authentication against their Cognos login), deciding how to correlate the burst key to a set of email addresses. This is the “burst email address or CAMID”.
- Deciding on how to name the files so to distinguish between them effectively. This is called the “burst label“. Typically, this will be the report name plus a suffix of the burst key, e.g. a report called ‘Sales Report by Area’ might have outputs ‘Sales Report by Area – Area 001’, ‘Sales Report by Area – Area 002’ etc.
It is widely understood that you can only burst a list. So where does this leave reports with crosstabs and charts in them?!? Such reports do error when trying to burst them. There is an article from IBM on how to burst a crosstab (the principle of which can also apply to a chart). It’s on this link. It basically says to nest the crosstab or chart into a list and then use varies techniques to hide the appearance of the list. I have been following this guideline for years. Whilst the outcome looks fine, it’s pretty impractical as it makes the report design more complicated than it needs to be. You’ll be pleased to know there is another way to burst crosstabs and charts without nesting inside a list! I can’t believe it was this simple… Basically you’re allowed to burst a ‘Page’ too. So assign a query to the page and create a master-detail relationship between the page and all the objects on it. Here’s how to do this step-by-step.
1. Make sure that ‘Page’ is selected in the properties pane (figure 1a). Find the ‘Query’ property. Change it to the query with the burst query items in it (such as burst key, burst label and burst recipients). In my example, I have a query called ‘Crosstab Query’ which contain items used in my crosstab as well as query items for the burst options.
Figure 1a: Put a query on the page which allows you to burst a page.
2. If the burst key doesn’t feature in your crosstab layout and you want it to stay that way then find the crosstab and make the burst key a property of one of the rows (figure 1b). If you are using a chart, then make it a property of the x axis node
Figure 1b: If you don’t want the burst key to appear in your crosstab, make it a property of the crosstab so that the master-detail relationship to the list will work.
3. Make sure that ‘Crosstab’ is selected within the properties pane. Create a master-detail relationship between the query against the page and the query against the crosstab, linking on the burst key. (Figure 1c)
Figure 1c: Master-detail between page and crosstab
4. Do this for every object on the page.
5. Burst away!
Often the burst key and the burst label are the same or pretty similar e.g. you split the files by country (burst key) and you want to append country name in the file name (burst label). Most burst keys will consist of one key (i.e. just sales area or just a cost centre). Bursting can be done on more than one key if the given burst key appears in more than one place in the hierarchy, for example, a salesperson with the area number ‘001’ who operates across more than one region (e.g. he/she operates in Division ‘North’ and ‘South’). If he/she wants a separate file for North and a separate file for South, this would warrant more than one burst key, so bursting on ‘Salesperson’ and ‘Region’. The software only allows you to choose one item so in this instance you would concatenate the two fields into one item, e.g. ‘North – Area 001’, ‘South – Area 001’. This will ensure that they burst into two separate files.
CAUTION: If you don’t concatenate when bursting to a file system, the files will have the same name – whichever output runs second in the sequence will overwrite the one run first.
Sometimes you may have a sales report that needs bursting one for each salesperson and also one for each regional manager. To avoid creating two separate reports, one with a burst key of ‘salesperson’ and another with a burst key of ‘region’ you can use a macro prompt for the burst key which is populated from a prompt on the prompt page. To see an example of a report structure that uses this, open this file, copy the contents, then open report from clipboard in report studio:
Burst key using macro prompt doc
Bursting on CAMID means you are bursting to a specific person's Cognos login. ID rather than just an email address. It's provides the same functionality as bursting to an email address (provided that there is an email address against the user profile in LDAP / Active Directory) but you have the additional option to save the burst output within Cognos Connection and then the relevant users go in to Cognos and can only see the output for which their login is secured to see. Each user, group, distribution list etc. in Cognos Connection has a CAMID. Simply find the object in the security tab in Cognos Administration, click on properties and then ‘View Search Path, ID and URL’. The search path is the CAMID. Each user authenticates within Cognos Connection with this underlying ID.
Figure 2: Find the CAMID of the user, group, distribution list etc. by going into Cognos Administration > Security > Users, Groups and Roles > click on the relevant person, group etc > View the search path, ID and URL.
In this example, we’ve burst a report one per country and saved in Cognos Connection. The saved outputs are saved against the report.
Figure 3a: How to view saved report outputs within Cognos Connection.
Figure 3b: An administrator who can see everything would see all countries as follows:
Figure 3c: However, there are three sales people responsible for these countries: M.Marceau, R.Biebrach and P.Frisch as follows:
By using CAMID instead of email address, these three people have a different view of the saved report output page as per below.
Figure 3d: M.Marceau’s view…
Figure 3e: R Biebrach’s view…
Figure 3f: P.Frisch’s view….
Firstly, you will need a table containing the burst key and the relevant CAMID’s and bring this into your data model (transformer, TM1, cube designer, framework manager etc).
It doesn’t necessarily need to be joined directly to your other data within your data model, this table can sit in a standalone query which is only referred to in the burst options with a master-detail relationship to your other data. The ‘Type’ field will be ‘Directory Entries’ as per below.
Figure 4: Example of Burst Options for bursting on CAMID
In Cognos Connection click ‘Run with options’ > ‘Advanced’. Ensure the delivery method is ‘save’ and ‘burst the report’ ticked.
Figure 5: Example of Run with Options menu when bursting on CAMID
View the saved output files as per figure 3.
If you want the burst with an email address from the package, you’ll need to make sure you’ve got it modelled/joined to the burst key in the framework/transformer/dynamic cube model. There’s many pitfalls with bursting to email addresses in terms of where best to source it from, maintenance, human error, etc. So here’s a guide to what works well and what can go wrong…
- Option 1: Find email address within the database. If you’re fortunate enough for your ERP system or other database to store the burst key and email address in one table (like area name, salesperson, salesperson email address all lined up) then simply bring the data in from there. But I’ve found that’s not often the case, so you’ll have to get inventive! Other options below are ways of creating the assembling the email address against a burst key…
- Option 2: Keep a manual list of email addresses mapped to the burst key. This is all well and good until someone can’t spell ‘[email protected]’ properly and then you’ll need to burst out just her reports all over again. For a large organisation with many people and lots of sales areas which change regularly, believe me, this is a regular occurrence. Hopefully your report design is catered to deal with that eventuality (i.e. you can select just her area from the prompt page).
- Option 3: Concatenating people’s names with an email suffix (not perfect). If you’ve got the burst key and person’s name modelled together, then you can concatenate their names with the substring ‘@companyname.co.uk’. This can cause problems due to name variations. The ERP system might have ‘Mike Smith’ in the system with ‘Area 001’ against him, so you could remove the space and add the email address component to make ‘[email protected]’ however, his real email address might be ‘[email protected]’. A more sustainable options might be email aliases or group names in outlook which are less problematic (see next bullet point).
- Option 4: Concatenate the burst key with the email suffix. Create new email addresses which are in the format of ‘<burst key>@companyname.co.uk’ so that you only need the burst key in the model to achieve the email address. Assign the relevant recipients to the relevant email address. You can do this with email aliases or outlook distribution groups. e.g. salesperson with area code ‘001’ could be part of an email distribution group ‘001’ and hence the email address for that group would be ‘[email protected]’. This is great because that way you can have more than recipient against a particular area. Putting this in outlook allows the administration of changes to be in the hands of the business rather than business intelligence team. This is easier if you have all the outlook groups using a standardised email suffix. If not, you may want to try force standardisation or find some logic to case statement for which suffix to use.
Figure 6: Example of burst key (in this example, country level) concatenated with email suffix, e.g. [email protected], [email protected] etc. ‘France’ and ‘Germany’ have been set up as distribution groups in Aramar’s directory. Email addresses can’t have spaces so you’ll have to remove spaces in countries like ‘Czech Republic’. Sometimes it’s easier to use a code like ‘CZRB’ rather than the description to remove the need for ambiguity.
In the data item, type an email address followed enclosed in single quotes and use this as the burst recipient email address. This is good for sending it to yourself during testing before changing it to the real email addresses when put live. You can’t put more than one in here sadly. So if you need it to go to more than one, then use an outlook distribution group in email format like the principle described in figure 6.
Figure 7: Bursting to an email address typed into a data item rather than from somewhere in the package.
Often people assume that you can burst to just one email address. It is indeed possible to burst to more than one person. Options available:
- In the query item containing the recipients ensure that each email address is separated with a comma without spaces. Then in the burst options menu ensure that ‘email addresses’ is selected and NOT ‘Directory Entries’ or ‘Automatic’.
- Burst to an email distribution list. If you have an email distribution list called ‘area001’ then the email address is ‘[email protected]’ which can be used as a burst recipient.
- Create a distribution list within Cognos and burst to the CAMID of that distribution list (see figure 7a). To find the CAMID, see section further below called ‘Bursting on CAMID’.
Figures 7a: Creating a distribution list maintained within Cognos
If you burst a report to many email recipients, it has the same file name and email subject on each email (see figure 8).
Figure 8: Bursting to email gives the same file name and email subject.
So if one person gets two emails they may have to open the reports to work out which one is which. There is a way to get the attachment and email subject to contain the burst key using event studio. Firstly, set up an event condition that will always be met so that it will run every time. My example is below.
Figure 9: The event condition in Event Studio. Use this expression window to filter which burst keys to run. If there is more than one condition/filter then use “AND” between them.
Then set up a task to ‘run a report’
Figure 10: Setting up an event studio task to run a report
The run report options must have ‘save’ as the delivery method this is where the next step takes the file from.
Figure 11: Setting up the run report options within event studio. For this particular scenario, you must tick ‘save’.
Finally set up a task to email and attach the report.
Figure 12: Setting up an event studio task to send an email.
Figure 13: Attach the report from the ‘run a report’ task
For the email subject, type the report name, in this example ‘Sales by Media Type and Genre – ‘, then drag on your burst key at the end from insertable objects. If the burst key doesn’t exist in your insertable objects, then drag on the burst key into your event condition expression and then delete the text you just dragged on.
Figure 14: Customising the Email Subject to contain the burst key in event studio
For the email recipients, you need to use the same data item that you used as the burst email used in your burst options menu. If your email didn’t come directly from the package then you can create a calculation. In this example, the email was a concatenation of country and an email suffix so right click in insertable objects pane > new calculation. You must ensure that each email address is different otherwise each recipient gets all email attachments on the same email.
Figure 15: Customising the burst email address using event studio
Drag this item into the ‘to’ field.
Figure 16: Setting the burst email address in event studio
Amend the task execution rules as appropriate, save the event studio condition, and then run. Files now have country in the email subject and file attachment name.
Figure 17: The resulting now have the burst key in the email subject and email attachment name.
Out of the box, bursting can attach one file to an email unless you’re attaching in multiple formats (like PDF and Excel) then it can attach both formats on one email. If you want to attach more than one different report to an email then you can use event studio.
To achieve this, set up something an event studio similar to that described in section ‘Renaming the email attachment file to include the burst key name’. This time, add another ‘Run a report’ task for the second report you wish to attach. In this example ‘Quantity by Media Type and Genre’.
Figure 18: Adding a second report task in Event Studio as a precursor to making it a second attachment to the burst email
Add the second attachment on the email task
Figure 19: Attaching a second report to a burst email in Event Studio.
Two attachments should now appear on the email.
Figure 20: Demonstration of the final setup of what ‘Send an email’ task should look to achieve two burst report attachments in one email using event studio
You may need to reorder the tasks so that both reports run before the email is sent out.
Figure 21: How to reorder tasks in Event Studio
Now the task order appears as below, with the email task at the end.
Figure 22: The result of reordering the tasks. This is the final stage in setting up this Event Studio instance.
The email is sent with two attachments from different reports.
Figure 23: Two burst emails on one report using Event Studio
Sometimes burst reports can be empty if there are no figures to display. If this is a report to highlight exceptions then it can be tedious for the recipient to get an empty report most days. It can be more effective to send the report out only when data appears. See attached report example.
Bursting only where there are figures doc
The attached report achieves this by having two queries feeding the main figures on the report. One is a detailed query going down to country, genre and media type level which supplies figures to the crosstab. The outer list is based on another query which just goes down to country level and is joined on a master detail relationship. The outer list query supplies the data items for the burst options. In this query you can restrict which burst keys appear by a filter which only displays those that have a figure against them using a simple filter like measure > 0.
Figure 24: How to set up a report studio report to dynamically filter burst keys which have data. This example is using dimensional data but the same principle applies to relational.
Not all users have the rights to ‘burst’ reports by default. This is defaulted to be in the hands of administrators. Review capabilities and decide who can have this facility. If your BI environment is pretty big and complex, then you might want to only restrict this to administrators anyway. This is because bursting can take considerable time depending on how long it takes to run the report and how many files you need to burst out. Depending on how you have Cognos Connection set up you might only be able to run 3 reports in the background at one time. So if you’re bursting a report that takes 3 hours to complete and you run it during the daytime, you might be hogging up all the resource and no-one else will be able to run any reports in the background. This will lead to a backlog of reports pending and waiting to run for hours. Tight controls over who can burst reports and when is advisable.
The amount of time it takes to run one report is critical, even if it’s only 30 seconds, it can be disastrous. The reason being that you may need to burst 1,000 files (like one per cost centre) which amounts to over 8 hours. If this report failed during the burst or came out with incorrect figures then it may need rerunning. Consequently, if it’s an important report that needs to come out that day it can mean another 8 hours before the report comes out again. This delays the distribution by a day and could be a high profile issue for those who rely on the information to do their work on time.
Work out a reasonable time frame for bursting a report and divide up by the number of files required for the burst (now and growth potential in the future) and come up with a target run time per report. Some options for coping with or speeding up long running bursts:
- Burst out of hours or in quiet periods.
- If you have an emergency burst you need to do as a one off during the day then burst on a dev/test Cognos Connection environment (not ideal as you’ll have to burst to a dev file location and then copy the files to a live location but good for emergencies!)
- Redesign the report to run quicker. This can be difficult and you might need to go back to the drawing board. Couple of things to try based on experience (and desperation!):
- If relational report…
- Review the data mart design:
- Creating a summary table with just the burst data in it required by the report.
- Review table indexes.
- Ensure all joining fields affected by the report are based on surrogate and foreign keys as integers or GUIDs – these are faster joining fields than char or varchar fields.
- Review report studio and framework manager design:
- Ensure all joining fields and fields being filtered don’t have functions applied to them in any way in report studio or framework manager, this will render the index speed benefits null and void.
- If dimensional report…
- Review cube design:
- If the cube is big and slow – create a smaller cube with just the relevant information in it required by the report.
- If nesting two large dimensions together in the report – review if they can be combined into one dimension.
If you’re not yet in the digital age and need to distribute the reports in printed format, don’t torture some poor person into printing off 1,000 PDFs and assembling them manually. Simply select the “Print the report” option in the advanced run with options menu. Many fancy printers now have the ability to print ready-stapled! See figure 25 below for where this run option exists. To set up a printer you’ll need to read the installation and administration guide from IBM’s webpage.
Figure 25: Example of Run with Options menu when bursting to a printer