Bursting Query in Oracle HCM

Share

Bursting Query in Oracle Fusion HCM: Complete Practical Guide

When working with reports in Oracle Fusion HCM, one of the most powerful yet often misunderstood features is the Bursting Query. In real implementations, especially for payroll, HR letters, and compliance reporting, bursting becomes a critical requirement.

As a consultant, you will frequently face scenarios where a single report needs to be distributed to multiple users—each receiving only their relevant data. This is exactly where Bursting Query in Oracle Fusion HCM plays a key role.

What is Bursting Query in Oracle Fusion HCM?

A Bursting Query is used in BI Publisher reports to split (burst) a single report output into multiple documents and deliver them to different recipients based on defined logic.

Instead of generating one large report, the system:

  • Divides the report into chunks (based on a key like Employee ID or Department)
  • Sends each chunk to a specific destination (Email, FTP, WebDAV, etc.)

Simple Example

Imagine generating a salary slip report for 1000 employees:

  • Without bursting → One large PDF
  • With bursting → 1000 individual PDFs emailed to each employee

Key Features of Bursting Query

1. Data-Based Distribution

  • Uses SQL logic to determine recipients
  • Can dynamically fetch email IDs or delivery channels

2. Multiple Delivery Channels

  • Email
  • FTP/SFTP
  • WebDAV
  • Printer

3. Format Control

  • Output formats: PDF, Excel, XML, etc.
  • Each recipient can receive different formats

4. Template Mapping

  • Different templates can be used for different users
  • Example: Managers vs Employees

5. Secure Distribution

  • Ensures users receive only their relevant data
  • Critical for payroll and HR compliance

Real-World Business Use Cases

Use Case 1: Employee Payslip Distribution

A company needs to send monthly payslips to all employees.

  • Bursting Key: Employee ID
  • Delivery: Email
  • Output: PDF

👉 Each employee receives only their payslip.

Use Case 2: Department-wise HR Reports

HR wants department-specific reports sent to department heads.

  • Bursting Key: Department ID
  • Delivery: Email
  • Template: Customized for managers

👉 Each manager receives only their department data.

Use Case 3: Offer Letter Generation

Recruitment team generates offer letters for candidates.

  • Bursting Key: Candidate ID
  • Delivery: Email + Archive
  • Template: Offer Letter format

👉 Each candidate gets a personalized document.

Architecture / Technical Flow

In a typical Oracle Fusion HCM setup:

  1. Data Template (XML Data Source)
    • Fetches report data
  2. RTF Template
    • Defines layout
  3. Bursting Query
    • Defines:
      • Split logic
      • Delivery logic
  4. BI Publisher Engine
    • Executes bursting
    • Sends output to destinations

Flow Summary

 
Data Template → BI Publisher → Bursting Query → Output Split → Delivery
 

Prerequisites

Before implementing bursting:

  • BI Publisher report should be created
  • Data model must include a bursting key
  • Email server (SMTP) should be configured
  • Required roles:
    • BI Administrator
    • Report Developer

Step-by-Step Build Process

Let’s walk through a real implementation scenario.

Step 1 – Create the BI Publisher Data Model

Navigation:

 
Tools → Reports and Analytics → Browse Catalog → New → Data Model
 
  • Write SQL query
  • Include bursting key

Example SQL

 
SELECT
person_number,
email_address,
salary
FROM per_all_people_f
 

👉 person_number will be used as the bursting key.

Step 2 – Create the Report Template

  • Upload RTF template
  • Map fields like:
    • Employee Name
    • Salary
    • Email

Step 3 – Define Bursting Query

This is the most important step.

Add a bursting query in the Data Model.

Sample Bursting Query

 
SELECT
person_number AS “KEY”,
‘EMAIL’ AS “DEL_CHANNEL”,
‘PDF’ AS “OUTPUT_FORMAT”,
email_address AS “PARAMETER1”,
‘Payslip’ AS “OUTPUT_NAME”,
‘en-US’ AS “LOCALE”,
‘Payslip_Template’ AS “TEMPLATE”
FROM per_all_people_f
 

Key Columns Explained

ColumnPurpose
KEYMatches main query key
DEL_CHANNELDelivery method (EMAIL)
OUTPUT_FORMATPDF, Excel, etc.
PARAMETER1Email address
OUTPUT_NAMEFile name
TEMPLATETemplate name

Step 4 – Configure Delivery

  • Ensure email delivery is enabled
  • Check SMTP configuration in BI Publisher

Step 5 – Enable Bursting in Report

  • Open report properties
  • Enable bursting
  • Select bursting query

Testing the Bursting Setup

Test Scenario: Payslip Distribution

Steps:

  1. Run report for multiple employees
  2. Check output

Expected Results:

  • Separate PDF per employee
  • Each sent to respective email

Validation Checks:

  • Email received?
  • Correct employee data?
  • Proper formatting?

Common Implementation Challenges

1. Email Not Triggering

Cause:

  • SMTP not configured

Solution:

  • Verify email server settings

2. Incorrect Data Splitting

Cause:

  • Wrong bursting key mapping

Solution:

  • Ensure KEY matches main query

3. Template Not Found Error

Cause:

  • Wrong template name in query

Solution:

  • Match exact template name

4. Performance Issues

Cause:

  • Large data volume

Solution:

  • Optimize SQL
  • Use filters

Best Practices from Real Projects

1. Always Use Indexed Columns

  • Improves performance
  • Example: person_number instead of name

2. Keep Bursting Query Simple

Avoid complex joins in bursting query

3. Validate Email Data

Ensure:

  • No null emails
  • Correct format

4. Use Naming Standards

Example:

 
Payslip_<EmployeeID>_<Month>
 

5. Test with Small Data First

  • Start with 5–10 records
  • Then scale

6. Secure Sensitive Reports

  • Especially payroll reports
  • Ensure correct recipient mapping

Expert Consultant Tips

  • Always separate data logic and bursting logic
  • Avoid hardcoding values in SQL
  • Use parameters for flexibility
  • Maintain reusable templates
  • Document bursting logic for support teams

Summary

Bursting Query in Oracle Fusion HCM is a must-know concept for any consultant working with BI Publisher reports. It enables:

  • Automated report distribution
  • Personalized document delivery
  • Secure handling of sensitive HR data

In real-world projects, bursting is heavily used for:

  • Payslips
  • Offer letters
  • HR reports

Mastering this concept will significantly improve your ability to deliver enterprise-grade reporting solutions.

FAQs

1. What is the main purpose of bursting in Oracle Fusion HCM?

Bursting is used to split a single report into multiple outputs and deliver them to different recipients automatically.

2. Can we use multiple delivery channels in bursting?

Yes, you can configure delivery via Email, FTP, WebDAV, and more within the same bursting query.

3. Is bursting supported for all BI Publisher reports?

Yes, as long as the data model includes a valid bursting query and key mapping.

For deeper technical understanding, refer to official Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html


Share

Leave a Reply

Your email address will not be published. Required fields are marked *