RevRec · 2023

Quick Facts

• Industry: Global clinical research organisation (CRO)
• Role: Senior Consultant — RevRec workstream lead
• Timeline: ~6 months end-to-end, one full quarterly recognition cycle in parallel for shadow validation
• Team: Solo build, architect-reviewed; two finance leads validating each recognition pass against the legacy spreadsheets
• Impact: Cube and relational sources unified behind one analytical view; recognition review collapsed from three spreadsheets and an email trail into a single audited dashboard workflow, with role-enforced scope at every edit.

---

Overview

The client was a global clinical research organisation (CRO) running revenue recognition that simply could not fit in a cube alone. Clinical trials carry transactional contract-line detail — milestone payments, pass-through costs, change orders, per-visit unit billings — living directly next to the dimensional aggregations that finance wants for reporting (entity, trial, therapeutic area, service line, period). A pure-cube model loses the contract-line grain. A pure-relational model loses the rollups that the review actually consumes. We built a custom RevRec model on OneStream that merges both: a data adapter stitching cube and relational sources into one analytical surface, reverse-calculation logic for estimate-to-complete, a dashboard-driven approval workflow, and a role-based security layer that regulates who can see and edit which slice of revenue.

This is the story of designing that blended model, the reverse-calc layer that runs underneath it, and the workflow that turned recognition review from a spreadsheet shuffle into an audited single-screen process.

The Problem

CRO revenue recognition is structurally awkward. Trials run for years, not months. Billing is progress-based and blended: some deliverables recognise on milestone events (regulatory submission, last-patient-out), some on input measures (hours, visits, monitoring days), some on output measures against a target. On top of that, every period carries an estimate-to-complete projection — finance has to work backwards from the contract's expected total and the work already booked to derive what should be recognised this period, then reconcile that against what was actually delivered. None of this fits cleanly into one shape.

What we found in place:

• Two sources of truth, neither complete. The OneStream cube held the dimensional totals — entity × trial × period — that management reports needed, but it could not represent contract-line granularity at scale. The relational tables held the contract-line detail (milestones, units, pass-throughs, change orders) but had no native rollup behaviour and were being summarised by hand into the cube every cycle. The hand-summary step was where errors entered.
• Reviewers were stitching the picture across spreadsheets. A typical recognition review meant opening at least three exports — the cube's dimensional view, a relational dump of contract lines for the trials in scope, and a separately-maintained estimate-to-complete workbook — and reconciling them mentally. The numbers usually agreed. When they didn't, finding the discrepancy took half a day.
• Approval was an email chain. Once a reviewer signed off on a slice of revenue, they emailed the workstream lead. There was no audit trail tying the approval to the specific dataset, no enforcement of who could approve what, and no way to lock the slice after sign-off. The same number could be edited after approval and the approval record would not reflect it.
• Estimate-to-complete lived in someone's head. The reverse calculation — given the contract's expected total and the cumulative actuals, what is the implied current-period recognition — was being done in formulas spread across the spreadsheets. Each finance lead had their own template. The math was right; the consistency was not.
• No scope guard on edits. Anyone with cube access could in principle edit any trial's revenue numbers. The control was social, not technical. For an organisation moving toward audited financial close, "we trust the team" is not a control statement.

The brief was specific: build a custom RevRec model that lets reviewers see cube and relational data as a single view, that runs the reverse-calc logic consistently for every trial, that drives review and approval through a dashboard rather than a mailbox, and that enforces role-based access so a reviewer can only touch the trials in their authorised scope.

The hard constraints:

1. No data migration. The relational tables were the system of record for contract lines and could not be flattened into the cube — they were consumed by other systems (billing, contracts, clinical operations) and the team had no mandate to disrupt those.
2. Cube-grade aggregation behaviour. Reviewers expected to drill from a therapeutic-area total down to a trial total down to a contract line without leaving the interface. The blended view had to feel like one cube, not two stitched sources.
3. Auditable end-to-end. Every approval needed to point at a specific (trial, period, dataset version) tuple and survive subsequent edits — i.e. a later edit could not silently invalidate a prior approval.

Process

Phase 1 — Designing the cube+relational data adapter

