Integration · 2023

Oracle EBS to OneStream subledger bridge

OneStreamOracle EBSSIC Server

Quick Facts

  • Industry: Oilfield services major
  • Role: Integration Lead — Oracle EBS ↔ OneStream pipeline owner
  • Timeline: ~4 months, infrastructure-first then pipelines
  • Team: Solo build with the client's Oracle DBA pair-reviewing each pipeline cutover
  • Impact: Daily manual EBS-to-OneStream loads replaced with scheduled auto-loads and a transaction-level subledger feed into Accounts Reconciliation.

Overview

The client is an oilfield services major running Oracle E-Business Suite as the system of record for GL, subledgers, and transactional accounting, with OneStream sitting on top for consolidation, reporting, and Accounts Reconciliation. Oracle EBS is the source of truth; OneStream is where finance lives. The pipe between them was a daily manual job — until it wasn't. We stood up the SIC Server end-to-end, built GL, subledger, and transactional pipelines on top of it with the client's specific validation and scheduling rules baked in, and then extended the subledger feed so the Accounts Reconciliation workflow could see transaction-level detail rather than period summaries.

This is the story of getting an integration off a person's calendar and onto a scheduler — and why the infrastructure work underneath had to come first.

The Problem

Most days the close didn't fail; it just dragged. Three things compounded into a daily tax on the finance team:

  • Manual EBS extracts, every business day. An analyst opened Oracle, ran the GL and subledger extracts in a fixed order, dropped the files in a landing folder, and kicked off the OneStream load. If they forgot a subledger, or ran them out of order, or loaded against the wrong period, the cleanup was a half-day. Most days it worked; the days it didn't were the ones everyone remembered.
  • Reconciliation between EBS subledgers and OneStream burned analyst hours per cycle. OneStream had period-level summaries from each subledger, but the Accounts Reconciliation workflow needed analysts to chase the underlying transactions back in EBS whenever a balance looked off. The bridge between "summary in OneStream" and "transaction in EBS" was a human with two tabs open.
  • SIC Server wasn't yet stood up. The OneStream-side integration infrastructure that the platform expected to drive the pipelines didn't exist on this client's environment. Until that base layer was built, debugged, and connectivity-validated against EBS, any pipeline work on top of it was building on sand.
  • Transaction-level visibility was missing from Accounts Reconciliation. The AR workflow was reconciling at the summary grain because that was the only grain the existing feeds delivered. Variances had to be drilled into by hand, every time.

The brief: stand up the integration infrastructure, replace the daily manual extracts with scheduled pipelines that respect the client's business rules, and extend the subledger feed so AR could reconcile at the transaction level. The hard constraints were the ones that always come with EBS work:

  1. No EBS source changes. The Oracle DBA team was clear: read-side only, no triggers, no shadow tables, no changes to GL or subledger objects. The pipeline had to live entirely on the OneStream side of the boundary.
  2. Scheduling had to match the client's business calendar, not a generic nightly cron. Different subledgers closed on different cadences; the AP feed couldn't fire until the AP sub-close was complete, and the GL load couldn't fire until every subledger had landed.
  3. Validation had to be in-line, not after-the-fact. A bad row that reached OneStream's staging area was a row someone had to chase down. Validation gates had to live upstream of the load, with clear error surfaces, so the analyst saw the problem before the load fired rather than after.

The first build target wasn't a pipeline at all. It was the SIC Server.

Process

Phase 1 — Stand up the SIC Server, end-to-end

OneStream's SIC Server is the integration host: it terminates connections to source systems, runs the connector logic, and hands rows into OneStream's staging tables. On this client's environment it wasn't installed, wasn't networked to EBS, and didn't have credentials provisioned against the Oracle side. Until that was fixed, "build a pipeline" was a phrase without a referent.

The phase had three pieces, in order:

  1. Installation. Walked the client's infra team through the SIC Server install on the right host, with the right service account, against the right OneStream application. The host had to see EBS over the internal network and OneStream over the application port; both had to be confirmed before anything else happened.
  2. Connectivity validation. Stood up a thin "hello, Oracle" test connector that did one thing: open a connection to EBS, run a trivial select against GL_PERIODS, and write the row count to a log. Ran it from the SIC Server, watched it succeed, and only then declared the layer ready. The point of the trivial test wasn't the data — it was proving that credentials, network path, ODBC drivers, and the OneStream-side configuration were all aligned before any real pipeline depended on them.
  3. Debugging the integration boundary. The first attempt at connectivity didn't work. It rarely does. Service-account permissions in EBS needed a couple of read grants the DBA hadn't anticipated; the ODBC driver version on the SIC host needed a patch; the OneStream-side connection string needed the right service-name vs SID convention. Each of those got walked, fixed, and documented in a runbook that the client's infra team kept after we left.

End of phase 1 the SIC Server was production-grade: installed, networked, credentialed, validated, and runbook'd. Every pipeline that came afterwards inherited that base.

Phase 2 — GL, subledger, and transactional pipelines

With the SIC Server up, the pipelines came next. Three families, in order of dependency:

  • Subledger pipelines first. AP, AR, and inventory subledger feeds were built first because the GL load depended on them — until the subledgers had landed for a given period, the GL load wasn't allowed to fire. Each subledger pipeline pulled at its own cadence (AP daily, others on close-driven triggers), validated against the period it was claiming to load, and wrote into OneStream staging with a subledger-source tag so downstream rules could route correctly.
  • GL pipeline second. Built to consume from EBS once the subledger pipelines had signalled "complete" for the period. The GL pipeline did the period-level pull, ran validation against expected control totals (the EBS-side trial balance for the period had to match the post-load OneStream balance within tolerance), and only then committed the load.
  • Transactional pipeline third. Pulled the underlying transaction grain from EBS — journal lines, AP voucher detail, AR invoice detail — into a separate OneStream staging area sized for that volume. This was the feed that the phase-3 Accounts Reconciliation work would eventually consume.

