EPM Tooling · 2022

Quick Facts

• Industry: Global consumer hardware brand
• Role: EPM Developer — OneStream tooling and Excel add-in extensions
• Timeline: ~6 months across two planning cycles
• Team: Solo build, finance lead validating each tool against real cycle work, IT ops reviewing deployments
• Impact: Clear-related developer tickets fell to roughly zero; finance moved from filing requests to self-serve analysis directly against the cube; review pages stopped dragging.

---

Overview

Every clear was a fresh ticket — until it wasn't. Wiping a slice of cube data ahead of a reload meant a developer cutting a bespoke business rule for that one scenario, that one entity set, that one time window. Finance couldn't touch the cube directly, so any reshape or reload turned into a queue item. We replaced the per-scenario clear rules with a single parametric utility, opened up live cube access inside the Excel add-in finance already lived in, layered macro-driven workbooks on top for self-serve analysis, and shipped targeted code modifications that took the laggier review pages back into responsive territory.

A tooling-led engagement: small, sharp tools that moved the line between "developer task" and "analyst task" — and a perf pass that fell out of the same instrumentation work.

The Problem

OneStream is generous to developers and stingy with end users. The platform team can compose just about any rule they want; the finance team gets whatever surface the developers chose to expose. Four things were compounding:

• Clearing a cube intersection required a custom rule per scenario. Every time finance needed to wipe a slice — a stale forecast version, a re-cast actual, a misposted entity — a developer wrote a business rule scoped to that exact intersection. The folder of one-off clear rules had grown past 60 entries, half of them unowned.
• Finance couldn't touch OneStream data directly. They could open canned cube views inside the platform UI, but the moment they wanted to pivot, slice differently, or pull the same data into their spreadsheet model, they had to ask. "Can you export X for me" was a multi-times-a-week request bouncing through the developer queue.
• Analyst spreadsheets lived outside the cube. Because the cube was hard to reach, the team had built a parallel universe of extracts, manual joins, and hand-typed lookups. Any number in the cube also lived in three Excel files, and reconciling them was its own sub-process.
• Some review pages were noticeably laggy. The weekly forecast review and the monthly close walkthrough took long enough to render that analysts started other work while waiting, lost context, and came back. Nothing was broken; it was just slow enough to break flow.

The brief was unglamorous and specific: stop having developers write one-off clear rules, give finance direct cube access from inside Excel, make their spreadsheets useful as live tools rather than dead extracts, and bring the slow pages back under control. No data-model rewrites, no platform upgrades, no big-bang launch.

Process

Three phases, overlapping at the edges. The clear utility came first — most painful ticket category. The Excel cube-view work ran alongside as the highest-leverage change for finance. Perf modifications came last, after enough instrumentation to know where the bottlenecks actually were rather than where they were rumoured to be.

Phase 1 — The generic clear utility

The first move was to enumerate every existing clear rule and ask what they had in common. The answer was: almost everything. Structurally, a clear rule is a set of dimension members defining an intersection plus a directive to wipe the data sitting there. The per-scenario rules were doing exactly that, with the intersection hard-coded into the rule body. There was no scenario-specific logic in 95% of them — just a different set of members.

The design fell out: take the intersection out of the rule body and turn it into parameters. One rule, configured at runtime, that accepts a member set across every dimension and clears whatever sits inside that selection.

The implementation had to handle a few real-world wrinkles:

1. Wildcards on dimensions the user doesn't care about. Most clear requests pin two or three dimensions tightly and leave the rest open. An unspecified dimension had to mean "all members" rather than fail for missing input.
2. Safety on the size of the cleared region. A wildcard on every dimension would wipe the entire cube. The utility estimated the size of the intersection and refused — or prompted — when it exceeded a configurable ceiling, with explicit override for the rare "yes, I really do mean this much" cases.
3. An audit trail. Every invocation logged the caller, intersection, row count, and result, replacing the implicit history that had lived in the folder of one-offs.
4. Compatibility with existing rules during migration. The utility had to coexist with the legacy rules until each was redirected.

The first cut ran inside the developer console only, against a non-production cube, and was validated by re-running every legacy clear scenario through the new utility and byte-comparing the resulting cube state. Once the diff was clean, the utility went to production and the dashboard surface was added.

Phase 2 — Cube-view connections inside the Excel add-in

The OneStream Excel add-in was already installed on every finance laptop, but it was being used primarily as a number-fetcher for static templates. The cube-view connection capability — the bit that lets a workbook hold a live, refreshable view of a cube slice — was either unknown or assumed to be a developer-only feature.

We changed both. Each canned cube view finance used inside the platform UI got an Excel-side equivalent: same dimension layout, same POV defaults, same filter behaviour, exposed as a refreshable connection inside a workbook. Analysts could open the workbook, change the POV from a familiar Excel control, hit refresh, and see live cube data without leaving the spreadsheet environment they already worked in.

The work was less about clever code and more about disciplined surface design. Each promoted view had to behave identically to its in-platform counterpart, refresh in a predictable window (anything slower got flagged for the phase-3 perf pass), carry POV defaults that matched its workflow, and surface errors in plain language rather than native stack traces. The promotion list ended up modest — about a dozen views — but covered the bulk of the day-to-day fetches.

