BI Publisher Bursting

Reports plays important part for management. It gives a birds eye view for a set of data, which allows the top management to take business decisions. BI publisher as a tool itself provides the reporting capability for OIM.

Simple reports can be created by creating a data model which are backed by SQL; and delivering the report to the required participant by configuring destination under output as follows:

But what if we want to deliver the single report by splitting it into different reports containing relevant data to the individual ? Bursting is the solution.

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.

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 via 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 will govern 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 will govern how formatting and delivery options are applied. In the invoice example, it is likely that each invoice will have delivery criteria determined by customer, therefore the Deliver By element may 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

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 BI Publisher. The information can be provided 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 that will be applied to the report data.

To add a bursting definition

1. On the component pane of the data model editor, click Bursting.
2. On the Bursting definition table, click the Create new Bursting button.
3. Enter the following for this bursting definition:Name – for example, “Burst to File”Type– SQL Query is currently the only supported typeData Source – select the data source that contains your delivery informationThe following figure shows a Bursting definition:

 Enabling bursting in the report:

1. Click on Edit–> Properties of the report.
2. Check “Enable Bursting” checkbox in Advanced and select bursting to apply and hit OK.

Use case:

Consider a scenario where in new joinees need their userlogin and email id, and other information from their immediate manager. The respective reportee will get the relevent information using following report with bursting feature enabled.

Sample Data Model SQL:

SELECT
    usr_login,
    usr_email
FROM usr
WHERE
    usr_manager_key = (
        SELECT
            usr_key
        FROM
            usr
        WHERE
            usr_login =:mgr_login
        )

Sample email bursting query:

SELECT DISTINCT
     ( "User Login" ) AS "KEY",
     'My Template' template,
     'en-US' locale,
     'pdf' output_format,
     "User Login" ||'_'|| TO_CHAR(SYSDATE,'YYYYMMDD_HH:MM:SS') output_name,
     'EMAIL' del_channel,
     "USR_EMAIL"   parameter1,
     'Chaitanya.idm@gmail.com' parameter2,
     '' parameter3,
     'EMAIL_SUBJECT' parameter4,
     'Hi '
     || "User Login"
     || CHR(13)
     || 'Please find attached Users for the update.' parameter5,
     'true' parameter6,
     'chaitanya.idm@gmail.com' parameter7
 FROM
     (
         SELECT
             usr_login   "User Login",
             usr_email
         FROM
             usr
         WHERE
             usr_manager_key = (
                 SELECT
                     usr_key
                 FROM
                     usr
                 WHERE
                     usr_login =:mgr_login
             )
     )
 ORDER BY
     "User Login"

Sample folder bursting Query:

SELECT DISTINCT
     ( usr_login ) AS "KEY",
     'CW Disable Manager' template,
     'en-US' locale,
     'RTF' template_format,
     'PDF' output_format,
     'FILE' del_channel,
     '/tmp/rp' parameter1,
     usr_login||'_'

     || TO_CHAR(SYSDATE,'YYYYMMDD_HH:MM:SS')
     || '.pdf' parameter2
 FROM
     (
         SELECT
             usr_login,
             usr_email
         FROM
             usr
         WHERE
             usr_manager_key = (
                 SELECT
                     usr_key
                 FROM
                     usr
                 WHERE
                     usr_login =:mgr_login
             )
     )
 ORDER BY
     usr_login

Example screenshot:

Reference:

https://docs.oracle.com/cd/E21764_01/bi.1111/e18862/T527073T555155.htm#T559968

https://docs.oracle.com/middleware/12212/bip/BIPDM/GUID-A926D588-426F-47C7-8A9A-B0A7959C5814.htm#BIPDM344