Issue With Referencing Global Variables in Connector Business Rules

[adykes]

Hi all, 

I am facing an issue regarding my ability to reference global objects between two different business rules, one of which is a finance business rule and the other of which is a connector business rule. To be more specific, the flow goes like this: 

1. First, I execute the finance business rule function that generates my global object, which is a DataTable, from a connector business rule: 

   1. BRApi.Finance.Calculate.ExecuteCustomCalculateBusinessRule(si, "Utilities", "GetDataTable", povInfo, CustomCalculateTimeType.MemberFilter)

2. Then, in the GetDataTable function of the Utilities finance business rule, I create a DataTable and save it as a global object:

   1. Dim dtCEDR_Integ As New DataTable("CEDR")
      'add data to data table
      globals.SetObject("dtCEDR_Integ", dtCEDR_Integ)

3. Then, back in the connector business rule, I try to get that global DataTable object.

   1. Dim dtCEDR_Integ As DataTable = globals.GetObject("dtCEDR_Integ")

However, when I attempt to import the data via the data source that is attached to the connector business rule, I get this error: "Unable to execute Business Rule 'BudFm_BudEx_Connector'. Object reference not set to an instance of an object."

Interestingly enough, the error doesn't happen when I get the global object (i.e. step #3 above) - it happens when I try to reference properties of the datatable, ex. 

dtCEDR_Integ.Rows.Count.ToString

This error confuses me greatly because I can do the exact same thing, except instead of using a connector business rule, I use an extender business rule, and everything works just fine. Is there something special about connector business rules that doesn't allow them to look at global variables the same way as other business rule types? Or am I just doing something wrong?

One additional piece of information that may be helpful to know is that the datatable that I am trying to generate does pull data from a cube that is separate from the cube that I'm importing into as specified in the data source. I don't think this would be causing issues but I want to add that in case that would in fact be an issue. 

Thanks in advance and I am looking forward to learning more about this confusing scenario. 

[DanielWillis]

---

@adykes wrote:

Interestingly enough, the error doesn't happen when I get the global object (i.e. step #3 above) - it happens when I try to reference properties of the datatable, ex.

---

I think getObject returns nothing rather than generating an error when a variable isn't set so that would be expected.

