Oracle Transactional Business Intelligence (OTBI) is the real-time reporting layer built into Oracle Fusion Cloud HCM. Unlike BI Publisher (which runs on extracted data), OTBI queries live transactional tables, giving you up-to-the-minute results. The catch: you must pick the correct subject area before you write a single column into your report.
Subject areas are pre-built, secured data models that expose Oracle Fusion tables as logical folders and columns. Choosing the wrong subject area is the number-one reason OTBI reports return wrong numbers, duplicate rows, or time out. This guide gives you a decision tree, a complete reference of every HCM subject area by module, and the performance tips that actually matter.
Key concept: Each subject area has a fact (the grain of the data) and dimensions (the attributes you can filter/group by). You cannot join across subject areas in a single analysis. If you need data from two subject areas, use a Combined Analysis or build a BI Publisher report instead.
In This Guide
- Decision Tree: Which Subject Area Do I Use?
- Workforce Management Subject Areas
- Compensation Subject Areas
- Recruiting Subject Areas
- Talent Management Subject Areas
- Succession Subject Areas
- Learning Subject Areas
- Benefits Subject Areas
- Payroll Subject Areas
- Time and Labor Subject Areas
- OTBI Performance Tips
- Common Pitfalls and Solutions
Decision Tree: Which Subject Area Do I Use?
Start here. Identify what you need, and the decision tree points you to the right subject area.
"I need a report on..." → Use this subject area
Tip: If you are unsure whether to use "Real Time" vs "Trend" subject areas — Real Time shows the current snapshot of data. Trend subject areas show data across time periods and are designed for period-over-period comparisons (headcount by month, turnover by quarter).
Workforce Management Subject Areas
These are the most-used OTBI subject areas. They source from PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_PERIODS_OF_SERVICE, and related Core HR tables.
| Subject Area | Module | Grain / Use Case |
|---|---|---|
| Worker Assignment Real Time | Workforce | Current worker assignments. Headcount, roster, org chart. One row per active assignment. |
| Worker Assignment Event Real Time | Workforce | Individual assignment events (hire, transfer, promotion, termination). One row per event. |
| Worker Assignment History Real Time | Workforce | Historical assignment changes over time. Multiple rows per assignment showing date-effective history. |
| Workforce Trend - Worker Trend Real Time | Workforce | Period-over-period workforce metrics. Headcount trends, turnover rates, new hires by month/quarter. |
| Workforce Trend - Gain Loss Trend Real Time | Workforce | Hires vs. terminations by period. Net gain/loss analysis. |
| Worker Document of Record Real Time | Workforce | Documents of record (visas, certifications, I-9). Expiry tracking. |
| Absence Real Time | Absence | Absence entries, balances, duration. Sourced from PER_ABSENCE_ENTRIES. |
| Worker Absence Plan Real Time | Absence | Absence plan details, accrual entitlements, plan enrollment. |
| Workforce Directory Real Time | Workforce | Directory-style worker info: name, title, phone, email, manager, photo. |
| Worker Seniority Real Time | Workforce | Seniority dates, length of service calculations, enterprise seniority. |
Compensation Subject Areas
Compensation subject areas source from CMP_SALARY_F, CMP_SALARY_COMPONENTS_F, and compensation plan tables.
| Subject Area | Module | Grain / Use Case |
|---|---|---|
| Salary Details Real Time | Compensation | Current and historical salary records. Base pay, components, frequency, currency. |
| Compensation Worksheet Real Time | Compensation | Comp plan worksheets with budget, allocations, and manager recommendations. |
| Compensation History Real Time | Compensation | Salary change history over time. Previous vs. new salary, change reason, change percentage. |
| Total Compensation Statement Real Time | Compensation | Total comp statements including salary, benefits, equity, and other comp components. |
Recruiting Subject Areas
Recruiting subject areas source from IRC_REQUISITIONS_B, IRC_SUBMISSIONS, and IRC_CANDIDATES.
| Subject Area | Module | Grain / Use Case |
|---|---|---|
| Recruiting Real Time | Recruiting | Requisitions, candidates, applications, and hiring pipeline. Primary recruiting SA. |
| Recruiting - Candidate Real Time | Recruiting | Candidate-centric view. Application history, source tracking, candidate pool. |
| Recruiting - Requisition Real Time | Recruiting | Requisition-centric view. Open reqs, filled reqs, time-to-fill, req status by department. |
| Recruiting - Interview Real Time | Recruiting | Interview schedules, feedback, interviewer assignments. |
| Recruiting - Offer Real Time | Recruiting | Offer details, offer status, offer amounts, offer-to-acceptance conversion. |
Talent Management Subject Areas
| Subject Area | Module | Grain / Use Case |
|---|---|---|
| Performance Rating Real Time | Talent | Performance review ratings. Manager rating, self rating, overall rating. Sources from HRA_PERFORMANCE_DOCUMENTS. |
| Goal Management Real Time | Talent | Employee goals, target dates, completion status. Sources from HRA_GOALS_F. |
| Talent Profile Real Time | Talent | Competencies, skills, certifications, qualifications. Sources from HRT_PROFILE_ITEMS_B. |
| Talent Review Real Time | Talent | Talent review meetings, 9-box placement, potential vs. performance grid. |
Succession Subject Areas
| Subject Area | Module | Grain / Use Case |
|---|---|---|
| Succession Real Time | Succession | Succession plans, candidates, readiness levels. Sources from PER_SUCCESSION_PLAN_CANDS. |
| Succession - Incumbent Real Time | Succession | Incumbent-centric view of succession plans. Positions at risk, flight risk, retirement eligibility. |
| Succession - Candidate Pipeline Real Time | Succession | Candidate pipeline depth and readiness for critical roles. |
Learning Subject Areas
Learning SAs source from WLF_LEARNING_RECORDS and WLF_COURSES_B.
| Subject Area | Module | Grain / Use Case |
|---|---|---|
| Learning Real Time | Learning | Enrollments, completions, in-progress courses. One row per learner per offering. |
| Learning - Course Catalog Real Time | Learning | Course catalog with categories, duration, delivery mode, prerequisites. |
| Learning - Compliance Real Time | Learning | Compliance training completion rates, overdue assignments, certification expiry. |
Benefits Subject Areas
Benefits SAs source from BEN_PRTT_ENRT_RSLT_F, BEN_PL_F, and BEN_PER_IN_LER.
| Subject Area | Module | Grain / Use Case |
|---|---|---|
| Benefits Enrollment Real Time | Benefits | Active benefit elections, plan enrollment, coverage levels, dependents on plan. |
| Benefits Life Event Real Time | Benefits | Life events (new hire, marriage, birth, QLE), processing status, event dates. |
Payroll Subject Areas
Payroll SAs source from PAY_RUN_RESULTS_F, PAY_ELEMENT_ENTRIES_F, PAY_BALANCE_TYPES, and PAY_PAYROLL_ACTIONS.
| Subject Area | Module | Grain / Use Case |
|---|---|---|
| Payroll Run Results Real Time | Payroll | Run result details by element. Earnings, deductions, employer costs per pay period. |
| Payroll Balance Real Time | Payroll | Balance values (YTD, QTD, MTD, PTD) by balance type and dimension. |
| Payroll Element Entry Real Time | Payroll | Element entry details. Recurring and nonrecurring entries assigned to workers. |
| Payroll Costing Results Real Time | Payroll | GL costing allocations per payroll run. Cost center, account, and segment distribution. |
| Payroll Payment Real Time | Payroll | Payment details including payment method, bank, amount, and payment date. |
| Payroll Statutory Deduction Real Time | Payroll | Tax withholdings and statutory deductions by jurisdiction. |
Time and Labor Subject Areas
| Subject Area | Module | Grain / Use Case |
|---|---|---|
| Time Real Time | Time | Reported time entries, time cards, approval status. Hours by project, task, type. |
| Time - Schedule Real Time | Time | Work schedules, shift patterns, schedule assignments. |
| Time - Exception Real Time | Time | Time exceptions (late arrival, early departure, missed punch). Compliance tracking. |
OTBI Performance Tips
1. Always Filter by Date-Effective Columns
Subject areas that source from _F tables (PER_ALL_ASSIGNMENTS_F, CMP_SALARY_F, etc.) are date-effective. If you don't filter, you get every historical row for every person. Use the subject area's built-in date filters:
-- In OTBI filter (Answers), set:
"Assignment Details"."Effective Start Date" <= CURRENT_DATE
AND "Assignment Details"."Effective End Date" >= CURRENT_DATE2. Use PRIMARY_FLAG and EFFECTIVE_LATEST_CHANGE
Workers can have multiple assignments. To avoid counting someone twice:
-- To get one row per worker:
"Assignment Details"."Primary Flag" = 'Y'
"Assignment Details"."Effective Latest Change" = 'Y'
-- PRIMARY_FLAG = 'Y' filters to the primary assignment
-- EFFECTIVE_LATEST_CHANGE = 'Y' filters to the latest date-effective row3. Avoid SELECT * Mentality
Only add columns you need. Every column you add is a join. OTBI analyses with 30+ columns will be slow. Start with 5–8 columns, validate the data, then add more.
4. Understand Row Limits
OTBI has a default row limit (usually 65,000 rows). If your result set exceeds this, OTBI silently truncates. For large data sets, use BI Publisher or HCM Extracts instead.
Warning: The OTBI row limit applies before aggregation. If you have 100,000 assignment rows and you're doing a COUNT, OTBI may return the count of only the first 65,000 rows. Always check "Rows" in your analysis to confirm you're not hitting the limit.
5. Cross-Subject-Area Limitations
You cannot join two subject areas in a single analysis. If you need salary data alongside absence data, you have three options:
- Combined Analysis: Build two separate analyses, then combine them on a shared key (Person Number)
- BI Publisher: Write a data model that queries multiple subject areas or directly uses SQL
- HCM Extract: Build an extract that pulls data from multiple tables and outputs to a file
6. Use "Indexed" Columns in Filters First
OTBI performance improves dramatically when you filter on columns that are indexed in the underlying tables. Good first filters:
Business Unit— almost always indexedDepartment— indexed on most assignment tablesLegal Employer— indexed on payroll and HR tablesPerson Number— indexed everywhere
Common OTBI Pitfalls and Solutions
| Pitfall | Symptom | Solution |
|---|---|---|
| Duplicate rows | Headcount shows 3x expected | Add PRIMARY_FLAG = 'Y' and EFFECTIVE_LATEST_CHANGE = 'Y' filters |
| Missing terminated workers | Turnover report shows zero terms | Use "Worker Assignment Event Real Time" instead of "Worker Assignment Real Time" (which only shows active workers) |
| Slow performance | Analysis times out | Reduce columns, add Business Unit filter, avoid using "contains" on text fields |
| Row limit hit | Data looks correct but totals are wrong | Check if result exceeds 65K rows. Use BI Publisher for large extracts |
| Wrong headcount for a past date | Historical headcount doesn't match | Use "Workforce Trend" SA with period dates, not "Worker Assignment" SA with manual date filter |
| Nulls in department/location | Some workers show blank department | Check if those workers have non-primary assignments. Filter ASSIGNMENT_TYPE to 'E' (Employee) or 'C' (CWK) |
| Security filtering too much | User sees fewer rows than expected | Check HCM data roles and security profiles. OTBI respects HCM data security on subject areas |
| Currency mismatch | Salary totals look wrong | Always include Currency Code in salary reports. Use the SA's built-in currency conversion columns if available |
| Absence hours vs. days | Absence durations don't match UI | Check UOM in the subject area. Some SAs report in hours, others in days. Verify against Absence Management configuration |
| Can't find a column | Expected attribute not in SA | Check if the column exists in a different SA folder, or if it's a DFF that needs to be exposed via a BI flex configuration |
Search the Underlying Tables
Every OTBI subject area maps to physical Oracle tables. Search them all here.
Search 35,000+ Tables