The first job was to make the blended view real. OneStream's strength here is that it exposes a data-adapter extension point: you can register a custom adapter that surfaces an external data source as something the cube view engine can consume alongside its native dimensions. The adapter we built does three things.

It queries the relational tables on demand, parameterised by the dimensional slice that the reviewer is currently looking at. If the reviewer drills into EU / Oncology / Trial-1247 / 2023-Q2, the adapter translates that into a relational query that returns the contract lines for that trial in that period. The cube provides the rollup behaviour above; the adapter provides the leaf-level grain below.

It reconciles keys at the boundary. The cube's trial dimension and the relational trial_id had drifted apart over years of independent maintenance — same trials, different spellings, occasional aliases. We built a key-translation table maintained on the relational side and made the adapter consult it on every query. Reviewers never see the translation; they see one trial.

It caches per-view, not per-session. A typical review touches the same trials repeatedly as the reviewer drills around. The adapter caches relational results scoped to the current view execution and clears on view exit, so the next reviewer starts fresh. This is the same per-execution caching pattern that pays off on every OneStream perf engagement.

End of phase 1, a reviewer could open a dashboard, select a slice, and see contract-line detail and dimensional totals as one view. The numbers tied to the legacy spreadsheets within rounding on every trial we shadowed.

Phase 2 — Reverse-calc extender rules and custom metric calculations

With the data surface in place, the next move was to push the estimate-to-complete logic out of the spreadsheets and into the platform. The math itself is not complex: given a contract's expected total revenue, the cumulative actual revenue recognised through prior periods, and the current-period inputs (milestones hit, units delivered, hours booked), derive the current-period recognition and the residual estimate-to-complete. The complexity is in the edge cases.

We implemented this as a set of extender rules that run during the recognition cycle. Each rule handles one recognition pattern — milestone, input-measure, output-measure, blended — and each rule supports both forward and reverse modes. Forward mode takes inputs and produces the recognised number. Reverse mode takes a target (typically the expected total at contract level) and works backwards to derive what the current-period number must be, given everything booked before.

A handful of metrics needed bespoke treatment beyond the standard recognition patterns:

• Non-revenue pass-throughs. Trial costs that the CRO bills onward to the sponsor at zero margin still need to be visible in the recognition view (because they affect the sponsor invoice) but excluded from the revenue total. Custom calculation that surfaces them on the view but tags them as non-revenue for the rollup.
• Change-order true-ups. When a contract change order lands mid-period, the expected total changes and every prior period's recognition is, in principle, mis-stated relative to the new total. The reverse-calc rule handles this with a one-shot true-up entry in the current period rather than restating prior periods — matches how the finance team books these manually.
• Blended deliverables. Some contract lines blend milestone and input-measure recognition (e.g. a study setup line that recognises 50% on protocol approval, 50% on first-patient-in). Custom calc that decomposes the line into its components and runs each through the appropriate rule.

The extender rules log every input and every derivation to a calculation-trace table, so a reviewer asking "why is this number what it is?" can drill from the dashboard into the trace without leaving the platform.

Phase 3 — Dashboard-driven workflow and role-based security

The third phase was the workflow itself. The intent was to make the path from "raw data" to "approved revenue" a guided sequence of dashboard steps, not a tour of the platform's backend.

The dashboard exposes four stages: Load (pulls the period's contract-line data through the adapter, runs the extender rules, lands the recognised numbers in a draft state), Review (reviewer drills into the blended view, sees the dimensional totals and the contract-line detail side by side, can annotate any line), Approve (reviewer clicks approve on a slice; the approval is written with a snapshot of the dataset version and the reviewer's identity), and Lock (workstream lead closes the period; approved slices become read-only). Each stage is a button. The underlying OneStream workflows are not surfaced.

Sitting under this is the security model. Every reviewer is mapped to a set of authorisation scopes — combinations of entity, therapeutic area, and trial — and every dashboard action checks the actor's scope against the slice they're trying to touch. A reviewer can view dimensional totals across the whole organisation (for context), but the moment they try to drill into a trial outside their scope, the contract-line detail is suppressed. Approve and Edit actions are similarly scope-gated. The scope checks happen server-side in the data adapter and in the extender rules, not in the dashboard — UI-only guards do not survive a clever reviewer.

