Using SQL in OneStream Finance Business Rules, Part 1

Custom relational tables In Finance Business Rules

While the last thrilling (surely) post on SQL in OneStream focused on the core tables that store data, this post is about custom tables in Finance Business Rules. Custom tables can provide supplemental data that isn’t loaded into the Cube and isn’t in Stage or provide metadata (amongst other possibilities) for internal methods and functions. It’s relational, but not as we mostly know it.

This post’s use case for SQL in Finance Business Rules is to create succinct code that relates metadata based on business relationships that are not easily defined by Cube dimensions. Okay, I lied, sort of, because that’s the next post in this series. This is like dinner: one must eat one’s main course before enjoying desert.

If dimensional members are strings within a business rule – they are tuples/memberscripts when used in an api.Data.Calculate or in an api.Data.SetDataCell or a MemberScriptAndValue object – and SQL queries return strings within an object, then those members can be driven by SQL. Easy peasy, no big deasy.

NB – This post will only cover reading the table and proving that it can be written to the OneStream Error Log. The next will actually apply the table contents to OneStream calculations.

What is probably the world’s simplest table. Probably.

The table BlogSampleSQL should perhaps be called BogSampleSQL (I slay myself, and likely no one else) because it has but three fields: Source, Target, and Product.

Here it is in SQL Server Management Studio:

Graphical user interface, table Description automatically generated

For those of you who love code, here’s the CREATE TABLE script:

Text Description automatically generated

The Use Case

Easy peasy, redux: take data from E#Pennsylvania:U1#10_010 to E#South_Carolina_U1#10_010 and from E#Pennsylvania:U1#10_020 to E#Washington:U1#10_020. The target Entity/Product (U1 is Product) members are blank.

I could of course hard code everything and use an api.Data.SetDataCell method to explicitly write Pennsylvania’s 10_010 and 10_020 to South_Carolina and Washington respectively, but that is the very quintessence of hard coding. Hard Coding Is Bad.

Querying the BlogSimpleSQL

Before we go any further, a note about DataTables and StringBuilders

Data Tables are the object VB.Net (really all of the .Net languages) uses to interact with relational data. See this really quite good explanation of how to use them in abstract.

StringBuilders are an elegant way to simplify string appends. If you’ve used & concatenators in strings before, you’ll know how error prone and difficult to read they can be. From the very same source as the Data Tables, have a read of another really quite good explanation of the StringBuilder object.

The query itself

In SSMS, the query and result are as follows:

Text Description automatically generated with medium confidence

Remember how I wrote this was easy? Easy.

Performing the query in OneStream

To query and display the table’s content, the Business Rule must:

  1. Declare variables (I like to mostly do this at the top of a rule for comprehension, but many like it in-line. It’s just a style thing so do whatever you like best.)
  2. Create a query as a string.
  3. Execute the query into a DataTable.
  4. Loop the results of the DataTable.
  5. Write the results to the Error Log.

Declaring variables

Yeah, I’m going to contradict myself at the very tippy-top: note that the POV members are declared in-line. Think of it as a way to illustrate two approaches or think of it as hypocrisy or both.

Text Description automatically generated

Creating the query

As noted, I like to use the StringBuilder object as it’s easy to read but concatenated strings work as well.

Text Description automatically generated

Note that I am using the POV’s Entity value as fed from the Data Management step to avoid hardcoding the WHERE clause. UPPER is a good habit to acquire (as opposed to all of the bad habits that so many of us – especially Yr. Obt. Svt. – have).

Executing the query

A screenshot of a computer Description automatically generated with medium confidence

The Using statement declares dbConn and limits its scope when trapping errors. dbConn instantiates a database connection.

And BRApi.Database.ExecuteSql is where the magic happens. Note that the connection, the query, and timeout (this is the Large one) are defined here. The results go into the DataTable object dtSimple.

Looping the DataTable

Text Description automatically generated

Strangely, maddeningly, confusingly, DataTable object counts are zero-based but the Rows.Count property is one-based. Ugh.

Keynote Speaker and Author Nick Westergaard

Writing the results

The query results are now stored in another StringBuilder object, so write them to the Error Log:

Graphical user interface, text, application Description automatically generated

That’s it: we have created a table, queried its contents, and written the results to the Error Log. Easy peasy.

The code in all of its glory

A picture containing text Description automatically generated

I like to write comments and insert blank lines for readability, so this is shorter than it looks. Again, this is all a question of style, but for the love of all that is good, please use comments, even if it isn’t your style. The next OneStream practitioner will thank you.

That’s it and what’s coming

As I wrote in the very beginning of this post, the real use case is reading the table, replacing member names in tuples/memberscripts, and then writing data to places where no data exists.

It’s just (almost) as easy as reading the table and twice (almost) as exciting. It certainly will be useful.

Be seeing you.


Share This: