OTBI Subject Areas vs Oracle HCM Tables: Know the Boundary comes down to one distinction: OTBI presents a governed, business-oriented model, while physical tables store HCM records and their relationships. Confusing those layers is a common cause of reports with mismatched fields, duplicate rows, or totals that do not reconcile.
Key Takeaways
- Subject areas are logical reporting models. Their facts, dimensions, hierarchies, and measures can combine data from multiple physical sources.
- Tables are physical structures. Their keys and effective dates determine how stored rows relate and change over time.
- Choose the reporting route by the job. Use OTBI for governed analysis, BI Publisher for designed outputs and supported SQL data models, and HCM Extracts for configured outbound extracts.
- Define grain before joining. A person, assignment, payroll action, and absence represent different row populations.
- Rebuild report behavior, not just its joins. OTBI filters, calculations, hierarchies, and data security affect the result.
- Validate from the lowest shared grain. Align dates, filters, security, and aggregation before deciding that OTBI and SQL disagree.
How an OTBI subject area differs from a physical table
An OTBI subject area is a business-facing collection of facts, dimensions, hierarchies, and measures. For example, Worker, Assignment, Payroll, Absence, and Compensation reporting concepts are arranged for analysis, rather than presented as a list of underlying Oracle HCM tables.
In OTBI, you build an analysis by selecting columns from subject-area folders; the semantic model determines which logical joins support those selections. You do not write a join between a subject area and a physical table or infer physical join keys from folder labels. If fields cannot be used together as expected, check the supported logical relationships in the model rather than matching fields by name.
A physical table is a structure of rows and columns. Its keys define relationships, and effective-dated tables use fields such as `EFFECTIVE_START_DATE` and `EFFECTIVE_END_DATE` to represent records across time.
Do not treat an OTBI field label as a physical column name. The semantic layer can apply aliases, calculations, hierarchies, filters, and aggregation rules, so the displayed label and returned value may not correspond to one column in one table.
There is no reliable one-to-one conversion from subject area to table. A logical fact or dimension can draw on multiple physical sources, and its behavior can depend on rules that a simple SQL join does not reproduce.
This is not a bug. It’s the architecture. In Oracle Fusion HCM, the semantic model organizes information for business analysis; the physical HCM data model organizes stored records and their relationships.
Choose OTBI, BI Publisher, or an extract route for the reporting need
Choose OTBI when analysts need governed, business-oriented analysis using delivered subject areas, dimensions, and measures. It is the natural route when the report should use the available semantic model rather than independently rebuilding its logic.
Choose BI Publisher when a report needs a designed output or a data model built from supported sources and SQL. Reproduce the required calculations and filters explicitly; OTBI subject-area fields are not automatically queryable physical tables.
Choose HCM Extracts for configured outbound data extracts. HCM Extracts are a distinct extraction mechanism; extract view objects are metadata objects that may support extract configurations, not the extracts themselves. Neither route should be confused with ad hoc analysis or direct physical-table access.
Direct database access is a separate access question. Oracle Fusion SaaS reporting routes, enabled data sources, and permissions determine which objects a particular query can use, so a table’s existence does not establish that it is accessible through every reporting tool.
Set the HCM row grain before joining tables
Write down what one output row represents before you build a table-based report: one person, one assignment, one payroll action, or one absence. Those are different populations, and joining them as though each person has only one related row creates duplicate counts.
For person and assignment reporting, PER_ALL_PEOPLE_F stores person records across effective dates, while PER_ALL_ASSIGNMENTS_M stores assignment records. Relate them through `PERSON_ID`, then apply a consistent reporting date to both tables.
Use the date rule that matches the report’s intended population, and account for assignment change behavior when selecting a row. Joining every effective-dated record without a shared date condition can multiply rows or pair a person record from one period with an assignment from another.
For example, an as-of join between person and assignment rows can use the same date bind in both effective-date predicates:
FROM PER_ALL_PEOPLE_F p JOIN PER_ALL_ASSIGNMENTS_M a ON a.PERSON_ID = p.PERSON_ID AND :as_of_date BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE AND :as_of_date BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
This keeps both records aligned to one reporting date. Add assignment-selection conditions only when they match the report’s intended population.
Count at the fact’s grain before rolling results up across dimensions. One person with multiple assignments can produce multiple assignment rows, so an assignment count is not automatically a worker count.
When you need to identify Oracle HCM tables, columns, or join paths, the Oracle HCM Tables Search provides a searchable schema reference for that investigation.
Trace a report field through view objects to its sources
Start with the exact subject area, folder, field, and measure in the analysis. Record its filters, hierarchy selections, prompts, and formula too, because a field name by itself does not specify the report logic.
Use release-relevant mapping and metadata to follow logical fields through the BI model and its view objects toward physical tables and columns. A mapping file is a starting point, not proof that it captures every join, filter, calculation, or security rule in the analysis.
For a concrete trace, take Worker > Person Number: follow that logical column to the BI view object named in the subject-area mapping, then check whether its source resolves to PER_ALL_PEOPLE_F.PERSON_NUMBER. Record the exact view-object name and source mapping for the release you are investigating; a displayed field can also involve a derived expression or other semantic logic, so the table column alone may not reproduce the report value.
Keep the object types distinct. BI view objects participate in the reporting model, public view objects are intended as a supported access layer, and extract view objects serve extract configurations; similar names do not make them interchangeable.
When a mapping is missing or ambiguous, inspect the object’s purpose, columns, and relationships instead of inferring equivalence from a matching name. Release changes can also affect the mapping, so validate that the metadata applies to the environment you are analyzing.
For schema investigation across Oracle Fusion Cloud, Oracle Cloud Tables is a search tool covering HCM, Financials, SCM, CX Sales, and Common Features, with table, column, and JOIN-path search features. It is available for $1.50, making it useful when you need to test a suspected table or relationship rather than guess from a field label.
Account for security and semantic-layer behavior
Compare results under the same user and role context. OTBI applies its configured data-security behavior, while a separate SQL route does not automatically inherit that context just because it reads related HCM data.
Reproduce the report’s prompts and filters, including its effective-date conditions. A different reporting date or prompt selection can change the population even when the underlying records have not changed.
Rebuild calculated measures and hierarchy behavior explicitly when moving logic into SQL. A displayed measure may aggregate at a particular level or apply semantic rules beyond the value stored in an individual physical column.
Successful access to a table or view does not prove that your SQL reproduces the governed OTBI result. A common source of false parity claims is comparing accessible rows with a report that applies additional security or semantic-layer behavior.
Validate a table-based report against the OTBI analysis
Fix a small reporting period and use the same user context for both reports. Record the OTBI prompts, filters, dimensions, and measure definitions so the comparison starts from a known specification.
Then compare row grain and effective-date rules before comparing totals. Reconcile counts at the lowest shared grain first; that makes duplicated or omitted records easier to locate than comparing only a final sum.
- Establish the baseline. Capture the OTBI selections, reporting period, user context, and output grain.
- Check keys and counts. Confirm that the intended keys are unique at each stage of the SQL query.
- Add joins and filters incrementally. Recheck row counts after every addition so a many-to-many relationship cannot silently inflate a measure.
- Align the final comparison. Match null handling, aggregation, hierarchy levels, and security context before treating a remaining difference as a data discrepancy.
For repeatable report patterns, the OTBI Template Pack: 15 Oracle HCM Report Patterns includes SQL and OTBI templates supplied as a PDF and a `.sql` file, with required filters, common-mistake notes, subject-area mapping, and table reference links. At $97, it suits analysts who want example patterns and validation details to work from while building or comparing reports.
Combine facts and dimensions without inflating measures
Identify each fact’s grain before combining measures. Joining an assignment-level fact to an absence-level fact can repeat values from one population across rows from the other, even when both relate to the same person.
Use a conformed dimension only when it represents the same business entity and time context for both facts. A shared label such as person or department does not, by itself, establish a valid join.
When a direct row-level join creates multiplicity, aggregate each fact to a compatible grain first, then combine the results using valid keys and date rules. Keep measures from separate populations distinct unless their relationship and aggregation are explicitly sound.
For example, if the output should contain one row per person for a reporting window, calculate the assignment measure and the absence measure separately, applying the same population and date filters to each. Reduce each result to one row per PERSON_ID, then join the summaries with absence_totals.PERSON_ID = assignment_totals.PERSON_ID. Starting with the assignment summary and using a left join preserves people with no matching absence row; joining the raw populations first can repeat either measure. For a period-by-period result, keep the same period key in both summaries and include it in the join.
Frequently Asked Questions
What does the _TL suffix mean on an Oracle HCM table?
The `_TL` suffix commonly identifies a translation table that holds language-dependent text associated with a base record. The translation key and language column can vary by table, so inspect that table’s metadata before writing the join.
Can an OTBI analysis include future-dated HCM changes?
It can when the subject area and analysis logic expose those records and the selected prompts or filters include their effective dates. A future-dated transaction’s approval or processing status can also affect whether it appears, so its effective date alone does not determine inclusion.
Do OTBI subject areas show HCM changes immediately after they are saved?
Visibility depends on the transaction’s commit and the reporting path used by that subject area. If a change is absent, first distinguish an uncommitted transaction from report caching or subject-area refresh behavior before changing the SQL logic.
How should I handle translated values when reproducing an OTBI report in SQL?
Join the appropriate translation rows using the documented base-record key and the report’s language context. If the query can return multiple languages, constrain it deliberately; otherwise translated descriptions can duplicate the underlying business record.
How can I tell whether a table-to-view-object mapping still applies to my environment?
Compare the mapping’s release or metadata version with the environment, then verify the object and its columns against the available metadata for that release. Recheck the relationship when a column or view object has changed rather than carrying forward an old join by name alone.
Conclusion
OTBI Subject Areas vs Oracle HCM Tables: Know the Boundary is a practical distinction between a logical, governed reporting model and the physical structures that store HCM data. Choose OTBI, BI Publisher, or HCM Extracts for the reporting job, then reproduce the required grain, effective dating, security, and semantic behavior when you build a table-based report.