Bursting is a process of splitting data into blocks, generating documents/output for each block and delivering the documents/output 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 (e.g. buyer, customer) value. A separate output/document is generated and delivered for each block of the data.
Most of the BI products offer the report bursting option which can split a single report based on specific key value and deliver reports driven by delivery element. Most of tools support different template, output format and delivery method and local to each split segment of report. Some of the practical uses of report bursting are as follows
· 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
The purpose of this blog is to discuss report bursting option provided by BI Publisher in general and BI Publisher 11.1.1.6.0 in particular. There a significant difference between how BI Publisher report bursting mechanism worked in earlier version of BI Publisher and BI Publisher 11.1.1.6.0.
Oracle BI Publisher Enterprise Edition
The BI Publisher data to destinations architecture is shown below. The key points of BI Publisher reporting architecture are as
· Supports multiple data sources
· Separate layout and translation from data logic
· Supports multiple report layouts and output formats and destinations
Bursting Overview
In short report bursting is the process of generating multiple documents from the same report and delivering each document to a different destination.
Key Concepts for Report Bursting
Split-by key 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, CUSTOMER_ID can be used as element. The data set must be sorted or grouped by this element. In short it determines how data is split.
Deliver-by key 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. In short it determines delivery channel, document layout and output format etc.
Bursting Query (BI Publisher Enterprise) 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. In short it is used to retrieve bursting information from database via a SQL query.
Bursting Control File (EBS) is a control file which has been created manually; it is used to store bursting information. It is not supported in BI Publisher Enterprise.
Report Bursting BI Publisher 10g and Earlier Versions
Report bursting option is available with EBS integrated BI Publisher and standalone BI Publisher Enterprise Edition. In EBS integrated mode uses control files as a delivery definition and then linked it with report. It uses concurrent manager to schedule and burst reports. The EBS bursting is not in the scope of this blog.
In BI Publisher Enterprise Edition the Bursting (or delivery) query is defined in the report definition. The option to burst option is selected while scheduling the report. A sample example is shown below which has employee query which has split by department name is used as source while the delivery query is set of parameters where Email has been specified as delivery channel.
The delivery query supports various channels such as Email, Printer, and Fax etc. The delivery channels and delivery query parameters supported by BI Publisher are as follows.
The bursting option is selected while scheduling the report as shown.
Major Limitations
The BI Publisher Bursting has following major limitations
· Inability to assign name to the output file delivered to file system or email as attachment
· Does not support event based scheduling for report bursting
· The bursting query is defined at report level rather than data model level
· Multiple report bursting query is not supported
· The delivery status may not truly indicate the status of the document.
· Report execution and delivery performance is a major problem for the substantial volumes of outbound documents
BI Publisher 11g Report Bursting
BI Publisher Enterprise bursting query is the only bursting mode supported in 11g. The control file based report bursting is not supported with Fusion Apps. The other major difference from the previous version is that bursting definitions(s) are now supported at data model level rather than report level. Multiple report bursting definitions are possible in the same data model. The bursting is enabled at the report level and it’s used while scheduling the report.
Bursting Definition
The bursting definition includes
Split-by key - Data in data model need to be sorted or grouped by split by key
Deliver-by key
Delivery query – SQL query that specifies template, template type, output format, locale, output name, delivery channel, and delivery parameters (e.g. email address, subject, email body, etc.)
Delivery XML
A delivery XML for Oracle BI Publisher 11.1.1.6.0 is as follows
<ROWSET>
<ROW>
<KEY> </KEY>
<TEMPLATE> </TEMPLATE>
<TEMPLATE_FORMAT> </TEMPLATE_FORMAT>
<LOCALE> </LOCALE>
<OUTPUT_FORMAT> </OUTPUT_FORMAT>
<OUTPUT_NAME> </OUTPUT_NAME>
<TIMEZONE> </TIMEZONE>
<CALENDAR> </CALENDAR>
<SAVE_OUTPUT> </SAVE_OUTPUT>
<DEL_CHANNEL> </DEL_CHANNEL>
<PARAMETER1> </PARAMETER1>
<PARAMETER2> </PARAMETER2>
<PARAMETER3> </PARAMETER3>
<PARAMETER4> </PARAMETER4>
<PARAMETER5> </PARAMETER5>
<PARAMETER6> </PARAMETER6>
<PARAMETER7> </PARAMETER7>
<PARAMETER8> /PARAMETER8>
<PARAMETER9> </PARAMETER9>
<PARAMETER10> </PARAMETER10>
</ROW>
</ROWSET>
Four new tags and 3 more parameters have been added. The four new tags and descriptions are as follows
· OUTPUT_NAME – Name that will be assigned to the output file in the report job history. This also becomes the name of the attachment when emailed.
· TIMEZONE–The time zone to use for the report
· CALENDAR–The calendar type to use for the report
· SAVE_OUTPUT–True or false, whether or not to save the burst output
There are three more parameters have been added, the value of the parameter depends on chosen DEL_CHANNEL. The DEL_CHANNEL and parameters definitions are
Delivery Query Parameter
Working Example of Report Bursting
Setting up bursting is a two step process.
1. Define the bursting criteria on the data model, multiple definitions can be defined in a data model
2. Associate bursting definition with report, a report can only be assigned to one of them
To explain how to achieve the report bursting step by step, an example of customer order detail report bursting from sample schema is depicted below.
Define Bursting Criteria on Data Model
The Customer Order Detail Data model has two SQL source data sets, customer and orders. The master detail relationship is set by making links between the customer data and the orders data by using data model editor. The relationship between two data sets is done based on customer name field. The list of values is defined as customer name.
Customer Data Set Definition is as follows
select "SAMP_CUSTOMERS_D"."NAME" as "NAME",
"SAMP_ADDRESSES_D"."ESTAB_NAME" as "ESTAB_NAME",
"SAMP_ADDRESSES_D"."ADDRESS1" as "ADDRESS1",
"SAMP_ADDRESSES_D"."ADDRESS2" as "ADDRESS2",
"SAMP_ADDRESSES_D"."CITY" as "CITY",
"SAMP_ADDRESSES_D"."STATE_PROVINCE_ABBRV" as "STATE_PROVINCE_ABBRV",
"SAMP_ADDRESSES_D"."POSTAL_CODE" as "POSTAL_CODE",
"SAMP_ADDRESSES_D"."COUNTRY_NAME" as "COUNTRY_NAME",
"SAMP_CUSTOMERS_D"."CUST_KEY" as "CUST_KEY",
"SAMP_ADDRESSES_D"."STATE_PROVINCE" as "STATE_PROVINCE",
"SAMP_ADDRESSES_D"."REGION" as "REGION"
from "BISAMPLE"."SAMP_ADDRESSES_D" "SAMP_ADDRESSES_D",
"BISAMPLE"."SAMP_CUSTOMERS_D" "SAMP_CUSTOMERS_D"
where "SAMP_CUSTOMERS_D"."ADDRESS_KEY"="SAMP_ADDRESSES_D"."ADDRESS_KEY"
and "SAMP_CUSTOMERS_D"."NAME" IN (:P_NAME)
order by "SAMP_CUSTOMERS_D"."NAME" ASC
Order Data Set Definition is as follows
select "SAMP_REVENUE_F"."CUST_KEY" as "CUST_KEY",
"SAMP_REVENUE_F"."ORDER_NUMBER" as "ORDER_NUMBER",
"SAMP_PRODUCTS_D"."BRAND" as "BRAND",
"SAMP_PRODUCTS_D"."PROD_DSC" as "PROD_DSC",
"SAMP_REVENUE_F"."UNITS" as "UNITS",
"SAMP_REVENUE_F"."REVENUE" as "REVENUE",
"SAMP_REVENUE_F"."ORDER_DAY_DT" as "ORDER_DAY_DT",
"SAMP_REVENUE_F"."BILL_DAY_DT" as "BILL_DAY_DT",
"SAMP_REVENUE_F"."PAID_DAY_DT" as "PAID_DAY_DT",
"SAMP_REVENUE_F"."ORDER_STATUS" as "ORDER_STATUS",
"SAMP_REVENUE_F"."CURRENCY" as "CURRENCY"
from "BISAMPLE"."SAMP_PRODUCTS_D" "SAMP_PRODUCTS_D",
"BISAMPLE"."SAMP_REVENUE_F" "SAMP_REVENUE_F"
where "SAMP_PRODUCTS_D"."PROD_KEY"="SAMP_REVENUE_F"."PROD_KEY"
The report bursting definition for file and email deliveries are created for the Customer Order Detail Data Model, the bursting definition and associated SQL’s are below.
Burst to Email
select "SAMP_CUSTOMERS_D"."NAME" as "KEY",
'Orders Letter' TEMPLATE,
'XPT' TEMPLATE_FORMAT,
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT,
"SAMP_CUSTOMERS_D"."NAME"||'_'||'Orders List' OUTPUT_NAME,
'EMAIL' DEL_CHANNEL, ( Delivery Channel – Email)
'to@nowhere.com' PARAMETER1,
'cc@nowhere.com' PARAMETER2,
'from@nowhere.com' PARAMETER3,
'SUBJECT:BI Publisher Enterprise Bursting Report' PARAMETER4,
'BODY: Email Body' PARAMETER5,
'true' PARAMETER6,
'reply-to@nowhere.com' PARAMETER7
from "BISAMPLE"."SAMP_CUSTOMERS_D" "SAMP_CUSTOMERS_D"
Burst to File
select
"SAMP_CUSTOMERS_D"."NAME" as "KEY",
'Orders Letter' TEMPLATE,
'XPT' TEMPLATE_FORMAT,
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT,
"SAMP_CUSTOMERS_D"."NAME"||'_'||'Orders List' OUTPUT_NAME,
'FILE' DEL_CHANNEL,
'/home/oracle/rb' PARAMETER1 ( Folder Location)
from
"BISAMPLE"."SAMP_CUSTOMERS_D" "SAMP_CUSTOMERS_D"
Associate Bursting Definition with Report
Now that the bursting definition has been setup, it must be associated with a report in order to be used. Edit Customer Letter and view the Properties. By default, the bursting definition associated with the data model for report will be selected in the Advanced section of the report properties. The drop down beside Enable Bursting allows you to select which bursting definition to use for the report. Remember, you can only use 1 bursting definition in a single report.
Scheduling a Report to be Burst
To schedule a report to be burst, create a new Report Job and schedule as normal, except on the Output tab check the checkbox labeled „Use Bursting Definition to Determine Output & Delivery Destination‟. When this box is checked, the output and destination input fields are hidden as all delivery information is expected to come from the bursting query.
The result of bursting should be individual customer letter generated and stored in folder specified or delivered to customer email address.
Bursting is an extremely powerful feature of BIP, there is no doubt. The next blog I would like to compare the report bursting feature of BI Publisher with Oracle BI Delivers, which I have used for report bursting for number of times.
No comments:
Post a Comment