Community Manager
Community Manager

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!

Contributor III

Good topic, I don't see this done enough in the wild. I agree that its much more readable. I also feel a lot more comfortable when I'm implementing more secure code (re: sql injection) even in a low risk environment such as OS.

To go one step further on the readability front, I don't entirely understand why people use StringBuilder in these situations and I'd love to be shown the light if there is some. I only understand the benefits of StringBuilder when compared to making many concatenations to a string (e.g. via a loop).

I find this code much more readable. I also like that I can copy the SQL for testing without having to remove x.appends.= and quotes all over the place

' use Decimals everywhere, its what OneStream cells also contain in .CellAmount
Dim someAmount as Decimal = 123.456
Dim myQuery As String
myQuery = "SELECT * FROM vStageSourceAndTargetDataWithAttributes
           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, parameters, False)

Feel free to send swag 😁


Community Manager
Community Manager

StringBuilder is generally meant for any string concatenation, not just in a loop - because it doesn't create new objects in memory with each concatenation, it should reduce memory-waste. It also works well for situations where whitespace in the String is significant, but you don't want to break code indentation with a multiline raw String.

I agree that it's unnecessary in this post, but I included it to raise awareness. Good comment!

New Contributor III

Can we use the above demonstrated Parameters to execute a Stored Procedure that expects Parameters?

When I tried, I get an error message that "Procedure or function 'sp_BDGT_NEROffering' expects parameter '@BudgetDate', which was not supplied. I tried with null parameter and a date as well.

Using dbConn As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, "RevDBBudget")
   Dim params As New List(Of DbParamInfo)
   params.Add(New DbParamInfo("@BudgetDate",""))
   brapi.ErrorLog.LogMessage(si, "params:" & params.ToString)
End Using
Params that was captured from Error Log  is : Description: params:System.Collections.Generic.List`1[OneStream.Shared.Wcf.DbParamInfo]

Appreciate any comments or pointers to troubleshoot.




Contributor III

Concerning the last comment, I would use a different way to execute the SP :

Consider here that "p_airport_1", "p_airport_2" and "p_passengers" are string parameters.

Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)

   Dim SqlQuery As String = "EXEC SP_API_CLIMATIQ_TRAVEL_FLIGHT @p_airport_1=" _
     & p_airport_1 & ", @p_airport_2= " & p_airport_2 & ", @p_passengers= " & p_passengers

   Dim dt As DataTable = BRApi.Database.ExecuteSqlUsingReader(dbConn, sqlQuery, True)

End Using

 Then I will execute the SP, but also get the output of the SP in a datatable.

New Contributor III

That worked. Thanks very much!