7 Adding Bursting Definitions
About Bursting
Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations.
The data for the report is generated by executing a query once and then splitting the data based on a Key value. For each block of the data, a separate document is generated and delivered.
Using Oracle BI Publisher bursting enables splitting a single report based on an element in the data model and deliver the report based on a second element in the data model. Driven by the delivery element, you can apply a different template, output format, delivery method, and locale to each split segment of the report. Example implementations include:
-
Invoice generation and delivery based on customer-specific layouts and delivery preference.
-
Financial reporting to generate a master report of all cost centers, splitting out individual cost center reports to the appropriate manager.
-
Generation of pay slips to all employees based on one extract and delivered through e-mail.
What is the Bursting Definition?
A bursting definition is a component of the data model. After you have defined the data sets for the data model, you can set up one or more bursting definitions.
When you set up a bursting definition, you define the following:
-
The Split By element is an element from the data that governs how the data is split. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_ID. The data set must be sorted or grouped by this element.
-
The Deliver By element is the element from the data that governs how formatting and delivery options are applied. In the invoice example, it is likely that each invoice has delivery criteria determined by customer; therefore, the Deliver By element would also be CUSTOMER_ID.
-
The Delivery Query is a SQL query that you define for BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details.
Adding a Bursting Definition to Your Data Model
In the Bursting definition table, create a new bursting definition by specifying its name, type, data source, and other properties.
Prerequisites:
-
You have defined the data set for this data model
-
The data set is sorted or grouped by the element by which you want to split the data in your bursting definition
-
The delivery and formatting information is available to Oracle BI Publisher. You can provide the information at runtime to BI Publisher in one of the following ways:
-
The information is stored in a database table available to BI Publisher for a dynamic delivery definition.
-
The information is hard coded in the delivery SQL for a static delivery definition.
-
-
The report definition for this data model has been created and includes the layouts to be applied to the report data.
To add a bursting definition to the data model and enable bursting in your report:
- Enter the following for this bursting definition:
Name - Enter a name for the query. For example, Burst to File.
Type - Select SQL Query. SQL Query is currently the only supported type.
Data Source - Select the data source that contains the delivery information.
Attaching PDF to Reports using Bursting Engine
You may have a requirement to attach PDFs along with invoices for customers. You can now attach these documents as an attachment along with the invoice while bursting.
- Define the SQL query for the attachment in the Content Server.
Note:
The column alias names KEY and ATTACHMENT_ID are mandatory.The SQL query uses the KEY and the ATTACHMENT_ID (which map to the document id of the PDF in the content server) to create an association between the PDF report and the PDF attachment.
Defining the Query for the Delivery XML
The bursting query is a SQL query that you define to provide the required information to format and deliver the report.
BI Publisher uses the results from the bursting query to create the delivery XML.
The BI Publisher bursting engine uses the delivery XML as a mapping table for each Deliver By element. The structure of the delivery XML required by BI Publisher is as follows:
<ROWSET> <ROW> <KEY></KEY> <TEMPLATE></TEMPLATE> <LOCALE></LOCALE> <OUTPUT_FORMAT></OUTPUT_FORMAT> <DEL_CHANNEL></DEL_CHANNEL> <TIMEZONE></TIMEZONE> <CALENDAR></CALENDAR> <OUTPUT_NAME></OUTPUT_NAME> <SAVE_OUTPUT></SAVE_OUTPUT> <PARAMETER1></PARAMETER1> <PARAMETER2></PARAMETER2> <PARAMETER3></PARAMETER3> <PARAMETER4></PARAMETER4> <PARAMETER5></PARAMETER5> <PARAMETER6></PARAMETER6> <PARAMETER7></PARAMETER7> <PARAMETER8></PARAMETER8> <PARAMETER9></PARAMETER9> <PARAMETER10></PARAMETER10> </ROW> </ROWSET>
-
KEY — The Delivery key and must match the Deliver By element. The bursting engine uses the key to link delivery criteria to a specific section of the burst data. Ensure that you use double quotes around "KEY" in the select statement, for example:
select d.department_name as "KEY", -
TEMPLATE — The name of the Layout to apply. Note that the value is the Layout name (for example, 'Customer Invoice'), not the template file name (for example, invoice.rtf).
-
LOCALE — The template locale, for example, 'en-US'.
-
OUTPUT_FORMAT — The output format. For a description of each type, see Selecting Output Formats in Report Designer's Guide for Oracle Business Intelligence Publisher. The table below shows the valid values to enter for the bursting query.
Output Format Value to Enter in Bursting Query Template Types That Can Generate This Output Format Interactive
N/A
Not supported for bursting
HTML
html
BI Publisher, RTF, XSL Stylesheet (FO)
PDF
pdf
BI Publisher, RTF, PDF, Flash, XSL Stylesheet (FO)
RTF
rtf
BI Publisher, RTF, XSL Stylesheet (FO)
Excel (mhtml)
excel
BI Publisher, RTF, Excel, XSL Stylesheet (FO)
Excel (html)
excel2000
BI Publisher, RTF, Excel, XSL Stylesheet (FO)
Excel (*.xlsx)
xlsx
BI Publisher, RTF, XSL Stylesheet (FO)
PowerPoint (mhtml)
ppt
BI Publisher, RTF, XSL Stylesheet (FO)
PowerPoint (.*pptx)
pptx
BI Publisher, RTF, XSL Stylesheet (FO)
MHTML
mhtml
BI Publisher, RTF, Flash, XSL Stylesheet (FO)
PDF/A
pdfa
BI Publisher, RTF, XSL Stylesheet (FO)
PDF/X
pdfx
BI Publisher, RTF, XSL Stylesheet (FO)
Zipped PDFs
pdfz
BI Publisher, RTF, PDF, XSL Stylesheet (FO)
FO Formatted XML
xslfo
BI Publisher, RTF, XSL Stylesheet (FO)
Data (XML)
xml
BI Publisher, RTF, PDF, Excel, Flash, XSL Stylesheet (FO), Etext, XSL Stylesheet (HTML XML/Text)
Data (CSV)
csv
BI Publisher, RTF, PDF, Excel, Flash, XSL Stylesheet (FO), XSL Stylesheet (HTML XML/Text), Etext
XML
txml
XSL Stylesheet (HTML XML/Text)
Text
text
XSL Stylesheet (HTML XML/Text), Etext
Flash
flash
Flash
-
SAVE_OUTPUT — Indicates whether to save the output documents to BI Publisher history tables that the output can be viewed and downloaded from the Report Job History page.
Valid values are 'true' (default) and 'false'. If this property is not set, the output is saved.
-
DEL_CHANNEL — The delivery method. Valid values are:
-
EMAIL
-
FAX
-
FILE
-
FTP
-
PRINT
-
WEBDAV
-
WCC
-
ODCS
-
-
TIMEZONE — The time zone to use for the report. Values must be in the Java format, for example: 'America/Los_Angeles'. If time zone is not provided, then the system default time zone is used to generate the report.
-
CALENDAR — The calendar to use for the report. Valid values are:
-
GREGORIAN
-
ARABIC_HIJRAH
-
ENGLISH_HIJRAH
-
JAPANESE_IMPERIAL
-
THAI_BUDDHA
-
ROC_OFFICIAL (Taiwan)
If not provided, the value 'GREGORIAN' is used.
-
-
OUTPUT_NAME — The name to assign to the output file in the report job history.
-
Delivery parameters by channel — The values required for the parameters depend on the delivery method chosen. The parameter values mappings for each method are shown in the table below. Not all delivery channels use all the parameters.
| Delivery Channel | PARAMETER Values |
|---|---|
|
|
PARAMETER1: Email address PARAMETER2: cc PARAMETER3: From PARAMETER4: Subject PARAMETER5: Message body PARAMETER6: Attachment value ('true' or 'false'). If your output format is PDF, you must set this parameter to "true" to attach the PDF to the e-mail. PARAMETER7: Reply-To PARAMETER8: Bcc (PARAMETER 9-10 are not used) |
|
Printer |
PARAMETER1: Printer group PARAMETER2: Printer name or for a printer on CUPS, the printer URI, for example: PARAMETER3: Number of copies PARAMETER4: Sides. Valid values are:
If the parameter is not specified, single-sided is used. PARAMETER5: Tray. Valid values are:
If not specified, the printer default is used. PARAMETER6: Print range. For example "3" prints page 3 only, "2-5" prints pages 2-5, "1,3-5" prints pages 1 and 3-5 (PARAMETER 7-10 are not used) |
|
Fax |
PARAMETER1: Fax Server Name PARAMETER2: Fax number (PARAMETER 3-10 are not used) |
|
WebDAV |
PARAMETER1: Server Name PARAMETER2: Username PARAMETER3: Password PARAMETER4: Remote Directory PARAMETER5: Remote Filename PARAMETER6: Authorization type, values are 'basic' or 'digest' (PARAMETER 7-10 are not used) |
|
File |
PARAMETER1: Directory PARAMETER2: File Name (PARAMETER 3-10 are not used) |
|
FTP and SFTP |
PARAMETER1: Server Name PARAMETER2: Username PARAMETER3: Password PARAMETER4: Remote Directory PARAMETER5: Remote Filename PARAMETER6: Secure (set this value to 'true' to enable Secure FTP) (PARAMETER 7-10 are not used) |
|
WCC |
PARAMETER1: Server Name PARAMETER2: Security Group PARAMETER3: Author PARAMETER4: Account (Optional) PARAMETER5: Title PARAMETER6: Primary File (or File Name) PARAMETER7: Comments (Optional) PARAMETER8: Content ID (Optional. Content ID must be unique.) PARAMETER9: Custom Metadata |
|
Document Cloud Services |
PARAMETER1: Server Name PARAMETER2: Folder Name PARAMETER3: File Name |
Passing a Parameter to the Bursting Query
You can pass the value for an element of your bursting XML using a parameter defined in the data model.
For example, if you want to be able to select the template at the time of submission, you can define a parameter in the data model and use the :parameter_name syntax in your query. The following example demonstrates this use case of a parameter in a bursting query.
Assume your report definition includes three layouts: layout1, layout2, and layout3. At submission time you want to select the layout (or TEMPLATE, as defined in the bursting query) to use. In your data model, define a list of values with the layout names. The following figure shows a data model with the layout list of values:
Next create a menu type parameter, here named P1:
In the bursting query, pass the parameter value to the TEMPLATE field using :P1 as shown in the following figure:
Defining the Split By and Deliver By Elements for a CLOB/XML Data Set
If the split-by and deliver-by elements required for your bursting definition reside in a data set retrieved from a CLOB column in a database, BI Publisher cannot parse the XML to present the elements in the Split By and Deliver By lists.
You therefore must manually enter the XPath to locate each element in the retrieved XML data set. To ensure that you enter the path correctly, use the data model editor's Get XML Output feature to view the XML that is generated by the data engine.
For example, the sample XML code, shown in the figure below, was stored in a CLOB column in the database called "XMLTEXT", and extracted as an XML data set:
For this example, you want to add a bursting definition with split by and deliver by element based on the DEPARTMENT_ID, which is an element within the CLOB/XML data set.
When you add the bursting definition, the Split By and Deliver By lists cannot parse the structure beneath the XMLTEXT element. Therefore, the list does not display the elements available beneath the XMLTEXT node, as shown in the figure below.
To use the DEPARTMENT_ID element as the Split By element, manually type the XPath into the field as shown in the figure below.
Configuring a Report to Use a Bursting Definition
Although you can define multiple bursting definitions for a single data model, you can enable only one for a report.
Enable a report to use a bursting definition on the Report Properties dialog of the report editor. See Configuring Report Properties in Report Designer's Guide for Oracle Business Intelligence Publisher.
After you configure the report to use the bursting definition, when you schedule a job for this report you can choose to use the bursting definition to format and deliver the report. See Creating a Bursting Job in User's Guide for Oracle Business Intelligence Publisher .
You can also opt not to use the bursting definition and choose your own output and destination as a regular scheduled report.
Sample Bursting Query
This example of a bursting query is based on an invoice report. This report is to be delivered by CUSTOMER_ID to each customer's individual e-mail address
This example assumes that the delivery and formatting preferences for each customer are contained in a database table named "CUSTOMERS". The CUSTOMERS table includes the following columns that will be retrieved to create the delivery XML dynamically at runtime:
-
CST_TEMPLATE
-
CST_LOCALE
-
CST_FORMAT
-
CST_EMAIL_ADDRESS
The CUSTOMER_ID will be used as the KEY and also to define the output file name.
The SQL code to generate the delivery data set for this example is as follows:
select distinct CUSTOMER_ID as "KEY", CST_TEMPLATE TEMPLATE, CST_LOCALE LOCALE, CST_FORMAT OUTPUT_FORMAT, CUSTOMER_ID OUTPUT_NAME, 'EMAIL' DEL_CHANNEL, CST_EMAIL_ADDRESS PARAMETER1, 'accounts.receivable@example.com' PARAMETER2, 'bip-collections@example.com' PARAMETER3, 'Your Invoices' PARAMETER4, 'Hi'||CUST_FIRST_NAME||':'|| 'Please find attached your invoices.' PARAMETER5, 'true' PARAMETER6, 'donotreply@mycompany.com' PARAMETER7 from CUSTOMERS
Creating a Table to Use as a Delivery Data Source
If the delivery information is not easily available in the existing data sources, then you can consider creating a table to use for the query to create the delivery XML.
Following is a sample:
Note:
If the JDBC driver that you use does not support column alias, when you define the bursting control table, the columns must match exactly the control XML tag name. For example, the KEY column must be named KEY, upper case is required. PARAMETER1 must be named PARAMETER1, not parameter1 nor param1, or any other non-matching name.
CREATE TABLE "XXX"."DELIVERY_CONTROL"
( "KEY" NUMBER,
"TEMPLATE" VARCHAR2(20 BYTE),
"LOCALE" VARCHAR2(20 BYTE),
"OUTPUT_FORMAT" VARCHAR2(20 BYTE),
"DEL_CHANNEL" VARCHAR2(20 BYTE),
"PARAMETER1" VARCHAR2(100 BYTE),
"PARAMETER2" VARCHAR2(100 BYTE),
"PARAMETER3" VARCHAR2(100 BYTE),
"PARAMETER4" VARCHAR2(100 BYTE),
"PARAMETER5" VARCHAR2(100 BYTE),
"PARAMETER6" VARCHAR2(100 BYTE),
"PARAMETER7" VARCHAR2(100 BYTE),
"PARAMETER8" VARCHAR2(100 BYTE),
"PARAMETER9" VARCHAR2(100 BYTE),
"PARAMETER10" VARCHAR2(100 BYTE),
"OUTPUT_NAME" VARCHAR2(100 BYTE),
"SAVE_OUTPUT" VARCHAR2(4 BYTE),
"TIMEZONE" VARCHAR2(300 BYTE),
"CALENDAR" VARCHAR2(300 BYTE)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EXAMPLES";
Tips for creating a creating bursting delivery table:
-
If the split data set does not contain a DELIVERY_KEY value, then the document is neither delivered nor generated. For example, using the preceding example, if customer with ID 123 is not defined in the bursting delivery table, this customer's document is not generated.
-
To enable a split data set to generate more than one document or deliver to more than one destination, duplicate the DELIVERY_KEY value and provide different sets of
OUTPUT_FORMAT,DEL_CHANNEL, or other parameters. For example, customer with ID 456 wants his document delivered to two e-mail addresses. To achieve this, insert two rows in the table, both with 456 as the DELIVERY_KEY and each with its own e-mail address.