The audit log captures every action (load, edit, approve, lock, unlock) with actor, timestamp, slice, and dataset version. Subsequent edits to an approved slice automatically invalidate the prior approval and surface a re-approval task on the workstream lead's dashboard.

Solution

The model that came out of the build has four components.

1. Cube + relational data adapter

A custom OneStream data adapter that surfaces the relational contract-line tables alongside the cube's dimensional members. Reviewers see one view; the adapter translates dimensional drills into parameterised relational queries, reconciles keys through a translation table, and caches results per view execution. Cube provides aggregation; adapter provides grain; neither source had to move.

2. Reverse-calc extender rules

A library of extender rules, one per recognition pattern (milestone, input-measure, output-measure, blended), each supporting forward and reverse modes. Reverse mode is what powers estimate-to-complete: given an expected total and the cumulative actuals, derive the current-period number that closes the gap. Custom calculations handle non-revenue pass-throughs, change-order true-ups, and blended deliverables. Every derivation writes to a calculation-trace table that the dashboard can drill into.

3. Dashboard-driven approval workflow

Load → Review → Approve → Lock, exposed as four dashboard stages. Reviewers never see the underlying workflow configuration. Approvals carry a snapshot of the dataset version and the actor's identity; subsequent edits automatically invalidate prior approvals and surface a re-approval task. Annotations live on the line, not in a separate workbook.

4. Role-based security model

Reviewers are mapped to authorisation scopes (entity × therapeutic area × trial). Every read, edit, and approve action checks scope server-side in the adapter and the extender rules — UI-only guards are not part of the control. Out-of-scope trials are visible at the aggregate level but redacted at the contract-line level. The audit log records every action against actor and slice.

Results

| Metric | Before | After | Change | |---|---|---|---| | Data sources reviewers reconcile | 3+ spreadsheets (cube export, relational dump, ETC workbook) | 1 unified blended view | source consolidation | | Approval workflow | Ad-hoc email chain, no dataset binding | Audited dashboard, snapshotted per slice | full audit trail | | Unauthorised-edit risk | Social control only | Role-enforced at adapter and rule layer | scope guard live | | Recognition cycle length | Weeks (review + reconciliation + email approvals) | Days (single dashboard pass) | cycle compressed |

Soft outcomes:

• Estimate-to-complete stopped being a per-reviewer template. The reverse-calc rules made the math identical across every trial; finance leads stopped comparing each other's spreadsheets and started comparing the platform's outputs.
• The contract-line / cube-total reconciliation argument disappeared. Because both sides came from the same blended view, the "your spreadsheet says one number, mine says another" conversation simply stopped happening.
• Audit readiness improved without a separate audit project. Every approval already carried the slice, the actor, and the dataset version; the year-end audit pulled the trail directly from the platform rather than asking the team to reconstruct it from email.

Learnings

What worked. Treating the cube and the relational tables as peers — neither subordinate to the other — was the design decision the whole build hung on. Earlier attempts at this kind of model usually try to force one source into the other's shape (flatten the contract lines into the cube, or rebuild the cube's rollups in SQL). Both directions throw away the thing the other source is good at. The adapter pattern lets each source do what it does well and meets the reviewer in the middle. That mental model is the most portable thing I took out of this engagement.

What I'd do differently. Build the audit log and the dataset-version snapshot earlier in the workflow phase rather than near the end. We landed the approval flow first and added the version binding once the team started asking "can we prove this number was the one approved?" The retrofit was cheap, but a fresh build that bakes versioning into Load on day one is cleaner. The lesson is the same one the metadata-automation case study made: build the thing that proves correctness before you build the thing that depends on correctness being provable.

Skill developed. Designing security as a server-side concern in the data layer rather than a UI concern in the workflow layer. The scope checks live in the adapter and the extender rules because that is where the data actually flows; the dashboard's job is to render, not to enforce. That separation has held up on every regulated-finance engagement since, and it is now the first question I ask on any RevRec or close-related scope: where is the enforcement, and would a determined reviewer be able to route around it?