OneStream BRGlobals and Specialty Optimization

If you are reading this, you either know all about BRGlobals, or you might have seen this in OneStream business rules.

I didn’t quite realize the use of BRGlobals (or how powerful it is or how to make use of it) till recently.

I’m currently on a project where Asset planning is done on OneStream’s specialty planning application, and they use many drivers at the Asset level.

The way I designed the system was to store Asset drivers (around 55 of them) in a separate relational table. When you run calculation from the Specialty planning, it’ll fetch the required row from the Driver table.

It works fine except for the performance. Imagine that you got 1000 Assets in the Specialty Planning application and only 500 drivers in the driver table.

When you execute the calculation for those 1000 assets, it is going to make 1000 calls to the SQL server. If you think that is not going to make a difference, then you are wrong. 🙁

Death by 1000 paper cuts it is.

I was thinking about making a single call to SQL server and get all the drivers that are needed for the calculation. With this information, I can then make calls to the cached table. Yes, I’m still making those calls. However, this time I’m avoiding the SQL server calls.

You might be wondering how much of a saving that could bring.

Here are the real-world timings

Calculation time with SQL calls – 1hour 9 minutes

Calculation time with cached datatable calls – 17 minutes

Yes, there is a huge difference.

How to implement

Here is where BRGlobals comes into play. If you look at BRGlobals, it got options to set the following at a global level.

• Boolean
• Decimal
• Guid
• Integer
• Object

I struggled with how to use BRGlobals to set a Datatable as global. I thought of playing with the BRGlobals.SetObject method.

Voila, and that is what I needed.

In Specialty apps, you trigger the calculation by clicking a button on a dashboard. This button calls a Dashboard extender rule and triggers the calc.

You can customize the button to call a custom dashboard extender that will generate a datatable. Yup, it is customizing the stock app. However, the benefits outweigh the customization.

Make sure that you note the customization and add it back after the upgrade of the solution.

Dashboard Extender Example

Dim dt As New DataTable()
' Get the required details from custom table
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
	Dim sql As New Text.StringBuilder
	
	sql.AppendLine("SELECT your columns")
	sql.AppendLine("From your table")
	sql.AppendLine("WHERE your filter = 'filtervalue'")
	
	dt = BRApi.Database.ExecuteSql(dbConn, sql.ToString,True)
End Using

globals.SetObject("NameOfObject", dt)

globals variable is nothing but BRGlobals.

Dashboard String Example

Now in the XFBR rule, which you are using to calculate the values, you can fetch the datatable from globals.

Dim dt As DataTable = globals.GetObject("onefromExtenderDT")
Dim yourCriteria As New Text.StringBuilder
yourCriteria.AppendLine("RegisterID=" & regID)
yourCriteria.AppendLine("AND Column1=" & col1Mbr)
Dim dr As DataRow() = dt.Select(yourCriteria.ToString)
' to get the value of a column
' you can perform the following
dim col1Val as Decimal = dr(0)("Column1")

You can query the datatable and get the required column value as above and use that in the calculations.

I hope it helps.