Fix Wrong Row Counts When Joining Across OTBI Subject Areas
OTBI analysis returns no rows or duplicated rows across subject areas
Also searched as
- how to join two OTBI subject areas correctly
- OTBI analysis wrong row counts multiple subject areas
- combining folders from different OTBI subject areas
- OTBI duplicate rows when adding a second folder
Short answer
Most subject areas in Oracle Fusion OTBI are self-contained and only support reliable analysis within their own folders; dragging a folder from a second subject area into the same analysis without a genuine shared join key silently produces duplicated or missing rows because OTBI has no way to correctly relate the two fact grains. Fix it by staying inside one subject area where possible, or by explicitly using a subject area that Oracle designed to cross domains, rather than combining arbitrary folders yourself.
Applies to: Oracle Fusion Cloud ERP, SCM, and HCM, all OTBI subject areas, all monthly updates
Diagnose and fix an OTBI multi-subject-area row count problem
- 1Confirm which subject area each folder you dragged into the analysis belongs to - the criteria pane groups folders by subject area, so check whether you have combined folders from two different top-level subject areas.
- 2If row counts look inflated, check whether you combined a header-level folder (one row per order) with a lines-level folder (one row per line) without a shared grain, which multiplies header attributes across every line.
- 3If row counts look too low or zero, check whether the two folders share no real join key at all - OTBI is silently returning an empty or filtered result rather than erroring.
- 4Search for a purpose-built cross-domain subject area (for example, one explicitly named to cover Order Management and Receivables together) before trying to manually relate two single-domain subject areas.
- 5If no cross-domain subject area exists for your combination, split the requirement into two separate analyses at matching grains and combine them in a dashboard, or in Excel/BI Publisher, rather than forcing an unsupported OTBI join.
- 6For a genuinely custom cross-subject-area requirement, evaluate a BI Publisher data model (custom SQL or multiple data sets) instead of trying to reshape OTBI folders to fit.
- 7Validate any fix against a known total (an existing report or a manual count for a small data set) before trusting the corrected analysis.
Why OTBI Cannot Freely Join Across Subject Areas
Each OTBI subject area is a curated semantic layer built around one or a small number of related fact grains, with folders and columns Oracle has already joined correctly within that subject area. When you drag in a folder from an unrelated subject area, OTBI has no metadata describing how the two fact grains relate, so it either falls back to a cross join that duplicates rows, or filters down to nothing because no matching dimension exists.
Header Versus Line Grain, The Most Common Trap
A very common version of this problem happens even inside a single subject area: combining a header-level measure, such as order total, with a line-level folder produces one row per line, and the header total appears to repeat and overstate when summed naively. Check the grain of every folder you add - Oracle typically names or documents which folders are header level and which are line or detail level - before summing any measure.
When Oracle Provides A Cross-Domain Subject Area
For some common cross-functional questions, Oracle ships a subject area explicitly designed to combine domains correctly, such as one that already joins procurement and payables data at a sensible grain. Always check whether a purpose-built subject area exists for your combination before assuming you need to force two unrelated ones together.
When To Stop Fighting OTBI And Use BI Publisher
If the requirement genuinely needs data from domains with no supported subject area relationship - say, HCM headcount alongside SCM inventory value - a BI Publisher data model with multiple data sets, or a custom SQL query against the appropriate views, gives you explicit control over the join instead of relying on OTBI's implicit relationships, which were never designed for that combination.
Common pitfalls
- !Combining header and line-level folders in the same OTBI analysis and summing a header measure without checking for duplication.
- !Dragging in a second, unrelated subject area's folder because it happens to have a similarly named column, assuming that implies a valid join.
- !Trusting a row count that looks plausible without validating it against a known total - silent duplication or filtering both look like normal results at a glance.
- !Rebuilding the same broken combination in a dashboard prompt instead of fixing the underlying analysis, which just moves the wrong numbers downstream.
- !Not checking whether Oracle already ships a cross-domain subject area for the exact combination before manually forcing two single-domain ones together.
How an ERP-grounded AI assistant handles this
ERPray grounded on your Fusion OTBI metadata can flag, before you run the analysis, that two folders you have combined come from unrelated subject areas or different grains, and suggest whether a purpose-built cross-domain subject area or a BI Publisher data model actually fits the question - catching the silent duplication or empty-result problem before it reaches a report someone relies on.
Frequently asked questions
How can I tell which subject area a folder belongs to?
In the OTBI analysis criteria pane, folders are grouped under the subject area name shown at the top of the Subject Areas panel when you select it. If you have added a second subject area to the same analysis, you will see a second top-level grouping - that is your signal the combination may not be supported.
Does OTBI ever warn me when a join is unsupported?
No. OTBI does not raise an error for an unsupported cross-subject-area combination; it just returns whatever the implicit relationship (or lack of one) produces, which can look like a plausible but wrong row count. Always validate against a known total.
Is there a list of which subject areas can be combined?
Oracle does not publish a general compatibility matrix; the safest approach is to check the subject area descriptions and look for a purpose-built subject area that already spans the domains you need, or ask through Oracle Support or the reporting guide for that specific combination.
Can a dashboard prompt fix a bad multi-subject-area join?
No. A dashboard prompt filters the same underlying analysis; if the analysis itself is joining two unrelated subject areas incorrectly, filtering it narrows the wrong result set rather than fixing the join.
Related
OTBI vs BI Publisher: Which One Should You Use
Use OTBI when a business user needs ad hoc, self-service, real-time analysis with no fixed layout. Use BI Publisher when you need a pixel-perfect, scheduled, high-volume, or externally distributed document such as an invoice, check, or statutory report. Most Fusion implementations end up using both, not one instead of the other.
How-toOracle Fusion Period Close: A Practical Checklist
Closing a Fusion GL period cleanly means closing every feeder subledger first (Payables, Receivables, Costing, Fixed Assets), running Create Accounting to post everything to GL, reconciling subledger-to-GL balances, and only then setting the GL period status to Closed - not Close Pending, which still allows adjustments. Close Monitor gives a single dashboard of every subledger's close status instead of checking each module separately.
Error fixFix FBDI Import Failures From Interface Table Validation Errors
When a Fusion FBDI import job errors out, the cause is almost always bad data in the interface tables (an invalid value set, a wrong date format, or a missing required column), not the ESS job itself. Open the process's Interface Errors Report or the Correct Import Errors spreadsheet to see the exact rejected rows and reason codes, fix the source data, and resubmit from the interface tables instead of re-running the whole FBDI load.
Error fixClear An Oracle Fusion ESS Job Stuck In Blocked Status
A Blocked status on an Oracle Fusion Enterprise Scheduler (ESS) job almost always means an earlier instance of the exact same job is still running or stuck, and the job definition does not allow simultaneous requests. Find and finish, or cancel, the earlier request first; only then does the Blocked request move to Running.
How-toPaginate And Authenticate Oracle Fusion REST API Calls Correctly
Oracle Fusion Cloud REST APIs authenticate with HTTP Basic Authentication, an integration user's credentials, over HTTPS by default, and paginate with limit and offset query parameters rather than a page number. Loop on the hasMore flag in the response until it returns false to retrieve a full result set.
Error fixFix The Oracle Fusion "You Don't Have Access To This Data" Error
This generic Oracle Fusion security message means the function privilege and the data security scope did not both line up for that user on that record - most often the job role is assigned but the matching data role, security profile, or business unit and ledger context in Manage Data Access for Users is missing. Fix it by checking data access setup for the user's role, not by re-granting the same job role again.
AI for ERPFusion Cloud ERP AI: OCI Generative AI Service vs. a Private LLM
Oracle's OCI Generative AI Service is a real option for Fusion Cloud ERP, but not the only one. Compare it honestly against a private LLM for sensitive data.
AI for ERPOracle ERP AI Consulting: What a Partner Should Deliver
What to demand from an Oracle ERP AI consulting partner across EBS, JD Edwards, NetSuite, and Fusion Cloud: interface tables, APIs, and buyer questions.
Stuck on Oracle Fusion Cloud ERP?
Talk to engineers who work inside Oracle Fusion Cloud ERP every week, and who build private AI that answers these questions from your own ERP data.