For those familiar with SQL – in particular, T-SQL, since OneStream’s back-end relies on a MSSQL database – you’ll be happy to hear that you can build an end-to-end SQL solution within OneStream. For context, I have automated many financial and operational processes using SQL and have ported them over into different platforms (Tableau, Power BI, etc.) – you can do the same within OneStream.
By the end of this series (not sure how many parts this will be, but expect multiple), I’ll take you through an end-to-end practical use-case of how and when to rely on a SQL solution inside of OneStream. It’s going to be very piece-meal, but going through the motions, everything should make sense if you’ve done anything similar.
Disclaimer: I am writing this for those who already have a proficiency in writing SQL, please consult the many learning resources online if you’re a SQL novice. Please do not blow up your OneStream application by running some SQL statement that returns an accidentally large data set. When in doubt, always include a TOP 10 declaration like so: SELECT TOP 10 * FROM TABLE to limit the number of rows returned.
Data Record Tables
Let’s first start with something simple: querying OneStream’s back-end data record tables.
OneStream’s back-end tables can be viewed here:
And the data record tables look like this:
The easiest way to see what’s inside of the tables is simply by clicking on the table in the list. You can also check them out in a dashboard data adapter component:
And when you test the adapter, you should see something like this:
Now you’re probably saying, “OK cool, now what?”
This table houses all YTD data (and in this case, for 2022) for every intersection in the application at both the base and parent level. Now, since every dimension member ID also exists in this table, we can leverage the Member table (which houses every member in the application for every configurable dimension)
Like so:
Which results in a data set that now has periodic values with account names and entities:
Now let’s chuck this into a BI Viewer:
Kind of neat, but it sucks to drag in every individual period.
So, let’s do this instead:
Wow, it just works:
And back into the BI Viewer:
Cool, right?
Now, if you’re Canadian (or you’re also a maple syrup, log-splitting, hockey enjoyer), you’ll notice that I have Canada in my data set which is the aggregated result of the provinces below it. If you’ve also not thought ahead and named your entities where it’s not clear which is a parent or base member (or you’re shackled to an entity structure you cannot change), then you’ll have to get creative in filtering that out in your SQL statement.
But you’d probably only have to get creative if you have a gazillion parents to consider – if not, just brute force it by excluding them with a where clause.
And there you have it, at the very least you now have a data set you can feed into dashboard components, but as one can imagine, pulling in other dimension members into your SQL statement is not a problem. Also, you’ll notice I made use of T-SQL’s unpivot functionality. Within OneStream, you can use any T-SQL feature that’s supported by the MSSQL version that your OneStream application is running on, as well as any other standard SQL language feature (group by, avg(), sum(), min(), max(), order by, having, sub-queries, etc.).
But wait, there’s more!
This exercise only scratches the surface of what you can do with SQL in OneStream. In the next part of this series, we’ll cover creating your own relational tables, loading them with data, and connecting them to OneStream cube data like what we retrieved above.
