Oracle Business Intelligence Publisher: Best Practices for Report Design

Troubleshooting Common Issues in Oracle Business Intelligence Publisher

1. Report not running or stuck in “Queued”/“Running”

• Cause: Scheduler/back-end service issues, insufficient resources, or long-running queries.
• Fixes:
  1. Check BI Publisher and WebLogic server logs (bi_server1, biplatform.log, and WebLogic logs).
  2. Verify BI Publisher and database connectivity; test datasource in Enterprise Manager.
  3. Restart BI Publisher services or affected application server nodes.
  4. Review and optimize the SQL/PLSQL data model; add indexes or rewrite queries.
  5. Increase scheduler threads or heap size if resource-constrained.

2. Empty or partial report output (no data or truncated)

• Cause: Query returns no rows, data mapping issues, or template limits.
• Fixes:
  1. Run the data model SQL manually against the database to confirm rows.
  2. Validate data structure: ensure field names/paths in the template match the data model.
  3. Check for filters or parameters being passed incorrectly.
  4. For truncation, verify output format settings and template pagination; increase character limits if custom code truncates fields.

3. Template rendering errors (layout broken, missing fields, formatting)

• Cause: Template XML mismatch, unsupported features in output format, or template corruption.
• Fixes:
  1. Download and inspect the XML data sample and compare XML tags to template fields.
  2. Use the Template Builder (Word/Excel) preview with sample data to debug.
  3. Replace problematic fields with simple placeholders, then reintroduce complex elements.
  4. Regenerate template from scratch if corruption suspected.

4. Authentication/authorization failures

• Cause: User role changes, LDAP/SSO problems, or permission misconfiguration on report objects.
• Fixes:
  1. Verify user account, roles, and permissions in BI Publisher Catalog and Fusion Middleware Console.
  2. Test login via direct BI Publisher URL vs SSO to isolate SSO issues.
  3. Check LDAP/Identity Provider connectivity and certificate validity.
  4. Reassign object permissions or inherit from parent folders where appropriate.

5. Slow report performance

• Cause: Inefficient queries, large data sets, template processing overhead, or resource limits.
• Fixes:
  1. Profile the SQL queries and add indexes, materialized views, or pagination.
  2. Use server-side filtering and avoid SELECT; return only required columns.
  3. Cache repeated datasets or use BI Publisher data model caching.
  4. Optimize templates (avoid heavy formatting, reduce loops or nested tables).
  5. Scale resources: increase JVM heap, add app server nodes, or tune database parameters.

6. Exported PDF/Excel corrupted or missing elements

• Cause: Renderer issues, fonts missing on server, or unsupported CSS/Word features.
• Fixes:
  1. Ensure required fonts are installed on the application server.
  2. Update or patch BI Publisher renderers; check known bugs for your version.
  3. Simplify template styling; avoid features unsupported by the PDF/Excel renderer.
  4. Test alternate output formats to isolate renderer-specific problems.

7. Scheduling/delivery failures (email, FTP, etc.)

• Cause: Incorrect delivery settings, SMTP/FTP connectivity problems, or credential expiry.
• Fixes:
  1. Test SMTP/FTP connectivity from the application server (telnet, curl).
  2. Verify credentials and update stored account details.
  3. Check delivery templates for invalid addresses or file paths.
  4. Review delivery logs for error codes and retry policies.

8. Version incompatibilities and bugs

• Cause: Mismatched BI Publisher, WebLogic, or database versions; missing patches.
• Fixes:
  1. Confirm compatibility matrix for your BI Publisher release.
  2. Apply recommended CPU/patch set updates and Merge Patches.
  3. Check Oracle Support for bug notes and workarounds.

Recommended troubleshooting workflow (step-by-step)

1. Reproduce the issue and capture exact steps.
2. Collect logs: biplatform.log, WebLogic server logs, data model SQL output, and template XML.
3. Isolate: test data model independently, preview template with sample data, and try different formats.
4. Resolve by applying targeted fixes above.
5. Verify full functionality and performance in a test environment before production changes.
6. Document root cause and remediation for future reference.

If you want, I can provide specific diagnostic commands/log locations for your BI Publisher version or help troubleshoot an actual error message—share the error text and your version (assume 12c/11g if unspecified).