What BICC does not carry, and the BIP pipeline that does
Four kinds of data never leave Fusion through BICC. The report tools hit their walls. Three tools, three jobs, one owned landing zone.
1 BICC, the BI Cloud Connector, is Oracle’s own bulk exporter. It carries the volume, every module, on schedule.
2 BIP, BI Publisher, is Oracle’s report engine, called over SOAP, its API. It carries only what BICC cannot see, in slices that never near the 100k wall.
3 A plain Python extractor paces the slices, retries failures, writes a row-count manifest. Its Airflow DAG embeds sanity checks: duplicate keys, counts against the manifest, null ledger keys. A failed check stops the load, not the close.
4 Everything lands side by side in the top tier of your cloud box, the raw layer called bronze, and loads straight down into the star.
5 The warehouse star is just a database in your own cloud, GCP, Azure, or AWS: one big table of amounts joined to small lookup tables. Security joins the amounts on ledger, so access travels with the data. The conformed Oracle model is public, drill it here.
6 The star feeds Power BI, Tableau, ThoughtSpot, or a custom app; ours is the console. No export caps, no timeouts, each person seeing only their ledgers. Every wall above, closed.
deploys Everything ships from Git. One Oracle quirk to respect: uploading report artifacts resets folder permissions, so every deploy ends by reasserting them.
Oracle’s own references on these limits: the documented analysis and report limits, the community threads asking Oracle to lift the export cap and extend the 25,000-row return, and the support note on agent deliveries capping at 25k. BICC itself is documented in Oracle’s guide, Creating a Business Intelligence Cloud Extract.
◆ What BICC does not carry— four categories that never arrive, no matter which PVOs you schedule.
| The gap | Why it matters |
|---|---|
| Tables with no PVO | BICC extracts view objects, not tables. Not every Fusion table is exposed as one, and if the object does not exist, no amount of scheduling produces the data. |
| Package-function values | Some columns are computed at report time by PL/SQL inside the application. The journal action user is the classic case, produced by a GL report package function, real on the printed report, absent from every extract. |
| DFF audit metadata | Custom descriptive flexfields carrying audit context are sometimes omitted from the PVO even when the base object is covered. The row arrives, the column you customized does not. |
| Security mappings | Who can see which ledger. The user-to-ledger access data ships through a BIP report as Oracle's standard extraction route, not through BICC at all. |
Two quieter constraints ride along: the BICC schema is Oracle-defined, denormalized with CamelCase names that differ from the database tables underneath, and extract windows run on Fusion's scheduler, which pauses for Fusion patch maintenance whether your close is running or not.
◆ Where OTBI and BIP hit their own walls— the limits teams work around every close, until they stop working around them.
| The wall | What it looks like in close week |
|---|---|
| OTBI export ceiling | Oracle documents the trio: analyses cap at 65,000 rows, and the Excel export at 25,000 rows and 50,000 cells. The community threads carry the consequence in the users’ own words: general ledger detail for the auditors extracted in batches, and teams discovering the wall a month before go-live. |
| OTBI joins stop at the subject area | Each subject area is its own island. The question that spans payables and the ledger in one result is exactly the question OTBI cannot join. |
| No point-in-time history | OTBI reads the live transaction tables. What the balance looked like last Tuesday, before the correction, is not a question it can answer. |
| BIP is a formatter, not an analyst | BI Publisher renders documents. Its SQL data sets run under memory and duration guardrails, and in the field a heavy data set slows sharply as row counts approach the hundred-thousand mark and times out beyond it. Bursting has its own quirks, nothing about it is interactive, and the same SQL does more as a model in a warehouse. |
◆ The supplement pipeline— BIP as a programmatic extractor for exactly what BICC misses.
| Step | The pattern |
|---|---|
| Data model | A custom .xdm data model holding the one query BICC cannot answer, stored under the Custom reports path in the BIP catalog. |
| Invocation | A plain Python extractor calls the SOAP CatalogService runReport operation and receives the payload as CSV or XML. It slices the query by period, ledger, or row window so no single run approaches the point where BIP slows, retries what fails, and writes a manifest of row counts per slice. Versioned in Git, owned by the client, no license. |
| Schedule | An Airflow DAG orchestrates the slices nightly, Cloud Composer when the cloud is Google, and an OIC schedule serves the same role where the client prefers Oracle-side orchestration. Every file lands in object storage beside the BICC extracts, one landing zone for the warehouse load. |
| Sanity checks | The DAG embeds data-quality gates between land and load: duplicate keys, row counts against the manifest, null ledger keys, period totals against yesterday. A failed gate stops the load and pages the pipeline owner, so a bad file never reaches a dashboard. |
| Deployment | The data model and report definitions live in Git and deploy through the SOAP uploadObject call, the same discipline as every other pipeline artifact. |
| The quirk | uploadObject resets folder permissions to default on every upload. This is Oracle's documented behavior, not a bug in your pipeline. The deploy must always finish with applyObjectSecurityXML to reassert the access model, or Monday's deploy quietly unshares Friday's reports. |
Every hop above is a named API, so nothing in this pipeline is hand-waved:
| Connection | The API that makes it |
|---|---|
| Fusion to the extractor | The BIP SOAP CatalogService runReport operation carries the supplement query. The bulk BICC files come out through their delivered storage target, retrieved by the same Python tooling. |
| Extractor to storage | The cloud storage client writes each slice: the GCS client on Google Cloud, the S3 client on AWS, the ADLS client on Azure. Same script, different import. |
| Storage to warehouse | Native load jobs, BigQuery load in this instance, pick up the files on arrival. No middleware, no per-row inserts. |
| Orchestration to everything | Airflow operators call each hop in order, retry on failure, and record the run. The DAG is the pipeline's audit trail. |
| Git to Fusion | uploadObject deploys the data models, applyObjectSecurityXML reasserts the permissions. The two always travel together. |
◆ The same pipeline carries the security model— who can see which ledger travels with the data, not after it.
| Layer | How it is enforced |
|---|---|
| The extract | User-to-ledger and user-to-business-unit access comes out of Fusion through the standard BIP security report, scheduled daily on the same pipeline, landing in the warehouse as its own fact table of user and ledger rows. Joiners and leavers refresh on demand, every change logged. |
| The join | The security fact joins the journal fact on ledger. That one join is the security model: a person’s rows are exactly the rows whose ledger appears against their name, nothing interpreted, nothing configured twice. |
| The enforcement | The BI tool’s row-level filter rides that join: the signed-in identity, mapped to the Fusion user by email, filters every dashboard and every Analyst answer before it renders. Where the warehouse supports row-access policies, the same table drives a second gate underneath, and the audit log keeps all of it. |
The point worth pausing on: the warehouse does not invent a security model, it mirrors the one Fusion already enforces, refreshed daily by the same supplement pipeline this page describes. Users see what they can see in the source, no more.
◆ The case, generalized— a daily journal entry report a global consumer-products group actually runs.
The controller's team needed a daily journal entry report with the full audit picture: who entered it, who posted it, and the approval context around it. The amounts were easy, BICC carries journal headers and lines all day. The audit columns were not, the action user is computed by a package function at report time and appears in no PVO. The old answer was a person exporting from the application every morning.
The build followed the dataflow above exactly. One custom BIP data model holds only the columns BICC cannot see. The Python extractor slices its runs so BIP never nears the hundred-thousand-row wall. An Airflow DAG paces the slices nightly into object storage beside the BICC files, and the star schema joins them to the journal detail on the client's own cloud, Google Cloud in this instance, the same pattern unchanged on AWS or Azure. The report that struggled near a hundred thousand rows became a warehouse that answers across one and a half million journal rows in seconds, no timeouts, with row-level security enforced where the data lives. The issue was not worked around. It was solved. The report became a query. The morning export became nobody's job. And the user-to-ledger security extract rode the same nightly pipeline, joined to the journal fact on ledger, so the BI tool shows each person only the ledgers Fusion grants them. And because the read-only AI Analyst runs on the same star, the journal detail it drills into now includes who acted, which is usually the question the flag was really asking.
Published clean-room: the pattern and the Oracle-documented mechanics are shown, the client's SQL, structures, and naming are not, and never will be.
- BICC
- BI Cloud Connector, Oracle’s bulk export service for Fusion data.
- PVO
- Public view object, the unit BICC extracts. No PVO, no data.
- star schema
- One table of amounts joined to small lookup tables. The fast shape.
- BIP
- BI Publisher, Oracle’s report engine. Here, a programmatic extractor.
- SOAP
- The web-service API style BIP answers on.
- OTBI
- Oracle’s ad-hoc reporting tool on seeded subject areas.
- DAG
- A scheduled chain of jobs. Airflow runs this pipeline as one.
- REST
- The modern web API style. Right for one record, wrong for bulk.
- DQ
- Data-quality checks. Dupes, counts, nulls, caught before the load.
- GL · AP · AR · FA
- Ledger, payables, receivables, assets. The finance modules.
- data model
- The .xdm file holding a BIP query. Here, the one query BICC cannot answer.
- the quirk
- uploadObject resets folder permissions. Deploys must end with applyObjectSecurityXML.