BIP Reports - Chapter 18: Performance Optimization & Best Practices in Oracle Fusion BI Publisher (Global HR Focus)

 

Chapter 18: Performance Optimization & Best Practices in Oracle Fusion BI Publisher (Global HR Focus)

This chapter helps you:

• Tune SQL queries for better speed

• Reduce load on Fusion's transactional database

• Optimize layouts for rendering performance

• Avoid timeouts during long report execution

• Schedule reports smartly for Global HR teams

---

✅ 1. Optimize SQL Queries for BI Publisher

🔧 General SQL Tips:

Optimization Technique	Benefit
Filter early (WHERE clause)	Reduce rows scanned
Avoid SELECT *	Limits unnecessary columns
Use date ranges on effective_start_date/end_date	Prevents huge joins
Use indexes (person_id, assignment_id)	Faster joins
Join only required tables	Reduces memory and CPU load

✅ Example: Before vs After

❌ Slow Query

sql
SELECT *
FROM per_all_people_f ppf
JOIN per_all_assignments_f paf ON ppf.person_id = paf.person_id

✅ Optimized Query

sql
SELECT
    ppf.full_name,
    ppf.person_number,
    paf.assignment_type
FROM per_all_people_f ppf
JOIN per_all_assignments_f paf ON ppf.person_id = paf.person_id
WHERE
    TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
    AND paf.primary_flag = 'Y'

---

✅ 2. Use BIP Parameters to Limit Dataset Size

Encourage users to filter by date, BU, department, etc.

sql
WHERE
    ppf.business_unit_id = :P_BUSINESS_UNIT
    AND paf.assignment_status_type IN (:P_STATUS)

• Use LOV (List of Values) for better user input control

• Set mandatory parameters to prevent accidental full runs

---

✅ 3. Choose Efficient Layouts

📄 RTF vs. Excel vs. PDF

Layout Type	Best For	Performance Notes
RTF	Letters, grouped reports	Moderate performance
Excel	Tabular exports, analysis	Faster for large data
PDF	Final delivery (formatted)	Slowest rendering

Tips:

• Avoid too many images/logos in headers

• Use simple table formats

• Limit nested tables in RTF

---

✅ 4. Leverage Caching and Scheduling

🕒 Schedule Heavy Reports:

• Avoid running large reports ad hoc

• Use BI Scheduler to:

  • Schedule at off-peak hours

  • Deliver by email or FTP

  • Cache results for reuse

💾 Cache LOVs and Static Data:

• Use “Enable Cache” in data model LOVs

• Prevent re-fetching unchanging values (like job names)

---

✅ 5. Split Large Reports Into Segments (Chunking)

If you expect large volumes:

• Use bursting logic to break into manageable files (by BU, manager, etc.)

• Run each part in parallel

---

✅ 6. Track and Monitor Report Performance

Use:

• Oracle BI Administration Reports

  • Location: Shared Folders > BI Publisher > Administration

• Review:

  • Report execution time

  • Most run reports

  • Users triggering large queries

---

✅ 7. Common Pitfalls to Avoid

Pitfall	Impact	Fix
Missing date filters	Pulls massive data	Always filter by date
SELECT *	Slows down rendering	Select only used columns
Too many nested groups	High memory usage	Flatten layout
Bursting to all users	Overload system	Filter smartly
Using BIP where OTBI suffices	Unnecessary complexity	Use OTBI for summaries

---

✅ 8. Summary of Best Practices

✅ Keep SQL clean, selective, and indexed
✅ Limit report rows with well-defined prompts
✅ Prefer Excel for large volumes
✅ Schedule vs run-on-demand
✅ Cache LOVs and repeat data
✅ Monitor, test, and improve iteratively

---

📌 Next Chapter Preview:

Chapter 19: BI Publisher Administration, Migration, and Backup

• Export/import reports between environments

• Backup BI Catalog folders

• Promote from TEST to PROD with version control