What I guess is happening is your Finance rule executes the connector rule, your connector rule creates the global var and populates it, your connector rule ends and destroys the global var, then your finance rule tries to access it and its not there. (Why that's different if you use extender rule rather than connector I'm not sure.

Could you try the following?

1. In your finance BR create the global variable
2. In your connector BR try and set the global variable which you created in your finance BR
3. See if you can now see the value of the global var

So hopefully the global var will exist until the finance BR completes and they both have access to it.

[adykes]

Hey Daniel, 

Thanks for your response. Actually, the role of the rules is the reverse of what you said - I'm creating and setting the global variable in the finance BR, and trying to retrieve it in the connector BR. I tried what you suggested by creating and setting the global variable initially in the connector BR, then overwriting it in the finance BR, then retrieving it in the connector BR. However, this did not work because I still received the same error as in my original post. Thank you anyways! I thought your solution would work.

[db_pdx]

Hi adykes: can I ask what the end goal is here?  I'm a bit concerned you're making it more complicated than it needs to be. We've never needed a global object to import data.

Is it to export data from CubeA and import it to CubeB? Consider the OOTB methods like a Data Source - Data Mgmt Export Sequence.  Or an FDX query.  If you don't need to transform the data and you don't need audit history, you can also do a simple api.Data.Calculate or a api.Data.GetBuffer/SetBuffer.

If you're querying an ancillary table to import, just put that query logic in the connector itself rather than adding the additional layer of the global object.

I might not be understanding the full requirements here, if you can share more details on the end goal we might be able to recommend a specific approach to take.

Cheers,  -db

[adykes]

Hey db, thanks for your response. Yes, the main goal here is to import data between two cubes. More specifically, I am trying to pull data from a datatable that is being generated by a function in the finance business rule. This function performs various logical checks on the source data in order to fill in additional columns in the datatable, so, while it might be possible to translate this code into another process, I'd rather not have to re-work all that code. Essentially, I need a way to grab that datatable and make it my source data in the connector BR. It's worth noting that the function that's generating the datatable is being called by a data adapter to bring that data into a BI Viewer component, so I tried executing the method command for the data adapter in the code as well: 

Using DbConnApp As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
	
	Dim methodTypeId As XFCommandMethodTypeId = XFCommandMethodType.BusinessRule.Id
	Dim methodQuery As String = "{USCG_RP_HelperQueries}{CostEstimateDetailReport}{WFTime=2026, WFScenario=RAP_FY26, WFCube=BudFm}"
	Dim resultDataTableName As String = "BudFmData"

	Dim dtDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(DbConnApp, methodTypeId, methodQuery, resultDataTableName, Nothing)

	BRApi.ErrorLog.LogMessage(si, "DataSet Info: " & dtDataSet.Tables.Count)
	
	Return Nothing 'Because I don't know how to convert a DataSet to a DataTable yet

End Using

But I am getting this error on line 9: Unable to execute Business Rule 'BudFm_BudEx_Connector'. Method type 'BusinessRule' requires an open application. I'm not sure if you've seen this error before, but is this approach also not possible?

[db_pdx · Accepted solution]

I haven't used the ExecuteMethodCommand, but it generally looks like it should work. It probably wants the Application Connection Info; BRApi.Database.CreateApplicationDbConnInfo(si).

Access the dataset: Dim dt as DataTable = dtDataSet.Tables(0)         (its index based)

Import it: api.Parser.ProcessDataTable(...)

[adykes]

Hey db, 

I just tried this, and it worked! Thank you so much... I think this is the approach I'm going to go with for the time being. As far as I understand, this approach skirts the need to have global objects because it queries the method command and therefore retrieves the datatable from the dashboard as a temporary object rather than trying to retrieve it in the code itself. Appreciate the help 😀

[JackLacava]

Globals are not really global, in the sense that they're not shared across the entire application - only between multiple runs of the same type of rule. So it might be working in your Dashboard Extender because you're using it across multiple Dashboard Extenders (or other Dashboard-related types of rule, it's a bit more vague there).

Correctly estimating the lifecycle of a global can be tricky. In practice, they are typically used in two situations:

• Transformation/Parser rules. Since they have to execute for hundreds or thousands of rows on each Import, it makes sense to cache as much as you can in global objects (typically setup actions or lookups).
• Calculation rules. Again, these can be run hundreds or thousands of times with each consolidation, so it makes sense to reuse as much as you can between runs.

Now, back to your problem. Custom Calculations are not meant to return values, they are meant to save values to the database; so if you want something back, ExecuteCustomCalculationBusinessRule is the wrong approach.

What you want to do is to have your method as a separate Function in the business rule:

Namespace OneStream.BusinessRule.Finance.MyBR
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
		   [...]
		End function

		' add another function to the class
		Public Function GetDataTable() as DataTable
		   ' do your thang here
		   [...]
		   return myShinyDataTable
		End Function
	End Class
End Namespace

Now you can execute it from elsewhere (note: you will have to add "BR\MyBR" in the Referenced Assemblies property of this other rule):

Dim myBr as new OneStream.BusinessRule.Finance.MyBR.MainClass
Dim myDt as DataTable = myBr.GetDataTable()

Note that this doesn't stop you from using the function in the CustomCalculate section of the same rule, if necessary:

Case = FinanceFunctionType.CustomCalculate
    Dim myDt = me.GetDataTable()

(You'll probably want your function to actually accept some parameters - particularly the SI object, which you will need pretty much all the time. Note that you don't have a valid api object in Dashboard contexts, so you can't use that.)

Last but not least: you probably don't want to put this sort of method, fetching random data, in Finance rules. It's literally the job for which Dashboard DataSet were invented. Among other benefits, you can execute DDS calls from Parameters or Data Adapter, which allow you to preview your dataset very easily as well as wiring it effortlessly to widgets. If you're not familiar with them, I strongly recommend to look them up (there is a decent intro on the blog, but look in the docs as well) and move your DT-fetching code there - again, you can still reference it from other rules, including Finance ones.

Hope this helps.

[adykes]

Hey Jack, thanks a ton for your response, it was very informative, especially regarding global variables and their shortcomings. Based on the responses I've received I now understand that using a global variable is not a good option for accomplishing what I'm trying to accomplish. 

I understand what you are saying about using a referenced assembly to call functions in the finance BR from the connector BR. I did in fact try this, but we are using the finance rules api in the function, so I am not able to pass in the correct api since connector BRs use a different type of api. Unless there's a way to use the finance rules api in the function without passing it in via the parameter, I don't think it will be possible to implement your suggestion. 

Finally, we are actually using the finance BR in conjunction with a data adapter - the data adapter calls a DDS BR that calls the finance BR function. Again, I believe this is because we need to use api functions in the generation of the datatable, which is not possible with the api object provided with a DDS BR (as far as I know). I mentioned in another reply my issues with trying to query the method command of the data adapter, but I believe that approach would be more closely aligned with what you are saying. 

Anyways, thanks for your response (and your helpful presence in the community). Final question - is "thang" official OneStream syntax? 😆

[JackLacava]

Yes, in a DDS you won't be able to use an api object of the same type as in a Finance rule (without contortions we don't want to go into).

I've read your other response, which mentions not having to rewrite the datatable-generating code, and I understand (although I'm pretty sure that, whatever it is that code does, it can be done without the Finance Api in one way or another. BRApi.Finance has pretty much everything you'd ever need...)

My suggestion then would be: in your Finance rule, you can dump your DataTable to an actual custom database table, which you can then read from elsewhere:

BRApi.Database.SaveCustomDataTable(si, dbLocation, tableName, dt, useBulkInsert)

You will have to manage the lifecycle yourself, though (checking if it exists already, and deciding when it's time to delete it or recreate it) - definitely not as quick as doing it in memory, but not too hard.

Worst of the worst: you can write it to a file, but it might be very slow (then again, it might be fast enough). 

@adykes wrote:

Final question - is "thang" official OneStream syntax? 😆

---

Ain't nuthin' but a Jack thang 😎 it's like this and like that and like this and uh...

[adykes]

Yes, I'm aware that I could probably re-write whatever's in that finance rule with BRApis instead of apis, but I will gladly take the more simple approach of creating a custom database table, if it's possible (unsure yet if the application security settings will allow it). Do you know what functions I can use to delete it? I checked the BRApi available functions and am only seeing functions for saving the datatable. 

Again, thank you so much for your guidance, and your lyrical genius 🎤