Oracle HCM BI Publisher Reports Guide

Share

Introduction

Oracle Fusion HCM BI Publisher Reports play a critical role in extracting, formatting, and delivering business-ready HR data from Oracle Fusion HCM. In real-world implementations, clients rarely rely only on standard reports—they need customized, pixel-perfect documents like payslips, offer letters, and compliance reports. This is where BI Publisher becomes essential.

As a consultant, you will frequently work on BI reports for payroll, HR operations, and integrations. Understanding how BI Publisher works in the context of Fusion HCM is not optional—it is a core skill.

What is BI Publisher in Oracle Fusion HCM?

Oracle BI Publisher (BIP) is Oracle’s enterprise reporting tool used for:

  • Generating pixel-perfect reports
  • Creating templates (RTF, Excel, PDF)
  • Extracting data using SQL, HCM Extracts, or data models
  • Delivering reports via email, FTP, or bursting logic

In Fusion HCM, BI Publisher is commonly used for:

  • Payroll outputs (payslips)
  • Employee documents (offer letters, experience letters)
  • Regulatory reports
  • Bulk HR data extraction

Key Features of Oracle Fusion HCM BI Publisher Reports

1. Pixel-Perfect Formatting

Unlike OTBI, BIP allows full control over formatting using RTF templates.

2. Multiple Data Sources

  • SQL queries
  • HCM Extracts
  • Web services

3. Bursting Capability

Split and distribute reports automatically (e.g., send payslips to employees).

4. Multi-format Output

  • PDF
  • Excel
  • CSV
  • XML

5. Scheduling & Automation

Reports can be scheduled using Enterprise Scheduler Service (ESS).

Real-World Implementation Use Cases

Use Case 1 – Payslip Generation

A global company needs monthly payslips:

  • Data Source: Payroll tables
  • Template: RTF
  • Bursting: Email to employees

Use Case 2 – Offer Letter Generation

HR generates offer letters:

  • Data Source: Worker assignment data
  • Template: RTF with conditional formatting
  • Output: PDF

Use Case 3 – Compliance Reporting

Client needs statutory reports:

  • Data Source: SQL query
  • Output: Excel
  • Scheduled monthly

Architecture / Technical Flow

In a typical Oracle Fusion HCM BI Publisher setup:

  1. Data is extracted using:
    • SQL Query OR
    • HCM Extract
  2. Data Model is created in BI Publisher
  3. Template is designed (RTF/Excel)
  4. Report Definition is created
  5. Report is executed via ESS job
  6. Output is delivered (UI, Email, FTP)

Prerequisites

Before building BI Publisher reports:

  • Access to BI Catalog
  • Knowledge of Fusion HCM tables (PER, PAY, etc.)
  • SQL skills
  • Template design knowledge (RTF using MS Word)
  • Required roles:
    • BI Administrator
    • BI Author

Step-by-Step Build Process

Step 1 – Navigate to BI Catalog

Navigator → Tools → Reports and Analytics

Click → Browse Catalog

Step 2 – Create Data Model

  • Go to Data Models
  • Click Create → Data Model

Define Data Set:

Example SQL:

 
SELECT
papf.person_number,
papf.display_name,
paam.assignment_number
FROM
per_all_people_f papf,
per_all_assignments_m paam
WHERE
papf.person_id = paam.person_id
 

Configure:

  • Parameters (optional)
  • Data structure

Click Save

Step 3 – Create Report Template

  • Open MS Word
  • Install BI Publisher Plugin
  • Load sample XML data
  • Design layout using:
    • Tables
    • Conditional formatting
    • Placeholders

Example:

 
<?PERSON_NUMBER?>
<?DISPLAY_NAME?>
 

Save as RTF

Step 4 – Create Report Definition

Navigator → Reports and Analytics → Create Report

  • Upload Data Model
  • Upload Template
  • Define output formats

Step 5 – Configure Properties

  • Output format (PDF/Excel)
  • Locale settings
  • Scheduling options

Step 6 – Save and Test

Save report in BI Catalog.

Testing the Technical Component

Example Test Scenario

  • Run report for employee number: 1001

Expected Output

  • Employee details displayed correctly
  • Proper formatting in PDF

Validation Checks

  • Data accuracy
  • Formatting alignment
  • Performance (execution time)

Common Errors and Troubleshooting

1. No Data Found

  • Check SQL joins
  • Validate parameters

2. Template Not Rendering Properly

  • Incorrect XML tags
  • Formatting issues in RTF

3. Performance Issues

  • Large datasets
  • Missing indexes

4. Bursting Failure

  • Incorrect delivery query
  • Email configuration issues

Best Practices from Real Projects

1. Always Use Secured Views

Avoid direct table access; use secured views when possible.

2. Optimize SQL Queries

  • Avoid unnecessary joins
  • Use bind variables

3. Keep Templates Simple

Over-designed templates cause performance issues.

4. Use HCM Extracts for Complex Reports

For payroll-heavy reports, HCM Extracts are more efficient.

5. Version Control

Maintain versions of:

  • Data models
  • Templates

Frequently Asked Interview Questions

1. What is BI Publisher in Fusion HCM?

It is a reporting tool used for generating formatted reports using SQL or extracts.

2. Difference between OTBI and BI Publisher?

  • OTBI: Real-time analytics
  • BIP: Formatted reports

3. What is a Data Model?

Defines how data is fetched and structured.

4. What is Bursting?

Splitting report output and delivering to multiple recipients.

5. What formats does BI Publisher support?

PDF, Excel, CSV, XML.

6. What is RTF Template?

A Word-based template used for report formatting.

7. Can BI Publisher use HCM Extracts?

Yes, it is commonly used for payroll reports.

8. What is ESS Job?

Used to schedule report execution.

9. How to improve performance?

Optimize SQL and reduce data volume.

10. What are secured views?

Oracle-provided views with security applied.

11. Can reports be scheduled?

Yes, via ESS.

12. What is XML in BI Publisher?

Intermediate data format used for templates.

13. What is a bursting query?

SQL that defines delivery logic.

14. Can BI Publisher call web services?

Yes.

15. Where are reports stored?

BI Catalog.

Real Implementation Scenario Insight

In one project, a client needed:

  • 50,000 monthly payslips
  • Email delivery
  • Multi-language support

Solution:

  • Used HCM Extract
  • Created RTF template with language conditions
  • Implemented bursting query

Result:

  • Fully automated payroll reporting
  • Zero manual intervention

Expert Tips

  • Learn Fusion HCM table structures deeply
  • Practice writing complex SQL queries
  • Use sample XML carefully
  • Always test with real production-like data
  • Debug using BI Publisher logs

FAQs

1. When should we use BI Publisher instead of OTBI?

Use BI Publisher when you need formatted documents like PDFs.

2. Can BI Publisher handle large data volumes?

Yes, but performance tuning is required.

3. Is BI Publisher used in payroll?

Yes, extensively for payslips and payroll reports.

Summary

Oracle Fusion HCM BI Publisher Reports are a core technical component in any HCM implementation. From payslips to compliance reports, BI Publisher enables consultants to deliver highly customized outputs that business users rely on daily.

Mastering BI Publisher means understanding:

  • Data models
  • SQL queries
  • Template design
  • Report delivery

For deeper understanding, refer to 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 *