The Riddle of the Docs

Gentle Reader, if you – as your author most definitely was when he first researched it – are somewhat puzzled when reading about Unbalanced Math, this is post is for you as it is a powerful method that illustrates OneStream’s flexibility and utility, prevents pretty dramatic error messages, and stops you from writing stupendously ill thought out nonoptimal solutions to simple problems. Seriously, if you write Finance Business Rules, you need to understand this.

Oh sure, I know all of this now, but as I first read through the documentation, I simply couldn’t figure it out. Why is it broken out separately from plain old api.Data.Calculate? “Unbalanced” is such an odd name. How do I use it? In short: what on earth are they going on about in the in the Design and Reference Guide and why should I care?

I’m sad to relate that as I read (and re-read) on, I became more confused. In particular, the references to Data Buffers just seemed…odd. I had to – as with practically everything else that involves OneStream and Yr. Obt. Svt.­ – figure this out by actually using it in a concrete functional use case of my own.

TL;DR, but you should

“Unbalanced” simply means that when a target member tuple in an api.Data.Calculate statement does not mirror the dimensions in the source member tuples, it is unbalanced in its dimensionality and when the tuples – which translate to Data Buffers – are unbalanced, a standard calculation will fail.

In Itsy Bitsy Words

A simple example is a centrally stored rate that is applied to multiple Entities and UD members. In pseudocode, it would look like:

Distribution = Sales * Distribution_Rate at No Geography at No Product

This looks simple enough. Sales is in multiple States (sorry, international readers) and Products. There is a single rate for the entire country by month. Multiplying Sales by that centrally stored expense rate for all States and Products should be a trifle.

Balanced Tuple Dimensions

A#Sales could be at O#Import and O#Forms, so O#BeforeAdj will be the Origin dimension member. These rate calculations only make sense at V#Periodic so that too will be explicitly set in the method. All calculations go to O#Forms. So far, so good.

First Pass, But Fails

That formula might look something like this:

Note that the U1#Total_Products.Base member filter will apply A#Distribution’s calculated results to every Product where A#Sales exists.

Also note that U1#No_Product is in A#Distribution_Rate’s tuple but not in the target A#Distribution’s tuple; this is no accident as the calculation must write to many Products using a rate stored at a single calculation-only driver Product.

Data Management

The super simple Data Management job cycles through just two States – enough for illustrative purposes and no more.

The data is equally simple as is the Excel proof of concept math:

Seriously, this is x = y * z. How hard could this be?

Harder Than Anticipated

No, OneStream doesn’t throw an error quite like that, but it’s almost as bad:

Ouch.

Despite the error message’s length, OneStream is pretty clear about the issue: A#Distribution_Rate has an explicit U1#No_Product member definition and A#Distribution does not; the member filter of U1#Total_Products.Base does not balance U1.

The error message states that either a specific target U1 member should be used or U1#All could apply the calculated results to, well, all U1 members.

Please Do Not Do This

U1#All will work in this very specific context:

Ta da:

Don’t, Just Don’t

There are warnings in the Design and Reference Guide to be very, very, very careful when using the All keyword because it can lead to data – perhaps quite a lot of data – being in places neither you nor anyone else might expect which is generally viewed as a Bad Thing. OneStream are not shy about pointing this out:

The author in your, um, author appreciates the “please do not do this” note which he suspects came from Product Support or Product Development or practically everyone who works for OneStream.

Having shown you the wrong way to do this, let’s try the right way: Unbalanced Math.

The Four Faces of Unbalanced Math

There are four unbalanced functions: AddUnbalanced, SubtractUnbalanced, DivideUnbalanced, and MultiplyUnbalanced, the last of which is the focus of this blog post. See the Design and Reference Guide for more detail on the first three.

I think of the functions as following (super roughly) this pattern:

x = y, z that is out of balance with x, the unbalanced bits of z that aren’t mentioned in x

In this use case, the x, y, and z as well as the missing bits must be surrounded by MultiplyUnbalanced and of course the whole thing is encapsulated within a api.Data.Calculate statement.

In All Its Glory

What does it take? Is it as complicated as I first thought?

Repeating U1#No_Product in that third parameter is all it takes. Easy-peasy.

NB — E#No_Geography isn’t out of balance because E#Pennsylvania and E#South_Carolina as defined in the Data Management Step are implicitly in the target Data Unit tuple.

I’ve modified A#Distribution_Rate to illustrate the impact:

That’s all there is to it. Also, this prevents OneStream’s documentation team (and the rest of that company) from a deep existential despair that ensues when #All is used. Win, win.

As Always, Easier Once Done

OneStream’s functionality can sometimes be difficult to suss out but with a bit of experimentation, it will give up its secrets. The reward is usually worth the struggle.

Rate calculations are common across all OneStream application. If you have not yet run across a requirement to perform Unbalanced Math, you will. It’s easy and powerful. Use it, and don’t use #All.

Be seeing you.