Bank reconciliation, days to seconds
Quick Facts
- Industry: Retail
- Platform: OneStream + PDI, connected over SIC
- Impact: Department-level reconciliation between the general ledger and the bank went from a 2–3 day multi-person query chase to seconds of self-service, with per-department security and a rolling 13-month window.
Overview
Each department reconciles its general-ledger transactions against the bank's records, and on this platform that was a slow, relayed process: a department lead who needed detail on a line had to raise a query, wait for someone to fetch the matching record from the bank, then reconcile by hand — two to three days for an answer. We built the reconciliation inside OneStream: every department's transactions load into a table, a dashboard puts them in front of the lead, and clicking a line item pulls that record's detail live from PDI over SIC. The same answer that used to take days now takes seconds — and the lead gets it themselves.
The Problem
- Reconciliation was a relay, not a task. To dig into a single transaction, a department lead opened a query to whoever owned the bank relationship, that person retrieved the matching detail, and only then could the two sides be reconciled. The round-trip was 2–3 days.
- The detail lived somewhere the lead couldn't reach. The transaction sat in the general ledger; the bank's matching record sat outside OneStream. Nobody at the department level had a single screen that showed both.
- Volume and access both needed controlling. Pulling everything for everyone would be slow and would expose every department's numbers to every user — neither acceptable.
Process
Bring every department's transactions into one table
All department transactions load into a table inside OneStream, which becomes the spine of the reconciliation. A dashboard renders those transactions so a department lead is looking at their own ledger lines directly, not a report someone ran for them.
Make any line item drill straight through to the bank record
The unlock is the drill-through: a lead clicks a line item in the dashboard table, and the system pulls that specific record's detail live from PDI over SIC. There's no query to raise and no one to wait on — the matching detail comes back directly from the client's database against the line they clicked, and the reconciliation happens on screen.
Keep it scoped and secure
Two guardrails make it production-safe:
- Per-department security. A user assigned to a department sees only that department's transactions. Reconciliation is self-service without exposing one department's numbers to another.
- Rolling 13-month window. Rather than load all history, the system loads a rolling 13 months from the chosen period — e.g., loading in December brings December plus the twelve prior periods. It keeps the working set fast and bounded.
Everything — the table, the dashboard, the drill-through, the security, and a full audit-trail history — is custom-built on top of OneStream.
Solution
1. In-OneStream transaction table + dashboard
Every department's transactions land in a OneStream table and surface on a dashboard, so leads work their own ledger lines directly.
2. Live click-to-detail drill-through
Clicking a line item pulls that record's detail live from PDI over SIC, against the exact line selected — replacing the query-and-wait relay with an on-screen answer.
3. Per-department security + rolling 13-month window
Users see only their own department's data, and the system loads a rolling 13 months (chosen period plus the twelve prior) to keep the working set fast and bounded. A full audit-trail history sits underneath.
Results
| Metric | Before | After | Change |
|---|---|---|---|
| Time to reconcile a line | 2–3 days (raise query → fetch from bank → reconcile) | seconds (click → live detail → reconcile) | days → seconds |
| Who can do it | relayed through the bank-relationship owner | the department lead, self-service | direct |
| Data scope per user | uncontrolled | own department only, rolling 13 months | scoped + secure |
Soft outcomes:
- The reconciliation stopped being a ticket. A department lead answers their own question on screen instead of opening a request and waiting on two other people.
- The working set stayed fast. The rolling 13-month window keeps the dashboard responsive without anyone pruning history by hand.
Learnings
What worked. Putting the drill-through where the work happens. The slow part was never the matching itself — it was the relay to fetch the bank record. Pulling that detail live, against the exact line the lead clicked, collapses the relay entirely.
What I'd do differently. Settle the data-window rule early. The rolling-13-month decision is what keeps the system fast, and it's much cleaner to design the loads around it from the start than to bolt a window onto loads that assumed all history.
Skill developed. Designing live drill-through over SIC as a first-class interaction, plus the per-department security and bounded-window discipline that make a self-service financial tool safe to hand to non-technical leads.