The cross-subject-area join OTBI cannot make
The report needs two subject areas. OTBI errors, or answers wrong, and every workaround freezes the report at creation. One model in an owned warehouse makes every join once.
1 Module facts, extracted whole: invoices, payments, assets, journals, costs. Native grain, nothing pre-joined.
2 The shared entities: suppliers, customers, accounts, assets, workers, ledgers. Fusion keeps a copy per module; here they are extracted once.
3 Each entity becomes one conformed dimension with one key, and every fact joins it. Any two facts are already joined through the dimension they share. One hard gate at load: orphan keys must be zero.
4 Everything lands in bronze, the raw tier of your cloud, and loads into the star.
5 The warehouse star is just a database in your own cloud, amounts joined to lookups, security joined on ledger. The conformed Oracle model is public, drill it here.
6 Read it in Power BI, Tableau, ThoughtSpot, or a custom app on the same star; ours is the console. A due date next to a distribution, a worker next to a cost: one query each, because the join is in the model, and a release cannot rename what you own.
deploys Everything ships from Git: a new key or column is a reviewed commit, inherited by every report.
The record, in Oracle’s own forum: the Payables thread where adding Due Date from the installments subject area breaks the analysis, the Fixed Assets thread where combining depreciation and transactions errors even when both sides carry Asset ID, the HCM thread where a workforce-to-payroll-costing join ignores the manager filter and returns every costing in the system, and the Procurement thread where purchase orders and receipts cannot be joined at all. The workaround mechanics are documented by practitioners: hand-issued logical SQL where the columns are fixed at creation and the names must be known in advance, and an ecosystem of paid tools whose stated reason to exist is this one limitation.
answer 1,412 open invoices · due dates and account coding on every row · the two-subject-area report, as one query
◆ The result, rendered— the two-subject-area report, as one grid.
| Supplier | Invoice | Due date | GL account | Open amount |
|---|---|---|---|---|
| Alder Metals LLC | INV-20841 | 2026-08-07 | 01-5210-620 | 18,450.00 |
| Alder Metals LLC | INV-20907 | 2026-08-21 | 01-5210-620 | 7,980.00 |
| Corvid Packaging Co | INV-20855 | 2026-08-10 | 01-5310-640 | 12,215.50 |
| Corvid Packaging Co | INV-20918 | 2026-08-28 | 01-5310-640 | 6,077.25 |
| Halvorsen Freight Inc | INV-20866 | 2026-08-14 | 01-5420-660 | 4,308.75 |
| Pinegate Tooling Ltd | INV-20872 | 2026-08-15 | 01-5230-620 | 22,640.00 |
| shown (6 rows) | 71,671.50 | |||
Illustrative suppliers and figures; the shape is the deliverable. Six of 1,412 rows, supplier security applied: coding from the invoice fact, due date from the installment fact, one query. Next month’s columns are two lines, not a new report.
◆ The record, in full— four modules, one failure, in Oracle’s own forum.
| The evidence | What it says |
|---|---|
| Payables | Invoice number and account coding live in the transactions subject area; the due date lives in installments. The moment the due date is added, the analysis breaks. The customer asks whether a union can be forced; a union requires column-for-column type matching, which this pair does not have. |
| Fixed Assets | Depreciation and asset transactions are separate subject areas. Filtering depreciation by retirement status needs both. Combining them errors, and adding Asset ID from each side, the obvious key, does not help, because the join OTBI permits is decided by the model, not by the reporter. |
| HCM to Payroll | A manager-scoped workforce query joined to payroll costing keeps the manager filter on one side only. The costing side returns every row in the system, a report that runs, looks right, and answers wrong, the most expensive kind of failure. |
| Procurement | Purchase orders and their receipts, the most ordinary operational pairing there is, cannot be joined across their two subject areas at all. |
| The mechanism | OTBI only joins subject areas through dimensions Oracle chose to conform. Where no shared dimension exists, the query errors; where one half-exists, filters silently stop traveling. The reporter cannot add the missing join, because the semantic model is Oracle’s, not theirs. |
◆ The owned join, mechanics— conformed dimensions, one key, every fact.
| Piece | How it works |
|---|---|
| One dimension per entity | Supplier, customer, account, asset, worker, ledger: each shared entity is extracted once and keyed once. Every fact table carries that key. The join between any two facts is the pair of joins they already have to the dimension between them. |
| Filters that travel | A filter on a conformed dimension constrains every fact joined to it, in one query plan. The manager filter that OTBI dropped on the costing side cannot be dropped here, because there is only one worker dimension for both facts to obey. |
| Columns any time | The logical-SQL workaround fixes its columns at creation. A star does the opposite: a report is a query, and next month’s query may select any column the model carries, or a column added by one reviewed commit. |
| Release-proof | Quarterly releases rename subject areas and rearrange folders, and every report built on them is rework. The owned model’s names are yours; upstream renames are absorbed once, in the extraction layer, not in every report. |
| Gates before anyone looks | No fact row may carry a key its dimension does not know: orphan keys must be zero. Row counts against the manifest, duplicate keys, and the ledger tie. A failed gate stops the load, not the analysis. |
◆ The pairs that fail, and the join that replaces each— the forum’s four cases, restated as one-line queries.
| In OTBI | In the owned star |
|---|---|
| Invoices ⋈ installments | The invoice fact and the installment fact share the invoice dimension. Due date, account coding, and supplier arrive in one result, and the supplier dimension is the same one Payables aging uses, so the numbers agree across reports. |
| Depreciation ⋈ transactions | Both facts share the asset dimension, which carries retirement status and date as attributes. Filtering depreciation to active assets is a WHERE clause, not a modeling request to Oracle. |
| Workforce ⋈ costing | Both facts share the worker dimension, and row security joins on it. A manager sees their people’s costings and no one else’s, on both sides of the join, by construction. |
| Orders ⋈ receipts | Both facts share the purchase order line dimension. Ordered, received, and open quantities sit in one row per line, the three-way match a buyer actually reads. |
◆ The due-date walk-through— the Payables thread’s report, built in one query.
The request from the thread: open invoices with invoice number, account coding, and due date, by supplier. In OTBI that is two subject areas and a broken analysis; the customer’s last hope was a union with column-for-column type matching that the pair cannot satisfy. In the owned star it is one query: the invoice fact carries the coding, the installment fact carries the due date, both join the invoice dimension, and the supplier dimension groups the result. 1,412 open invoices return with every requested column on every row. When the treasurer asks next month for payment terms and discount dates beside them, that is the same query with two more columns, not a new report, because the model already carries them. Nothing was unioned, nothing was frozen, and the next quarterly release cannot touch it.
Published clean-room: the pattern is shown, no client’s SQL, structures, or naming appears here, and never will.
- OTBI
- Oracle Transactional Business Intelligence, Fusion’s ad-hoc report tool.
- subject area
- A packaged slice of Fusion data OTBI can query. Joins between them are Oracle’s to grant.
- conformed dimension
- One shared lookup, one key, joined by every fact. The owned fix.
- logical SQL
- OTBI’s query language. The workaround medium, columns frozen at creation.
- UNION
- Stacking two results. Requires matching column types, which failing pairs rarely have.
- grain
- What one row means in a fact table. Kept native here, never pre-joined.
- orphan key
- A fact row pointing at a dimension row that does not exist. Must be zero.
- BICC
- BI Cloud Connector, Oracle’s bulk export service for Fusion data.
- DAG
- A scheduled chain of jobs. Airflow runs the nightly load as one.
- star schema
- Facts joined to small lookup tables. The shape where joins are already made.
- ⋈
- A join. Here it belongs to the model, not the report.