XML Bursting process in Oracle Fusion applications
- Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations.
- For each block of the data, a separate document is generated and delivered.
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:
- Split By element: used to split the XML up into subsections, called Bursting Data Blocks.
- Deliver By element: used to specify how the report instances should be formatted and what those delivery destinations should be.
- Delivery Query: The query that returns the formatting and delivery details.
Before moving forward, lets us see all these three components in detail. Let us consider this XML file as our reference.
Split By element
Split By element is used to split the XML up into subsections, called Bursting Data Blocks. Few points to note are,
- XML is split by the group to which the Split By element values belongs
- Bursting Data Block consists of all elements contained within the group element that contains the Split By element
- Split By element does not have to be the first element in the group in which it occurs
- If two or more blocks have same Split By element values, then both those blocks will be combined to one single block
Let us consider two different scenarios of split by element.
Split Based on VENDOR_ID
If we have specified VENDOR_ID as the Split By Element, then we would get 3 different bursting data blocks as shown below.
Split Based on INVOICE_ID
If we have specified INVOICE_ID as the SplitBy Element, then we would get 4 different bursting data blocks as shown below.
Deliver By element
Deliver By element is used to specify how the report instances should be formatted and what those delivery destinations should be.
We can imagine this as a variable that stores some information that will be later match against delivery query.
Let Us Consider Three Scenarios,
- Split By and Deliver By element both are same (1-1 Relationship between the Split By and Deliver By element value)
- Deliver By element is at a higher level than the Split By element (many-to-one relationship between the Split By and Deliver By element value)
- Deliver By element is at a lower level than the Split By element (one-to-many relationship between the Split By and Deliver By element value)
Split By and Deliver By element both are SAME (<Invoice_ID>)
Split By Invoice_ID & Deliver By /G_vendor/Vendor_ID
Split By /G_vendor/Vendor_ID & Deliver By /G_vendor/G_Header/Invoice_ID
Note: Even though there are two different INVOICE_ID in this block , it stores the latest occurrence of it overwriting its previous value.
The query returns the formatting and delivery details. It should return these columns. Not all these are compulsory. The Parameters will vary based on the delivery types.
- KEY is 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.
- OUTPUT_FORMAT – is the output format.
- 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. If this property is not set, the output will be saved.
- DEL_CHANNEL: EMAIL,FAX,FILE,FTP,PRINT
- OUTPUT_NAME – is the name that will be assigned to the output file in the report job history.
- Delivery Channel: EMAIL 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
(PARAMETER 9-10 are not used)
Delivery Channel: FTP and SFTP PARAMETER1: Server name PARAMETER2: Username PARAMETER3: Password PARAMETER4: Remote Directory PARAMETER5: Remote File Name
PARAMETER6: Secure (set this value to ‘true’ to enable Secure FTP) (PARAMETER 7-10 are not used)
Sample Delivery Query
- VENDOR_ID as Key
- Sample result of the query with VENDOR_ID as Key
- INVOICE_ID as Key
- Sample result of the query with INVOICE_ID as Key
- How everything comes together?
As illustrated above, the DeliverBy element is matched against the KEY of the delivery query and the other fields are used are formatting and delivery options.
Few key points to note are,
- If two or more blocks have same Split By element values then both those blocks will be combined to one single block
- If a single block contains two or more deliver by element, the last value will be the deliver by element there will be “no” multiple reports.
- The Deliver By element is used to format and deliver the set of report instances generated from a Bursting Data Block, does not decide the number of reports.
- If the Deliver By value has no entry in the Delivery Mapping, then processing of the report job continues and no report instances are generated for the associated Bursting Data Block
Adding a Bursting Definition to Your Data Model
In order to add the bursting definition to the data model,
- Click on Edit data model.
- Add a new bursting definition.
Configuring a Report to Use a Bursting Definition
We should enable Bursting at report level.
- Edit the Report
- Click on Properties and check “Enable Bursting” and select one Bursting Profile.
Note: We can have multiple Bursting Profiles defined but can enable only one at any given point of time.
Let us look at some steps to set up an SFTP connection.
∙ Go to the Oracle BI Publisher (/xmlpserver)
∙ Click on Administration
- Click on FTP under Delivery
- Click on Add Server
- Fill in the details such as server Name , Host , Port etc.. along with the Username and Password.
- Test the connection and click on APPLY.