Integration · 2024

Quick Facts

• Industry: Records management / govtech firm
• Role: Integration Lead
• Timeline: ~3 months
• Team: Solo build, architect-reviewed, with the client's finance lead validating every cycle
• Impact: GL feed cut from day-old manual exports to real-time REST pulls, audit trail surfaced for every load, on-demand sync available from inside OneStream.

---

Overview

Consolidation is only as good as the freshness of the GL feed underneath it. The client was running monthly close on OneStream against Netsuite-sourced data that came across through manual CSV exports — accurate when it arrived, but often a day behind reality and untraceable to a specific export run. We wired the feed straight to the source with a direct Netsuite ↔ OneStream REST API integration, folded the pull and the load into a single transaction inside OneStream, and added an on-demand sync surface so finance could trigger a fresh pull without leaving the platform. Net effect: data freshness went from "day-old, depending on who exported when" to "real-time, with an audit trail for every load."

The Problem

The records-management business runs a standard cloud GL on Netsuite and a OneStream consolidation cube on top. The seam between them was the manual export. Three things made it painful:

• Staleness was structural. The export was an analyst task that fired once a day at best, sometimes once every couple of days during quiet periods. Consolidation cycles routinely ran against data that had been written hours or a full day earlier; reconciliations against the live ledger surfaced drift that took analyst time to investigate.
• No on-demand path. If finance needed a fresh number for an ad-hoc review — a board question, an audit follow-up — they had to ask the analyst to run a fresh export and reload. The round-trip was rarely under an hour even when everyone was available.
• No audit trail across the handoff. The "data came from Netsuite" claim was true in the abstract, but there was no record of which export run had produced which row that ended up in OneStream. When a consolidation number looked off, the investigation always opened with "let's see if we can figure out which export this came from," which was sometimes possible and sometimes not.

The brief: build a direct connection that pulls from Netsuite live, runs inside OneStream's data-load orchestration so it's one transaction not two, and carries an audit trail through the whole path.

Process

Phase 1 — Netsuite REST client (auth + rate limits)

Netsuite's REST API is rich but unforgiving. The first phase was building a client that handled the parts the GL pull would have to deal with day in and day out: token-based authentication that rotates on schedule, rate-limit awareness with backoff that doesn't fail the cycle, pagination through arbitrarily large result sets, and structured error surfacing so any failure could be diagnosed without opening a debugger.

The client was built as a OneStream Extensibility rule rather than as external middleware. That decision mattered: the integration ran inside the same process space as the load it fed, so there was no second hop to introduce its own failure modes. Authentication tokens lived in OneStream's environment configuration, encrypted at rest, with no separate secrets management to keep in sync.

By the end of phase 1 the client could read GL data from Netsuite reliably, including the edge cases — large result sets, paginated reads, transient rate-limit responses, expired tokens — without operator intervention.

Phase 2 — Pull-and-load as one transaction

The next step folded the pull into OneStream's data-load orchestration. The principle was simple: there should never be a state where Netsuite data exists outside OneStream's transaction boundary. Either the pull succeeds and the load commits, or one of them fails and the cube state is exactly what it was before the cycle started.

We implemented this by hooking the REST client's output directly into the load's input stream — no intermediate staging table, no temp file, no separate validation pass against staged data. The load reads each row as the client produces it, runs validation inline, and commits to the cube only after the full pull has been read. If the pull stalls, the load aborts and the cube is untouched. If the load's validation surfaces an issue, the pull is rolled back and the operator sees the same error trail as if it were any other in-OneStream validation failure.

The transactional behaviour also gave us the audit trail almost for free. Each pull carries a generated run-id; each loaded row carries the run-id of the pull it came from; the audit table records the run-id with timestamp, requesting user, Netsuite query parameters, and row count. The "which export did this come from" question that used to be a forensic exercise became a single-table lookup.

Phase 3 — On-demand sync surface

The third phase added the surface that let finance trigger a fresh pull on demand. We built it as a dashboard button inside OneStream with two affordances: a "sync now" trigger that fires the same pull-and-load transaction with the user's identity attached, and a "what would change" preview that runs the pull but holds the load in a comparison view so the user sees what would update before committing.

The preview path was the piece finance valued most. It meant the question "is anything new in Netsuite since the last close?" could be answered in seconds without committing anything; if the answer was "nothing material," nothing changed and the audit table stayed quiet. If the answer was "yes, here are 14 new transactions," the user could commit the load with one more click and the audit entry recorded what they had reviewed.

Solution

Four components:

1. Netsuite REST client

A OneStream Extensibility-rule client that handles authentication, token rotation, pagination, rate-limit backoff, and structured error surfacing. No external middleware. Credentials live in OneStream's environment configuration encrypted at rest.

2. Pull-and-load transactional orchestration

The REST client's output feeds directly into OneStream's data-load orchestration as a single transaction. No staging table, no temp file. Either the pull succeeds and the load commits, or the cube state is unchanged. Inline validation runs on the live stream rather than against staged copies.

3. On-demand sync surface

A dashboard button inside OneStream with two paths: "sync now" (fire the transaction, commit the load) and "preview changes" (fire the pull, hold the load, show the diff). The preview path is what made the surface usable for ad-hoc finance questions.

4. Audit trail

Each pull carries a generated run-id; each loaded row records the run-id, requesting user, Netsuite query parameters, and row count. The "which export did this row come from" lookup is one query.

Results

| Metric | Before | After | Change | |---|---|---|---| | Data freshness for consolidation | day-old (analyst-driven exports) | real-time at pull time | real-time | | Manual touchpoints per cycle | multiple exports + imports | zero (or one preview-then-commit click) | eliminated | | Audit trail across handoff | none | run-id on every row, full provenance | introduced | | On-demand sync availability | no (analyst round-trip) | yes (in-platform, finance-self-serve) | self-serve | | Reconciliation drift investigations | several per close | rare | near-zero |

Soft outcomes:

• Consolidation got quieter. Once the feed was live and audited, the recurring "is this number from a recent export?" conversation stopped happening.
• Audit support got faster. The "show me where this row came from" question now has a single-query answer; auditors got their reconciliation walk in minutes instead of half-days.
• Finance ad-hoc reviews got real. The preview surface meant questions that used to require an analyst round-trip got answered in the meeting they were raised in.

Learnings

What worked. Treating the pull and the load as one transaction was the call that made everything else simpler. Staging tables introduce their own state and their own failure modes; eliminating them eliminated a whole category of "the staged data and the cube disagree" investigations. The same principle generalises to any source-to-target integration where the two ends already have transactional semantics — push the boundary outward, not inward.

What I'd do differently. Build the audit trail in phase 1, not phase 2. Once the pull-and-load was in place the audit fields fell out naturally, but if I'd designed the client with run-ids as a first-class concept from the start, the integration code would have been a little cleaner and the audit-table schema wouldn't have needed its retrofit pass.

Skill developed. Reading API rate-limit responses as a contract rather than as an exception. Netsuite (like most live source systems) tells you precisely how to back off and when to retry; building the client to honour those signals up front means the integration runs at the API's natural pace without needing the operator to babysit it during heavy reads.