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:
For those of you who love code, here’s the CREATE TABLE script:
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:
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:
- 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.)
- Create a query as a string.
- Execute the query into a DataTable.
- Loop the results of the DataTable.
- 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.
Creating the query
As noted, I like to use the StringBuilder object as it’s easy to read but concatenated strings work as well.
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
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
Strangely, maddeningly, confusingly, DataTable object counts are zero-based but the Rows.Count property is one-based. Ugh.
Writing the results
The query results are now stored in another StringBuilder object, so write them to the Error Log:
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
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.
