Lights-Out Trial Balance Load: seven regions, zero babysitting

Overview#

The month-end load that tells you what broke, not just that it broke.

Seven regional Oracle trial balances load into OneStream on a schedule, in order, every day. When one fails, the right people get an email that says exactly which member broke, why, and how to fix it, with the full list attached. Nobody has to open a log.

The client is a global oilfield services group. Trial balances come straight from Oracle through a direct connection into seven regional workflows, with the corporate workflow loaded last. During close, finance needs the current month and the next month refreshed several times a day. The users are regional controllers who own the workflows, the corporate consolidation team and the OneStream admins.

The Problem#

Four things made every refresh slower and riskier than it needed to be:

• Manual, click-heavy loads. Each workflow needs six steps: Import, Validate Transformation, Validate Intersections, Load Cube, Process Cube and Confirm. Seven workflows across two months is 84 manual steps per refresh.
• Failures were silent or cryptic. A failed load showed a red step and a generic message such as "Invalid member name." Someone had to notice, open the workflow and dig through stage data to find the bad member.
• The wrong people found out late. Regional owners did not know their load had failed until someone in corporate chased them.
• One failure stopped everything. A bad mapping in one region could block every other region's refresh.

What I Built#

Two pieces work together: an orchestrator that runs the loads, and an email module that reports on them.

The Orchestrator#

A OneStream Extender business rule runs every regional workflow in a fixed order, strictly one after another, with the corporate workflow last. For each workflow it drives all six steps and checks each step's status before moving on. If any other region fails, the rest still load. Only a user cancelling the run stops it.

Period Logic#

The run is anchored on the Global POV, which finance already moves forward each month. Parameters change what loads:

The Email Module#

Five email types, all sent through OneStream's mail connection:

Design decisions worth calling out:

• Month first, failure obvious. Subjects lead with the period in plain words and the OneStream code. In an inbox sorted by subject, a month's emails group together and failures stand out with no emoji or symbols.
• A coloured header band with a badge. Green for success; red with a white "LOAD FAILED" or "COMPLETED WITH ERRORS" badge for problems.
• Built for Outlook. Table-based HTML with inline styles and no embedded images, falling back to a text wordmark when no logo is set.
• Recipients are OneStream user names, not email addresses. The rule looks each person up in OneStream security for their current address, removes duplicates and sends one email per event even if someone is on several lists. Admins edit a single, clearly labelled block at the top of the rule.
• Email can never break the load. Every send is wrapped so a mail-server problem is logged and the load carries on.

The Emails#

These are the actual email templates, rendered with anonymised sample data. They are shown in the order a finance team meets them during a bad close night and then a good one.

Load failed at Validate Intersections#

The most useful email: a plain-English summary, a details table, every distinct problem grouped and counted, step-specific "What to do" guidance, a ledger of which loads are done, and the CSV attached. Subject line: September 2026 (2026M9) FAILED - [OneStream] Africa Oracle TB Load - Validate Intersections

Subject: September 2026 (2026M9) FAILED - [OneStream] Africa Oracle TB Load - Validate Intersections

Month completed with errors#

One summary email at the end of the run lists exactly which regions loaded and which failed, so nobody has to ask "did everything go through?"

Subject: October 2026 (2026M10) COMPLETED WITH ERRORS - [OneStream] Oracle TB Loads (5 of 7 loaded)

Workflow loaded#

The quiet good news, sent to a region's owners as soon as their workflow finishes for a month.

Subject: September 2026 (2026M9) - [OneStream] Europe West Oracle TB Loaded

Month completed#

When all seven regions load, one email says so: "7 of 7".

Subject: September 2026 (2026M9) COMPLETED - [OneStream] All Oracle TB Loads (7 of 7)

Load failed at Confirmation#

The same failure email at the last step, with guidance specific to confirming a workflow rather than to mapping.

Subject: October 2026 (2026M10) FAILED - [OneStream] Europe West Oracle TB Load - Confirmation

From "Invalid member name." to an answer#

OneStream's own validation message is often generic. The failure email replaces it with a sentence a controller can act on.

Every distinct problem is grouped and counted, most frequent first. One bad account mapping that hits 18 rows shows as one line with "18", not 18 lines.

How it works in plain words: the rule reads OneStream's stored validation-error records directly. For each failing row it checks which dimension member did not resolve to a real OneStream member. That pinpoints the broken dimension and the member even when OneStream's message does not name it, and it fills in the member names that OneStream's message template leaves blank.

What's in the Attachment#

The attached CSV changes with the step that failed, and holds every affected row (up to 5,000):

The file is written to the server's temporary folder only for the send and deleted straight after. A few rows of the Validate Intersections file, with illustrative data:

Scheduling#

Three OneStream Task Scheduler tasks share one Data Management sequence. Only their parameters differ:

The only monthly manual step is rolling the Global POV forward to the new close period. A short illustrated guide was written for the client's admins.

Under the Hood#

Keeps going when one load fails#

A failure is captured with the exact step and reason, the email goes out, and the run moves on to the next region. One bad mapping can no longer hold up six other refreshes.

An honest progress bar#

OneStream's progress call adds to a running total. The original bar sat at 99% for most of the run. Reading the API reference showed why, and the bar now advances an equal share per step across all 14 loads.

Stage-data forensics and defensive design#

Validation detail comes from the stage tables, not from the generic message. Email failures are logged and never thrown, attachments are always cleaned up, and the month parameters are checked against the Time dimension before anything runs.

Results#

Soft outcomes:

• Faster fixes. Owners often learn about a failure, and exactly what to correct, before anyone at corporate notices.
• The right people find out first. Failure emails go to the workflow's owners and admins at the moment it happens.
• Admin-friendly. Recipients, the cutoff day and the time zone are plain settings, not buried logic.

Learnings#

What worked. Treating the email as the product. Putting the exact member, the count and the fix in the first screen of the message is what turns a failure from a hunt into a task, and grouping identical issues kept it readable.

What I'd do differently. Prove it in the client's close earlier. The module is built and in final testing, so the real measure of the emails, whether owners act on them without a follow-up, is still to come. I would also agree the recipient lists with each region before building, not after.

Skill developed. Writing operational messages for the person who has to act on them. Every email states the month, the region, the step and the next move in plain words, and failures are designed to stand out in an inbox without relying on colour alone.