I am Not a Data Unit, I Am a Free Geek

It is possible – barely so – that the maxim, “You can’t write outside of a Data Unit” isn’t quite as oppressive as the struggle Prisoner No. 6 faced in his quest for answers and freedom from his jailer’s shackles, but only by a hair’s breadth. Maybe. Probably.

[/vc_column_text][vc_empty_space height=”20px”][vc_video link=”https://www.youtube.com/watch?v=nW-bFGzNMXw”][vc_empty_space height=”20px”][/vc_column][/vc_row]When Yr. Obt. Svt. first worked with OneStream he was told just that: Finance Business Rules cannot write outside of the current Data Unit. I came from a technology where this was possible albeit a bit convoluted, so I was a bit bemused by that assertion. I was also frustrated as there are many use cases where writing outside of the current Data Unit is vital. Yes, it’s trivial to read a data value from another Data Unit and sometimes that suffices but definitely not always. I (rather foolishly) believed this rule but then I can be a bit gullible. In my folly, I euphemistically wrote non-optimal code, i.e., it was a bit of a dog’s breakfast but it worked, sort of. Don’t be That Geek.

Freedom Through MemberScriptAndValue

There is a way to write outside of the Data Unit but it is not well known. As of the writing of this post, Googling (and Duck Duck Going) for “MemberScriptAndValue” returns three hits, one of which is Part 1 of this series and the other two which do not actually contain “MemberScriptAndValue”. So yes, obscure, but a hidden treasure.

The Problem

I want to write to an intersection at Entity Pennsylvania and UD1 10_010 but am running it from a Data Management step that points to Entity South_Carolina. Good ol’ api.Data.Calculate pukes its guts out when pointed to Pennsylvania.

This:

Results in:

The same is alas true for api.Data.SetDataCell.

This:

Results in:

Bugger. Seemingly it’s true: writing outside of the Data Unit is not possible. Bugger again.

Freedom’s Four Parts

MemberScriptAndValue solves this problem in a (like That Other Technology I wrote about) slightly obscure way but once mastered is easy peasy no big deasy.

Part The First

Declare three variables of types MemberScriptAndVale, a List(of MemberScriptAndValues), and lastly a simple string to contain the memberscript/tuple.

objMemberScriptValue is where most of the magic happens. It will contain the memberscript/tuple as well as the data value. There is also a property called IsNoData which will be familiar to anyone who has used api.Data.SetDataCell.

Note that strMemberScript is fully qualified just as api.Data.SetDataCell must be. api.Data.Calculate doesn’t really require that as it can implicitly pick up the Data Unit dimensionality from the executing Data Unit although I prefer to explicitly assign the tuple.

Part The Second

Assign three member properties to objMemberScriptValue: Amount, IsNoData, and Script.

Part The Third

Add the objMemberScriptValue object to the List objMemberScriptValues:

Part The Fourth

The last step is to write the list to the cube. There are two ways to do this, one Very Bad But Simple and the other It Works But Is Ugly.

Really, Don’t Do This

The below code will write to the cube but will not throw errors. Bitter experience has shown that I am perfectly capable of passing an invalid memberscript/tuple to the SetDataCellsUsingMemberScript method and having it silently fail. I like to think of moments like that as learning experiences but really they’re just exercises in stupidity. Don’t be stupid.

Do This

Yes, a little verbose, but as the comments note, if SetDataCellsUsingMemberScript doesn’t work, OneStream will tell you that you’re writing to an invalid tuple.

Does It Work?

My code needs to write the number 303 to E#Pennsylvania:U1#10_010, replacing the existing 223.

Here’s the original state:

Running the Data Management step (and yes, it’s running from E#South_Carolina) results in:

Et voilà, OneStream has written outside of the Data Unit! That wasn’t so bad, was it?

Are there any restrictions? Well, yes, but it’s really just two and a minor ones at that: MemberScriptAndValue can only write to O#Forms. That’s where I always write calculated results (there can be issues with writing to O#Import when loading to the same Level 2 Data Units) in any case so I don’t see that being an issue. The other thing to consider is that the result is not calculated but instead stored as input data. Yes, really.

The Cell Status tells the tale:

On reflection, if a data value is written to O#Forms, it does sort of make sense that it’s Input, but that isn’t how api.Data.Calculate works. This difference matters because as non-calculated data, you cannot use api.Data.ClearCalculatedData to clear out the data and instead must either use a Data Buffer approach or manually clear out the data (yes, you can, but don’t unless you eat pain like candy) or revert to api.Data.SetDataCell. Both the Data Buffer and SetDataCell ironically require execution within the source Data Buffer although the former approach is very fast. However, you can use objMemberScriptValue.IsNoData = True to remove data outside of the Data Unit. Whew.

The Way of The World

It is possible to write outside of the executing Data Unit. Yes, more steps, but were I you, oh Gentle Reader, I’d put all of this into a function or better yet a class and vastly simplify the code process at least on reuse.

We are – really and truly – getting awfully close to the end of the series on SQL in OneStream Finance Business Rules. Fingers crossed, the next post in this exciting (ahem) series will show you just how to do that.

Be seeing you.