Each pipeline got the same treatment: connection through the SIC Server, validation upstream of the load, a clear error surface (the staging area would refuse the load and write an error row that an analyst could read directly rather than dig out of a log), and a scheduling hook into the client's business-calendar engine so it fired at the right time relative to the upstream sub-close.

Phase 3 — Subledger-to-AR transaction feed

Phase 2 left OneStream with period-summary subledger data and a separate transactional staging area, but the Accounts Reconciliation workflow was still reconciling at the summary grain. Phase 3 was the work of plumbing the transactional staging area through to AR.

The shape was straightforward in retrospect: AR needed, per reconciling account, the list of underlying transactions that summed to the period balance, with enough source-system context (EBS transaction ID, posting date, source subledger, document reference) that an analyst could trace any single transaction back to EBS in one click rather than two tabs and a search box. The pipeline already had the data; the work was in defining the AR-side data model, mapping the transactional staging columns into it, and adding a load step that fired on AR's reconciliation cadence rather than the GL load cadence (AR ran on a different rhythm than the close).

End of phase 3 the AR workflow could reconcile at the transaction level. Variance research stopped being a tab-switching exercise.

Solution

The system that came out of those three phases has four named components:

1. SIC Server installation and connectivity

Production-grade install of the OneStream SIC Server on the client's environment, networked to Oracle EBS with the right service-account grants, ODBC driver versions, and connection-string conventions. Validated with a trivial connector before any pipeline went on top, and runbook'd so the client's infra team owns it post-engagement. This is the layer everything else sits on.

2. GL, subledger, and transactional pipelines

Three pipeline families, dependency-ordered: subledgers first, GL second, transactional grain third. Each pipeline runs through the SIC Server, lands in OneStream staging with source tags that drive downstream routing, and respects the dependency between subledger close and GL load. No EBS-side changes; entirely a read-side integration.

3. Schedule and validation engine

The pipelines don't fire on a generic cron. Each one is hooked into the client's business calendar — AP runs daily, GL runs after every subledger reports complete, transactional pulls run on their own rhythm — and each fires its validation step upstream of the load. Validation includes control-total reconciliation (EBS trial balance vs predicted OneStream balance) and row-level type/key checks; failure surfaces inline as an error row in staging rather than as a silent load with bad data downstream.

4. Subledger-to-AR transaction feed

The transactional staging area feeds the Accounts Reconciliation workflow at row grain. Each reconciling account in AR now has its underlying transactions attached with full source-system context, so variance research is a single click rather than a manual cross-system trace. The feed runs on AR's own cadence, decoupled from GL, so the two workflows don't block each other.

Results

| Metric | Before | After | Change | |---|---|---|---| | Load cadence | Daily manual extract + load | Scheduled auto-loads | zero-touch | | Analyst-touch per cycle | Hours (extract / validate / load by hand) | Minutes (exception handling only) | order of magnitude | | AR reconciliation granularity | Period summary | Transaction-level | grain change | | SIC Server uptime since cutover | n/a (didn't exist) | Production-grade, runbook'd | new infrastructure | | Load-failure cleanup events | Recurring, ~weekly | Validation-caught upstream | error class moved upstream |

Soft outcomes:

  • The close stopped depending on a person being at their desk. The pipelines fire on schedule whether the analyst is in the office or not; what used to be a daily blocker is now an exception-handling job that surfaces a row in staging when something needs human attention.
  • AR variance research collapsed. The transaction-level feed turned "open EBS in another tab and chase the number" into "click the row in AR." The hour-per-variance tax disappeared.
  • The SIC Server became reusable. Once it was stood up for these pipelines, the client's team has used the same SIC infrastructure for subsequent integrations without re-treading the install / connectivity / debugging work. The base layer paid for itself more than once.

Learnings

What worked. Building the SIC Server end-to-end before any pipeline touched it. The temptation on integration work is to start with the visible thing — the pipeline — and treat the infrastructure as something to fix when it breaks. On this engagement we did the opposite: stood up the base layer, validated it with a trivial connector, and only then put real pipelines on top. The downstream work was faster as a result, because every pipeline issue we hit was a pipeline issue, not a "is it the SIC Server or the pipeline?" issue. That separation was worth the up-front cost.

What I'd do differently. Build the transactional pipeline in parallel with the subledger pipelines, not after. By the time we got to phase 3, AR had been waiting on transaction-level detail for weeks, and the work to plumb the transactional staging through to AR was largely independent of the summary subledger pipelines. Sequencing it last was a default rather than a decision; running the transactional and subledger streams in parallel would have compressed phase 2 and phase 3 into a single block and shortened the engagement by a few weeks.

Skill developed. Treating the SIC Server as the unit of integration, not the pipeline. On Oracle EBS ↔ OneStream work the infrastructure layer carries more of the risk than the pipelines do — service accounts, network paths, driver versions, connection-string conventions — and the engagements that ship cleanly are the ones that pay that cost up front. That framing has changed how I scope every integration engagement since: SIC Server first, pipelines second, transactional grain only after the base layer is provably solid.