Mastering SQL for Oracle Fusion HCM – Chapter 15. SQL in BI Publisher and OTBI: Querying Oracle Fusion HCM Data Sources

 

✅Chapter 15. SQL in BI Publisher and OTBI: Querying Oracle Fusion HCM Data Sources

SQL in BI Publisher & OTBI for Oracle Fusion HCM Reporting

Learn how to use SQL in Oracle BI Publisher and OTBI to extract, report, and analyze Oracle Fusion HCM data. Real-time examples included.

---

🔹 Introduction

Oracle Fusion HCM offers robust reporting tools such as BI Publisher (BIP) and Oracle Transactional Business Intelligence (OTBI). Both tools allow users to run SQL queries to fetch real-time data, though each has specific use cases and capabilities.

In this chapter, you’ll learn:

• The role of SQL in BI Publisher vs OTBI

• How to create SQL queries for BIP reports

• How to work with subject areas in OTBI

• Real-time Fusion HCM use cases

• Tips for writing and optimizing SQL in both platforms

---

🔹 1. Overview: BI Publisher vs OTBI

Feature	BI Publisher (BIP)	OTBI
SQL Support	Full SQL (native Oracle)	No direct SQL (uses metadata)
Data Source	Data Model with SQL or View	Predefined subject areas
Real-Time Data	Yes	Yes
Customization Level	High (SQL + templates)	Moderate (drag & drop)
Output Formats	Excel, PDF, RTF, HTML	Dashboard/Interactive
Scheduling	Yes	Yes

---

🔹 2. Writing SQL in BI Publisher

✅ How to Use SQL in BIP:

1. Go to Business Intelligence → Catalog.

2. Create a new Data Model.

3. Choose SQL Query as your data source.

4. Paste your SQL and define parameters (if needed).

✅ Example 1: List of Active Employees and Their Job Details

SELECT ppf.person_number, ppf.full_name, paam.job_id, paam.assignment_status_type
FROM per_all_people_f ppf
JOIN per_all_assignments_m paam
  ON ppf.person_id = paam.person_id
WHERE TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  AND paam.assignment_status_type = 'ACTIVE';

💡 Real-Time Use: Pull active employee data for payroll or HR audit reports.

---

🔹 3. Working with Parameters in BI Publisher

You can add parameters like :P_DEPT_ID to make your query dynamic.

WHERE paam.department_id = :P_DEPT_ID

• Supports string, date, number, and LOV (list of values).

• Parameters can be reused across multiple queries in the same data model.

---

🔹 4. SQL in OTBI (Indirect via Logical SQL)

OTBI doesn’t support native SQL but allows drag-and-drop queries based on Subject Areas. Advanced users can write Logical SQL behind the scenes using:

1. Oracle BI Answers (Criteria tab)

2. Advanced SQL View (Analysis → Advanced → Logical SQL)

❌ You cannot write native SQL like:

SELECT * FROM PER_ALL_ASSIGNMENTS_M;

✅ But you can do:

SELECT "workforce management"."employee number",
       "workforce management"."department name"
FROM   "workforce management"
WHERE  "workforce management"."assignment status" = 'Active' 

---

🔹 5. Real-Time Use Cases for SQL in Fusion HCM

🔸 Case 1: Payroll Report in BIP

SELECT ppf.full_name,
       pr.assignment_id,
       pr.amount
FROM   pay_element_entries_f pr,
       per_all_people_f ppf
WHERE  pr.element_name = 'Basic Salary'
       AND TRUNC(SYSDATE) BETWEEN pr.effective_start_date AND
                                  pr.effective_end_date
       AND pr.person_id = ppf.person_id 

Use: Create monthly salary output for payroll processing.

---

🔸 Case 2: OTBI Dashboard for Leave Balances

• Subject Area: Absence Management – Absence Real Time

• Metrics: Leave Type, Balance, Employee, Department

• Visualization: OTBI table or bar chart with filters on date and department

---

🔹 6. Performance Optimization Tips

✅ BI Publisher:

• Use date filters: TRUNC(SYSDATE) or :P_DATE

• Avoid SELECT *, use only necessary fields

• Limit rows: FETCH FIRST 100 ROWS ONLY

• Use WITH clause (CTE) for cleaner queries

✅ OTBI:

• Don’t overload with too many columns or filters

• Use dashboard prompts and subject areas effectively

• Avoid custom calculations unless necessary

---

🔹 Summary

✅ Use SQL in BI Publisher to create pixel-perfect reports using Oracle SQL
✅ Use OTBI for quick, interactive dashboards using subject areas
✅ Apply parameters, filters, and performance best practices to handle large Fusion HCM datasets

---

🔹 Next Steps

• Previous Chapter: Mastering SQL for Oracle Fusion HCM – Chapter 14. SQL Advanced Data Manipulation Techniques in Fusion HCM

• Next Chapter:  Mastering SQL for Oracle Fusion HCM – Chapter 16. Best Practices for SQL in Fusion HCM – Optimizing Reports and Queries

 Pages 

Mastering SQL for Oracle Fusion HCM

Oracle Fusion HCM Extracts

Oracle Fusion BI Publisher Course

Oracle Fusion Fast Formulas