Are we there yet?

Yes. Yes we are. Here it is, after a somewhat incredible delay (Could I have written this in one post? Maybe. I did learn a lot along the way so probably not. If the question is: should I have written this in one post, the answer is almost certainly affirmative. Such is the way of the world.) is the culmination of using SQL to drive Finance Business Rules. It looks a little complex but really isn’t. Trust me if you dare.

Use case

If you remember back to the first installment of this series, the goal is to write data values to tuples/member scripts as defined in a SQL table. The target tuples are defined by the query, ignoring the executing Data Unit.

In the real world, the impetus for this approach would either be calculation requirements that cannot be readily derived from existing data or are cumbersome to code. The latter is my real-world case that drove this series as without a SQL-metadata technique, I’d have written thousands (yes, really) of lines of api.Data.Calculate statements, mimicking the technique used in Another Tool. I am far too lazy to do that and the chances of me getting that many lines of code right round down to zero. That complexity doesn’t consider code maintenance which would almost certainly end up having errors and be a nightmare to validate. Ugh.

Let SQL take the strain instead.

The code

Declaring variables

Yes, I usually define my variables before assigning them although many people prefer in-line declarations. It generally doesn’t have to be that way although I strongly suggest declaring variables outside of a loop unless there’s a variable scope problem. As an example, the below strSource could have been declared and assigned within the Data Table loop but that’s instantiating a variable n number of times where only assignment is required. Remember the hiker’s aphorism, “Ounces equal pounds and pounds equals pain.” There’s enough pain in my life and I’ll reckon in yours as well.

Running the query

The SQL statement executes a query against the table BLOGSIMPLESQL with a WHERE clause testing for an Entity in the POV. The Data Management step that runs this query uses E#Pennsylvania.

Here’s the contents of the table:

And here’s the query in OneStream:

Once executed, the Data Table dtSimple has two rows which can now be looped.

Let’s loop ‘em

Remember that Data Tables are enumerable class that can be looped using DataRow.

As the code loops through the records, it grabs the Target and Product field values and then assigns them to the strMemberScript variable. Notice the $ symbol in front of the leading double quote symbol and the {} curly braces: they allow interpolation to substitute values which is miles easier than throwing & concatenators into your code.

Note also the random number generator value that assigned to objMemberScriptValue.Amount. As always, I steal any and all ideas whenever and wherever I can (This blog is my attempt at paying back freely shared knowledge; sometimes what I write is even correct.) and I was just too lazy to do any real math.

Write it out

The SetDataCellUsingMemberScript method does not require that BoolValue test as shown below but without it one can easily have code failures and not realize it.

The proof of the pudding is in the eating

A blank canvas:

NB – The 303 is from previous blog posts – the goal here is to write to E#South_Carolina:U1#10_010 and E#Washington:U1#10_020.

Run the Data Management step:

Note that the Entity Filter is E#Pennsylvania which provides both the source value for the SQL statement as well as illustrating that this code is writing outside of the Data Unit.

The first run gives the randomly generated 28 and 22 the different Entities (and thus Data Units) E#South_Carolina and E#Washington:

And a second run shows 267 and 215 which are oddly coincident with my local area codes. Weird, but then much of what I do is.

And that really and truly is it.

What have we done?

This series has shown:

1. SQL tables can be queried within a Finance Business Rule.
2. The resulting Data Table can be looped in two different ways.
3. Data can be written outside of the calling Data Unit.
4. SQL tables can drive the tuples/member scripts in a MemberScriptAndValue object.
5. You, Gentle Reader, can write calculations that don’t depend on existing data, don’t live within the executing Data Unit, and don’t require eleventy billion lines of code.
6. And now you can go onto the next geeky-cool OneStream challenge.

Be seeing you.