To continue with one of my previous posts on creating templates the easy way in BI Publisher Enterprise, let’s talk about bursting in BIP. If you are familiar with the bursting control file concept in EBS, Oracle has set up BI Publisher Enterprise a bit differently. There are quite a few resources out there on the web that go into EBS bursting control files in more detail, so I’ll stay on topic for now.
Using the Bursting Properties screen (shown below), there are 4 steps to follow to enable and configure bursting and delivery from BI Publisher for a specified report.
- Enable Bursting: The easy to explain part -> Click on the ‘Enable Bursting’ checkbox!
- Split By: Choose how you want to split/burst the data. Clicking on this drop-down you will be presented with an XPath representation of your XML data. Choose the data element that you want BIP to split the final output on.
- For example, if I’m sending out invoices to customers, I may want to create a new PDF file for each customer in my data, so I would choose to split on the Customer ID. While processing the XML, every time BI Publisher encounters this element, and the value is different than the previous, a new document will be created.
- Deliver By: Choose the ‘KEY’ to deliver the data by. Clicking on this drop-down you will again be presented with an XPath representation of your XML data. The data element you choose here will be used to determine which row in the following delivery query’s output will be matched with the document that is currently being burst. I know this really sounds confusing at the moment, but stick with me through this next part….
- Delivery Data Source: Finally, you need to create a SQL query that returns how you want to deliver the data. This query needs to return the following columns:
- KEY – This value much match the value of the unique identifier that you chose for the ‘Deliver By’ field in step 3.
- For example: for a data set that includes overdue invoices sorted by customer, you may want to deliver the data based on unique customer settings. When writing the query, you want to return the customer id for the KEY column. This means that your 'Deliver By' field must be the XML element that represents the customer id so that BIP can match them up.
- TEMPLATE – Name of the template to apply as setup in BIP Enterprise
- TEMPLATE_FORMAT – The format of the template (i.e. RTF/PDF/etc…)
- LOCALE – Locale of the template
- OUTPUT_FORMAT – How you want the final published output to be formatted. (i.e. PDF, HTML, Excel, etc….)
- DEL_CHANNEL – The delivery channel to use to send the newly burst and formatted data through.
- PARAMETERS1-10 – These fields define the parameters for the specified delivery channel. i.e. email server, email address, file path, etc…. See the BIP documentation for more detail.
- KEY – This value much match the value of the unique identifier that you chose for the ‘Deliver By’ field in step 3.
An example always helps me visualize how things work.
In my previous post regarding Starter Templates, I created a quick report that would output all customers who have overdue invoices and how much they owe. Here is a sample:
Now I want to deliver the report to each customer, but only show them THEIR overdue invoices, not everyone else's! Each customer may have a different mechanism through which they would like me to communicate information to them. In this case, let’s say that Acme Corp would prefer to receive emails and Zeta Co prefers faxes. I have all of this information in my database so I can write a delivery query that will send Acme Corp’s information to their email address and Zeta Co’s information to their fax number.
Here is my customer table:
Here is my BIP setup for Bursting:
Note, once run, this query will return 2 rows (You’ll note that I hardcoded a few parameters as well -> These can certainly be dynamic!):
After splitting the XML data using the ‘Split By’ field into individual XML files, BI Publisher will run this query and based on the ‘Deliver By’ field in the XML file, will match up the appropriate row above returned by the query. So for the customer ‘Acme Corp’ – Customer ID 1, the values will be used to send an email to invoices@acmecorp, and for the customer ‘Zeta Co’ - Customer ID 2, a fax will be sent to 804-897-1600. Each customer will ONLY receive THEIR information because I set up my 'Split By' field to be the customer_id.
A fairly simple example, but I think it gets the point across. The ability to query is actually very powerful allowing you to deliver documents via different channels with dynamic recipient information for each document in a batch. You can even make the output type, the actual look and feel of the document, and locale information all dynamic based on values in your database.