SQL: cross and delight of the IT revolution. If you've been in the game for more than a few years, chances are that you got your hands dirty with database queries once or thrice.
OneStream itself keeps data in a SQL database, and as much as we try to surface it through more human representations, sometimes the only way forward is to bust out a SELECT. The good news is that OneStream makes it pretty easy to do it; the bad news is that you're probably doing it wrong.
For simple scenarios, it's often enough to just create a Dashboard Data Adapter of type SQL, write your query, and connect it to grids or charts. Doesn't get easier than that!
You can even leverage adapters from a Business Rule, with a BRApi call:
' Whether the Adapter is in the Dashboard page under the System tab
Dim isSystemLevel As Boolean = False
' Post-7.3 reference to a Workspace
Dim workspaceID As Guid = BRApi.Dashboards.Workspaces.GetWorkspaceIDFromName(si, _
isSystemLevel, "MyWorkspace")
' Adapter Name
Dim adapterName As String = "MySQLAdapter"
' Result Table Name can be an empty string
Dim resultsTableName As String = "MyOutputTable"
' If your adapter uses Dashboard Parameters in the query,
' you can provide values for them in this object.
Dim customSubstVars As New Dictionary(Of String, String)
' customSubstVars.add("MyDashboardParameterName", "Some value")
Dim resultDataSet As DataSet = BRApi.Dashboards.Process.GetAdoDataSetForAdapter(si, _
isSystemLevel, workspaceID, adapterName, resultsTableName, customSubstVars)
' Tables are in a DataSet, which can be returned as it is.
' If you really want to return a single table, use .Copy:
' Return resultDataSet.Tables(0).Copy()
Return resultDataSet
However, there are situations in which you just don't want to keep a Data Adapter around;
and if you're only ever going to use it through rules, it's not the best-performing solution anyway, in terms of execution time.
For pure Rules, the best approach is to work directly with ADO objects. OneStream provides a number of utility methods to do it, on top of standard .Net functionality. We've seen some of them in a previous post, but for raw queries you might be familiar with this kind of approach:
This is faster, but it's an obsolete way to write SQL:
- What if your String variables include a single quote (') ? The query breaks.
- What if your String variables include characters that might have to be escaped in SQL? Like literal _, %, \, !, etc... The query breaks.
- What if you forget a single quote, in that jungle of '"& ... ? The query breaks.
It's 2023, we have databases on mobile phones; we can do better.
Query Parameters have been available in SQLServer for a very long time. OneStream allows you to leverage them very easily:
Dim scenario As String = "Actual"
Dim entity As String = "South Houston"
Using dbConn As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
Dim myQuery As New Text.StringBuilder()
' In our query we just define placeholders, starting with @;
' note how there are no quotes anywhere.
myQuery.Append("SELECT * FROM vStageSourceAndTargetDataWithAttributes")
myQuery.Append(" WHERE Sn = @scenarioParam AND EtT = @entityParam")
' Then we construct a list of objects that provide values for those placeholders ...
Dim parameters As New List(Of DbParamInfo)
parameters.Add(New DbParamInfo("scenarioParam", scenario))
parameters.Add(New DbParamInfo("entityParam", entity))
' ... and pass that list to ExecuteSQL.
Dim result As DataTable = BRApi.Database.ExecuteSql( _
dbconn, myQuery.ToString, parameters, False)
Return result
End Using
This code is slightly more verbose, but:
- there is no chance of forgetting a quote
- all escaping is taken care of by the system
- there is no chance that a malicious user will manage to pass malformed input ("SQL-injection" attacks)
- the raw query is arguably more readable
- number-conversion works regardless of user's Culture settings.
"Why would we worry about user's Culture?", some of you might object after reading that last item. The reality is that such settings will influence rule execution, as mentioned in a previous blog. Trying to concatenate numbers into a raw SQL query would likely, sooner or later, incur into issues with decimal and thousand separators during String conversions, which vary by Culture.
By using query parameters we don't need to convert numbers into Strings, so this query will always work properly regardless of Culture:
' use Decimals everywhere, it's what OneStream cells also contain in .CellAmount
Dim someAmount as Decimal = 123.456
Dim myQuery As New Text.StringBuilder()
myQuery.Append("SELECT * FROM vStageSourceAndTargetDataWithAttributes")
myQuery.Append(" WHERE RawAmount < @myThreshold")
Dim parameters As New List(Of DbParamInfo)
parameters.Add(New DbParamInfo("myThreshold", someAmount))
Dim result As DataTable = BRApi.Database.ExecuteSql(dbconn, myQuery.ToString, parameters, False)
Similar considerations can be made for dates and times.
BONUS! Query parameters allow SQLServer to detect similar queries much more easily, resulting in large speedups on repeated calls! Why not be a hero and rewrite some old, performance-critical queries with parameters, and boasvt about your benchmark with peers and bosses...? (You can send me 10% of your payrise - I take credit cards.)
So now you know how to run SQL queries with OneStream in a way that is safe, modern, and efficient. What are you going to build with it? Amaze us with your parameterized creations in the Forums, for a chance to get some swag!
9 Comments
