Real-time on-prem ↔ cloud retail bridge
Quick Facts
- Industry: US convenience retail chain (fuel + in-store back-office, multi-thousand-store footprint)
- Role: Project Lead — integration and master-data workstream
- Timeline: ~6 months, three phases (gateway → ODBC → MDM migration)
- Team: Myself as lead, plus two client integration engineers and one platform architect on reviews
- Impact: Manual file transfers between OneStream and PDI replaced with a live SIC ODBC connection over a hardened on-prem-to-cloud gateway; OneStream became the canonical source of truth for store, entity, and account master data.
Overview
A US convenience retail chain was running OneStream in the cloud for consolidation and planning while their petroleum and in-store back-office data sat in PDI on-premise. The two platforms talked through scheduled file transfers — CSV drops, SFTP pickups, a handful of analysts babysitting the cadence — which meant every downstream report was working on data that was hours (sometimes a full day) stale, and master data drifted between systems because each side maintained its own copy. We replaced the file-transfer plumbing with a live SIC ODBC connection, stood up the SIC Gateway server to bridge the on-prem environment with OneStream's cloud, and migrated master-data ownership into OneStream so that store, entity, and account attributes had exactly one home. By the end of the engagement, OneStream pulled from PDI on demand, dashboards reflected back-office state in real time, and the dual-maintenance loop was gone.
This is the story of how that bridge was built, in three phases.
The Problem
OneStream's strength as a unified CPM platform is undercut the moment your operational data lives somewhere else and you can only see it on a schedule. On this platform the gap between the cloud-hosted finance workload and the on-prem PDI back-office was wide enough that several otherwise-fixable problems compounded:
- Manual file transfers on a fixed cadence. PDI exported store-level numbers to a flat-file landing zone, an analyst confirmed the drop, and OneStream picked it up on the next scheduled load. The full round-trip was a half-day on a good day and a full day when anything failed validation. Anything time-sensitive — fuel margin shifts, late store-level corrections — landed in finance dashboards with a built-in lag that the business had stopped trying to close.
- No clean on-prem ↔ cloud connectivity layer. The two environments existed on either side of the corporate firewall with no managed bridge between them. Every integration attempt to date had been a point solution: an SFTP job, a scheduled task, an analyst-run export. There was no shared substrate that could carry a live query.
- No canonical master-data source. Store hierarchies, entity rollups, and chart-of-accounts attributes existed in both PDI and OneStream, with overlapping but non-identical schemas. When a new store opened or an entity was reorganised, the change had to be applied twice — once in each system — and the two records drifted whenever someone forgot a field or the cadence skipped a beat. Quarterly reconciliations had quietly become their own workstream.
- Dual-maintenance overhead absorbed real headcount. The team had two analysts whose effective job was keeping master data in sync between systems and triaging the file-transfer failures. None of that work showed up on a roadmap; all of it was load-bearing.
The brief: stand up a live, supported connection between OneStream and PDI; make OneStream the single owner of master data; and give the platform team enough operational visibility that they stop firefighting transfer failures and start shipping changes.
Process
Phase 1 — SIC Gateway stand-up and the cloud ↔ on-prem handshake
Nothing else in the project worked until the gateway worked, so phase 1 was unglamorous infrastructure. SIC Gateway is the on-prem service that brokers traffic between OneStream's cloud and a customer's internal network — it's the seam you need so that a cloud-hosted load rule can reach an on-prem ODBC source without exposing the on-prem source to the open internet. We sized and provisioned a dedicated gateway server inside the client's environment, hardened it to their security posture (least-privilege service accounts, audited outbound rules, certificate rotation on a documented cadence), and registered it with OneStream's cloud tenant.
The handshake itself was the part that took longest to validate. We ran a deliberately boring smoke sequence — ping, authenticate, pull a tiny known table, pull a larger known table, pull a table under load — and watched each step from both sides of the wire. Two failure modes surfaced and got fixed before they could mask anything later: an outbound proxy rule that intermittently rewrote the auth header, and a TLS handshake that worked at low concurrency and failed at high concurrency because the gateway service had been given a smaller worker pool than its expected fan-in. Both are the kind of issue that would have looked like "the integration is flaky" three months later if we hadn't caught them now.
By the end of phase 1, the gateway had been running clean for two weeks against a synthetic load that mirrored the eventual production fan-in, and the platform architect had signed off on the security posture. We had a bridge.
Phase 2 — OneStream ↔ PDI live ODBC connection
With the gateway in place, phase 2 was building the actual integration on top of it. SIC ODBC is the connector pattern that lets a OneStream load rule speak SQL against an external system through the gateway. The work split into three pieces.
First, the connection profile. We registered the PDI data source as an ODBC connection inside OneStream, pinned the service-account credentials, and confirmed that every query that came through the connection was tagged in the gateway's audit log with the originating user, the workflow, and the rule. That tagging mattered later — when a downstream report ran slow, we wanted to know within seconds whether the issue was the query or the network.
Second, the query layer. We replaced the old file-transfer jobs one by one with live ODBC queries. Each file that used to land in the SFTP zone became a parameterised query against PDI, embedded inside a OneStream load rule that ran on demand instead of on a schedule. We migrated the highest-value, lowest-risk loads first (store-level daily fuel volume, in-store sales summaries) and only moved on to the more complex multi-table joins once the simple ones had been running in production for two weeks without drama.
Third, operational guardrails. A live connection is more powerful than a file-transfer pipeline, and also more capable of misbehaving. We added query timeouts at the gateway, row-count sanity checks on every load, and an explicit alerting path for the integration team if a query returned outside its expected envelope. If a query that usually returned 50,000 rows came back with 5 or 5,000,000, the load rule short-circuited and paged a human rather than overwriting the cube.
By the end of phase 2, the file-transfer cadence had been retired for the migrated loads, latency on dashboards backed by those loads dropped from "yesterday" to "now", and the analysts who had been babysitting the SFTP drops were freed up for the next phase.
Phase 3 — Master-data migration and decommissioning the legacy MDM
Phase 3 was the structural change that the first two phases had only enabled. With a live connection in place we could finally answer the question the team had been quietly asking for years: which system owns store, entity, and account master data? The answer the project committed to was OneStream — and the work was to make that real.
We built a custom MDM dashboard inside OneStream that exposed the full master-data surface: store attributes, entity rollups, account properties, every field the legacy system had carried plus the OneStream-native ones the team had been maintaining separately. The dashboard included guardrails logic on every editable field — required-field checks, type checks, referential-integrity checks against the live PDI lookup tables, and a rollup-consistency check that ran before any save was committed.
In parallel, we wrote programmatic property-update routines as OneStream extender rules. Before this project, a bulk property change — re-tagging two hundred stores into a new district, for instance — meant an analyst opening the dimension editor and editing each member one by one. The extender rules accepted a structured input (CSV, dashboard form, or API call) and applied the property change across the matching members in one operation, with full audit logging and a rollback path. What used to be a multi-day analyst exercise became a single submit-and-confirm.
The cutover itself was the careful part. We ran OneStream and the legacy MDM in shadow mode for one full quarterly cycle — both systems received the same changes, the two outputs were diffed nightly, any drift was investigated before it could compound — and only flipped the canonical pointer to OneStream once the diff had been clean for three consecutive weeks. Once the flip happened, the legacy MDM was put into read-only mode for an additional month as a fallback, then formally decommissioned.
Solution
The integration that came out of those three phases has four named components:
1. SIC Gateway (on-prem ↔ cloud bridge)
A hardened on-prem service that brokers all traffic between the client's internal network and OneStream's cloud tenant. Runs on a dedicated server inside the client's environment, registered with the OneStream tenant, and operated under the client's existing change-management process. Owns certificate rotation, audit logging, and the outbound traffic envelope. Every other component on this list depends on it.
2. OneStream ↔ PDI live ODBC connection
A registered ODBC connection inside OneStream, routed through the gateway, that exposes PDI as a queryable data source to any OneStream load rule. Replaces the previous file-transfer pipeline for every migrated load. Tagged audit logging on every query (user / workflow / rule), enforced query timeouts, row-count sanity checks per load, and an alerting path that short-circuits the load and pages a human if a query returns outside its expected envelope.
3. Master-data dashboards and guardrails
A custom OneStream dashboard that owns the master-data surface for store, entity, and account attributes. Every editable field runs through a guardrails layer (required-field, type, referential-integrity, rollup-consistency) before a save is committed. The dashboard is the human-facing side of OneStream-as-MDM; behind it sits the property-update infrastructure that the extender rules drive.
4. Programmatic property-update extender rules
Extender-rule routines that apply bulk property updates across many dimension members in one operation. Accept structured input (CSV upload, dashboard form, or API call), validate against the same guardrails the dashboard uses, apply the change atomically, log the operation for audit, and expose a rollback path. Replaces the manual one-by-one edits that bulk changes used to require.
Everything's wrapped inside OneStream's native rule and dashboard surface, so there's no external middleware in the critical path — the gateway is the only on-prem service, and from OneStream's perspective the entire integration looks like ordinary load rules and dashboards.
Results
| Metric | Before | After | Change |
|---|---|---|---|
| PDI → OneStream data movement | Manual file transfer, scheduled batches | Live SIC ODBC over the gateway | real-time |
| Authoritative MDM source | Two systems (PDI + OneStream) maintained in parallel | One (OneStream), with PDI consuming downstream | dual → single |
| Bulk property updates | Manual edits in the dimension editor, one member at a time | Programmatic via extender rules, structured input | manual → programmatic |
| Downstream data latency on key dashboards | Hours to a full day | Seconds (<5min end-to-end on the live loads) | day → minutes |
| Transfer-failure tickets per sprint | ~3-4 | 0 since cutover | error class eliminated |
Soft outcomes:
- The dual-maintenance loop disappeared. The two analysts who had been keeping PDI and OneStream in sync moved onto roadmap work for the first time in years. The "did anyone update the other system?" question stopped being asked.
- Master-data changes became a single confident operation. Opening a new store, reorganising a district, or re-tagging an account no longer required cross-system choreography; the change happens in OneStream and PDI sees it through the live connection.
- The platform team got operational visibility for the first time. Tagged audit logs on every query, row-count sanity checks on every load, and the gateway's own telemetry meant that when something looked off, the team could see it in minutes rather than reconstructing it from analyst memory.
Learnings
What worked. Sequencing the phases strictly — gateway first, ODBC second, MDM migration third — kept each layer of risk isolated. Every phase had a clear exit criterion that didn't depend on the next phase being even started, so when something needed extra hardening (the TLS-handshake-under-concurrency issue in phase 1, the row-count alerting in phase 2) we could fix it in place without compromising downstream timelines. The shadow-mode quarter before flipping the MDM canonical pointer was the single highest-leverage decision in the project; it caught two edge-case drift patterns that would have been very expensive to discover post-cutover.
What I'd do differently. Stand up the row-count sanity checks and the tagged audit logging in phase 1, not phase 2. They were trivial to add once we knew what to look for, and the two weeks of clean-running-but-undiagnosable gateway traffic at the start of phase 1 would have been more useful with them in place. The lesson: invest in operational visibility before you need to debug something, because the moment you need it is the moment you can't add it.
Skill developed. Designing on-prem ↔ cloud integrations as a substrate rather than a pipeline. The instinct on a project like this is to think in terms of "data going from A to B" and to optimise the path. The actual unlock is to build the bridge once, make it boringly reliable, and then let every downstream integration sit on top of it as ordinary configuration. The gateway-then-ODBC-then-MDM sequence is now the default mental model I bring to any hybrid-environment engagement, and it has shortened the discovery phase on every project since.