BIP Reports - Chapter 12: BI Publisher Bursting — Personalized Report Delivery (Global HR Use Case)

 

💥 Chapter 12: BI Publisher Bursting — Personalized Report Delivery (Global HR Use Case)

Bursting allows you to:

• Generate multiple versions of a report in one run

• Split data by business unit, department, manager, etc.

• Send each part to different recipients (email, FTP, etc.)

This is ideal for HR reporting when:

• Managers need data only for their own teams

• BU Heads receive reports only for their own BU

• HR partners need tailored reports monthly

---

✅ 1. Real-World Use Case: Active Employees by Business Unit

Goal:
Send a separate employee report to each BU Head, containing only the active employees in their respective Business Unit.

---

✅ 2. Required Components

Component	Description
Data Model	With main query + bursting query
Report Layout	Excel or RTF layout
Bursting Query	Defines how data is split and where it's sent

---

✅ 3. Sample Main SQL (Employee Data)

sql
SELECT
    bu.name AS business_unit,
    bu.organization_id AS bu_id,
    per.full_name,
    per.person_number,
    job.job_name,
    per.hire_date
FROM
    per_all_people_f per
JOIN
    per_all_assignments_f assign ON per.person_id = assign.person_id
JOIN
    hr_jobs job ON assign.job_id = job.job_id
JOIN
    hr_organization_units bu ON assign.business_unit_id = bu.organization_id
WHERE
    TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date
    AND per.current_employee_flag = 'Y'

---

✅ 4. Bursting Query: One Email per Business Unit

Add a second query in the Data Model → Bursting SQL:

sql
SELECT
    bu.organization_id AS KEY,
    'EMAIL' AS DELIVERY_TYPE,
    hr.email_address AS DELIVERY_DESTINATION,
    'application/pdf' AS OUTPUT_FORMAT,
    'Active Employees - ' || bu.name AS OUTPUT_NAME,
    'Active Employees - ' || bu.name AS SUBJECT,
    'Please find attached the active employee report for ' || bu.name AS MESSAGE
FROM
    hr_organization_units bu
JOIN
    per_email_addresses hr ON bu.manager_person_id = hr.person_id
WHERE
    hr.email_type = 'W'

🔑 KEY must match a field (e.g., organization_id) in your main query. This splits the data accordingly.

---

✅ 5. Enable Bursting in Report Definition

1. In the BI Catalog, open the report

2. Go to More > Properties

3. Enable: ✅ Bursting Enabled

4. Under Bursting Definition, upload the bursting query

5. Choose the output format (PDF, Excel)

6. Save and run

---

✅ 6. Test the Burst Run

1. Schedule or run the report manually

2. Check:

   • Output splitting per BU

   • Correct recipients

   • Email delivery (check spam/junk too)

---

✅ 7. Other Delivery Options

Delivery Type	Use Case
EMAIL	Send to HR, managers, executives
FTP	For integrations or archival storage
FILE	Save to local/shared BI folders
PRINTER	For printed reports (rare now)

You can configure multiple delivery channels in the same burst.

---

✅ 8. Best Practices for HR Bursting

Tip	Reason
Use clear subject lines	Helps managers identify reports
Format emails professionally	Maintain brand & clarity
Match KEY to main query field	Ensures proper split logic
Schedule at off-peak hours	Avoid system delays
Test on sandbox first	Prevent mass email mistakes

---

✅ 9. Summary

You now know how to:

• Create and configure a bursting query

• Split HR data by BU or Department

• Deliver personalized outputs to HR stakeholders

This ensures targeted, secure, and efficient report distribution—a critical need in any large HR organization.

---

📌 Next Chapter Preview:

Chapter 13: Performance Optimization for Large Reports

• Reducing runtime and data size

• Using bind variables vs. literals

• Indexing and hints in SQL

• Optimizing layout rendering