One of these things is not like the other, one of these things does not belong

Dynamic formulas are, by their very name, not stored. So what, you might say (I fear that many say that when I write but such is life), dynamic members are for reporting, stored calculations whether in a Business Rule or a formula, are for allocations and other calculations that are, well, stored.

There are times, however, where the calculated results in a dynamic formula are needed in a stored calculation. Unfortunately, referring to a dynamic calculation within a Finance Business Rule (I believe this is true for the elventy million other kinds of rules as well, but I am of course always ready to be corrected), simply doesn’t work. Or does it?

Programmed for failure

Here’s a super simple formula. Try not to laugh.

In a prior life, I’d refer to a calculated value like this in a stored rule:

It sort of makes sense, right? Silly Example = Dynamically calculated member and make the result Durable Data. Alas, this is the result:

Is the syntax somehow wrong? Nope, because this:

Results in:

Bugger. The syntax isn’t wrong, but referring to a dynamic member doesn’t work. This was, until quite recently, the Way That It Works, or so I thought until I spoke with my colleague Jay Adler. Really, Jay, really? I’m wrong, again? Probably. Maybe.

Can it be done at all?

What Jay pointed out is that it is doable, so long as the member tuple is fully defined. Is it so? Partially.

This results in, as before, a successfully executed custom calculate Business Rule but does not return a result.

Hmm. Can I get that data point at all? Yes.

Yes!

Hah!

No!

I can get the dynamically calculated member value! So totes obvs, I can then assign that to my target account and all will be right in the world.

Oh, poo.

OneStream really, really, really doesn’t want you to do this.

Now what?

The DataBuffer to the rescue

I know that I can get the dynamic member’s value. That’s huge and I thank Jay. I then curse myself because I sort of forgot to ask Jay what comes next. This lack of follow through is endemic to Yr. Obt. Svt.’s so-called career. It was ever thus.

No matter, I stumbled upon the answer because that fairly horrific data error message suggests that all (pun sort of intended) I need to do is vary the dimensional members as both a target tuple as well as the intersection of the dynamically calculated member. NB – That last statement isn’t necessarily true – a calculation could go after a fixed dynamic member formula tuple but in my use case, I really do want the ratio of Margin and Sales for a given State and Product.

The answer then is to define a buffer that contains Sales (there’s no point in calculating this if Sales doesn’t exist), grab the relevant UD1 member (and Entity, Time, and Scenario as api.Data.GetDataCell().CellAmount requires those values), retrieve the results of the dynamic formula, and perform the assign. There’s also the matter of assigning the buffer cell to A#Stored_Example instead of A#Sales but that’s not the end of the world.

Defining the DataBuffer

This is largely straightforward. In future blog posts, I’ll endeavor to explain each component in greater detail. For now, this is the setup:

Note the use of interpolation to make simpler string replacements as well as defining the fixed tuple members in a variable to make things a bit easier to read.

Writing outside of the Data Buffer

The source DataBuffer goes after A#Sales but I need to write to A#Stored_Example. By creating two buffers and copying and then changing the target account in the target, I can write to a new target tuple.

Outside of the buffer loop (I have seen this done within the loop which works but is really inefficient as the result never changes), I need to grab the internal member id of A#Stored_Example.

Now loop the buffer:

I’m still using api.Data.GetDataCell().CellAmount to get the dynamically calculated value but am now passing the UD1 Product. Also, that dreaded constant error message is prevented because I am now writing the result to a specific intersection within the DataBuffer.

Within the DataBuffer loop, the code explicitly instantiates the target cellResult DataBufferCell to allow revalues, grabs the name of the source cell’s UD1 member, retrieves the dynamically calculated formula result into a Decimal variable, copies the source cell into the target cell, values the cell to the dynamic Decimal value, changes the that target cell’s AccountId to A#Stored_Example’s, and then writes the result cell to the target buffer.

That sounds complicated: look at the code and (really, just try it) say the above run-on sentence out loud. You’ll discover it’s easier to understand than you might think.

Lastly, the code commits the buffer to the cube:

Note the many commas and the Boolean at the end of the method: within the context of a planning application (which is all I really know, which is kind of sad, but which is also the Way of the World) having data that is cleared on Consolidation/Aggregation is kind of nuts unless you have an awfully good use case. Yes, you can set this at a global Scenario level but I am a belt-and-braces kind of guy and I like to know that my data isn’t going bye-bye when data gets added up.

Is that all? Yup.

That’s it with this example for now

Firstly, again, thanks to Jay, showing me again, if there be such need, that I must try harder to understand how OneStream Finance Business Rules work. This is obviously a silly use case and there are considerations about performance when it comes to pulling dynamic formula members potentially outside of the Data Buffer, but if this is what you need, the above is how you do it.

Future posts will try to explore a bit more about how Data Buffers work and specifically (this is the bit I find the most confusing) what’s possible and what is not when it comes to defining scope. Doing that in this post (I am at seven pages in Word already) is just too ambitious of a task.

Be seeing you.