Forecasting · 2023

Quick Facts

• Industry: Mining
• Platform: OneStream
• Impact: A single report carrying full forecasting and annotations dropped from ~20 minutes to under a minute, and annotation entry moved to a one-click dashboard upload.

---

Overview

The client ran their forecasting and the business commentary behind it out of a single OneStream report — values and annotations together. It was correct, and it was painfully slow: roughly 20 minutes to render. Two different things were costing that time, and they needed two different fixes. An FDX query running in the background pre-identifies which members actually hold data so the report stops paying OneStream's default pull-everything-then-suppress tax, and the annotations — which were hammering the database on every render — get cached once and served from the globals object thereafter. Together they took the report under a minute. Alongside the render work, annotation entry was rebuilt on a custom, time-independent storage table fed by a single dashboard Excel upload.

The Problem

The report had to render a large forecasting grid and, in the same pass, the annotation attached to each member. Two costs compounded:

• No-data suppression was doing the most expensive thing possible. With ordinary OneStream suppression, the engine first pulls everything, then loops back over the whole set to drop the members that have no data, then renders what's left. On a wide forecasting cube that pull-then-prune cycle is most of the wall-clock time, and almost all of it is wasted on members that were never going to appear.
• Annotations round-tripped the database on every render. The annotations were rendered as dynamic calc members in the cell. Each one reached into the database, came back, and the next one did the same — over and over, for every annotated cell, every time the report ran. The database chatter, not the computation, was the bottleneck.

The brief: get the report fast enough to use live in a review meeting, without changing what it shows.

Process

Stop pulling members that have no data

Rather than lean on native suppression, I wrote an FDX query that runs in the background and returns exactly which members hold data for the report's point of view. The report then pulls only those members. The expensive part of suppression — pull the whole space, then iterate it again to discard the empties — never happens, because the empties were never requested in the first place. This is the change that took the biggest bite out of the 20 minutes.

Cache the annotations instead of re-fetching them

The annotations were the other half. Because each annotated cell was a dynamic calc member fetching from the database independently, the report was making the same kind of trip hundreds of times per run. The fix was to read the annotation table once on the first run and hold it in the globals object; every subsequent render reads from that in-memory copy instead of going back to the database. The repeated round-trips collapse into a single load.

Give the annotations a storage home that fits the report

OneStream's native annotation storage is not especially performant, and it's time-dependent — annotations are tied to a time period, which didn't match how this client needed to use them. So annotation storage was rebuilt on a custom ancillary table keyed by the full point-of-view of each member, deliberately time-independent. That table is what the report reads (and caches), and what the upload flow writes.

Make entering annotations a single action

Annotation entry was folded into a dynamic Excel upload: one workbook captures both the values and the annotations. On the OnePlace tab the user gets a single Import / Validate / Load dashboard step wired to run through the API behind one upload button — no separate import, validate, and load to run by hand. Upload, let it run, hit refresh, review. The client loved how little there was to it.

Solution

1. FDX-driven member targeting

A background FDX query resolves which members actually contain data for the requested POV; the report pulls only those, sidestepping the pull-everything-then-suppress cycle entirely.

2. Globals-cached annotations

The annotation table is loaded once and held in the globals object, so the per-cell dynamic calc members read from memory on every subsequent render instead of re-querying the database.

3. Custom time-independent annotation storage

A bespoke ancillary table keyed by each member's full point of view replaces OneStream's native annotation store, which was both slower and time-dependent. The report reads from it; the upload writes to it.

4. One-click dashboard upload

A dynamic Excel upload captures values and annotations in a single workbook, run through an API-driven Import / Validate / Load dashboard step behind one upload button.

Results

Soft outcomes:

• The report became usable live. A 20-minute render can't sit inside a review meeting; under a minute can.
• Values and annotations stopped being two jobs. One Excel, one upload, one refresh.

Learnings

What worked. Not trusting native suppression. The instinct is to let the platform suppress empties for you; the win was realising how it does that — pull everything, then prune — and pre-empting it with an FDX query that only asks for members that exist. Same theme on the annotations: the bottleneck wasn't compute, it was hundreds of identical database round-trips, and the globals object turns "fetch every time" into "fetch once."

What I'd do differently. Reach for the custom POV-keyed annotation table sooner. A good chunk of the early slowness traced back to native annotation storage being both slow and time-dependent; once it was clear the client needed time-independent annotations, the bespoke table was the obvious move and could have come first.

Skill developed. Reading OneStream report latency as I/O shape, not compute — where is the engine pulling more than it needs, and where is it making the same trip twice? FDX to bound the pull and globals to kill the repeat fetches are now the first two things I check on any slow report.