Phase 3 — Macros for self-serve, plus the perf modifications

With live cube data reaching the spreadsheet, the question was what analysts wanted to do with it. The answer: the same things they'd been doing in their parallel-universe extracts, but with the data refreshing in place. Two sessions with senior analysts produced a small library of macros — variance breakouts against a comparator POV, top-N filters across a dimension, hierarchy roll-down for a chosen parent, copy-to-static-snapshot for deck handover. The macros lived inside the cube-view workbooks themselves, so opening the workbook gave you both the live data and the manipulation tools in one surface.

In parallel, the lag on the heavy review pages had become impossible to ignore — partly because the Excel cube views drew on the same underlying calculations as the dashboards. The instrumentation pass was straightforward: turn up calculation logging, run the offending pages against a representative POV, read the timing histogram.

Two findings dominated. First, a handful of dynamic calculations were being evaluated multiple times per render because the dashboard was triggering recomputation on POV-bound components even when the POV hadn't changed. A guard that only recomputed when the input POV mutated removed a chunk of per-render overhead. Second, the heaviest page built its filter list from a full dimension scan on every load; replacing the scan with a cached member list — refreshed when the dimension itself changed — cut first-paint substantially. Neither change was a rewrite; both were small, surgical edits scoped to the bottleneck the instrumentation surfaced.

Solution

Four components shipped, each small on its own and compounding with the others.

1. Generic clear utility

A single parameter-driven rule that accepts an intersection across every dimension and clears the data sitting there. Handles wildcards for unspecified dimensions, enforces a configurable ceiling on the cleared region with explicit override, and writes every invocation to an audit log keyed by caller, intersection, row count, and outcome.

2. Excel cube-view connection layer

Live, refreshable cube-view connections exposed inside the Excel add-in, mirroring the canned views finance already used in the platform UI. POV controls live in the spreadsheet; refresh is a single click; the surface is identical between in-platform and Excel views. Error states are wrapped in plain-language handlers that tell the analyst what to do rather than emit a platform stack trace.

3. Macro-driven self-serve spreadsheets

A small library of macros bundled into the cube-view workbooks that automate the common analyst manipulations — variance breakouts, top-N filters, hierarchy roll-downs, copy-to-static for deck handover. The library was scoped tight on purpose: each macro had to earn its place by replacing a real, recurring manual sequence.

4. Targeted code modifications for responsiveness

Two specific edits informed by calculation-log instrumentation: a guard against redundant recomputation on POV-bound dashboard components when the POV had not actually changed, and a cached member-list for the dimension scan that was rebuilding on every load of the heaviest review page. Both edits are small and local; both moved the user-visible latency from disruptive to negligible.

Results

| Metric | Before | After | Change | |---|---|---|---| | Clear-related developer tickets | Frequent (multiple per week) | Approximately zero | Eliminated as a category | | Analyst access pattern | Request through developer queue | Self-serve from Excel | Request → self-serve | | Excel-vs-cube data parity | Siloed extracts, weekly reconciliation | Live cube data inside Excel | Siloed → live | | Review page latency | Laggy enough to break analyst flow | Responsive on first paint | Laggy → responsive | | One-off clear rules in production | ~60 accumulated, mostly unowned | 1 generic utility | Folder retired |

Soft outcomes:

• The reconciliation half-day disappeared. Finance stopped maintaining the parallel-universe spreadsheets because cube data was now reaching their workbooks live. Month-end "cube number vs spreadsheet number" stopped being a recurring conversation.
• Developer time freed up. The clear-rule queue and the data-extract queue had been eating a meaningful chunk of the team's week. Both went away inside the first cycle after the tooling landed.
• Forecast review meetings stopped waiting on dashboards. Once the heaviest review pages painted quickly, meetings ran on their agenda rather than on the platform's render time.

Learnings

What worked. Building one parametric utility instead of 60 specific rules sounds obvious in retrospect, but the work to get there was about resisting the temptation to fix each ticket as it landed. The folder had grown one one-off at a time precisely because each request felt small. Stepping back and asking what the requests had in common — and the answer was almost everything — turned a recurring developer burden into a one-time piece of infrastructure. The same pattern carried into the Excel work: rather than treating every "can you export X" as a one-off, we promoted the underlying view to a self-serve surface and the request category went away.

What I'd do differently. Run the perf instrumentation earlier. The phase-3 code modifications benefited not just the dashboards but also the Excel cube-view refresh times, because the two share calculation paths. If the instrumentation had run in phase 1, the perf wins would have landed in the same cycle as the clear utility and the Excel rollout would have hit a faster baseline. Instrument early, even when perf isn't the primary deliverable — the same signal often informs the work you are doing.

Skill developed. Designing tools for the user one rung up from the developer. Most of what I'd built before this engagement was developer-facing — rules, frameworks, internal utilities. The Excel cube-view and macro work forced a different discipline: the user is an analyst, the surface has to feel native to them, the failure modes have to speak their language, and the success criterion is whether they can do tomorrow's work without filing a ticket. That framing — can this user do their next task without asking me — has become the bar I hold tooling work to since.