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 a 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 the 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.
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.
ake sure that you note the customization and add it back after the upgrade of the solution.
After doing this for some time now, I realize that you can use the EventHandler to do this instead of customizing the button.
Add a BeforeSelectionChangedEvent for CalculatePlan that pulls the required details for your calculation as shown below.
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.