Business Rules

The Smart Integration Connector Capabilities introduce additional business rule APIs (BR APIs) to allow for execution and management of remote business rules inside the context of the Smart Integration Connector gateway. These rules are transported using https to the Smart Integration Connector local gateway, compiled locally, executed, and the results returned to the caller for further processing. They provide a mechanism for complex drill backs, data processing scenarios or to invoke remote webAPIs hosted in your network.

NOTE: Gateways must have a local data source defined to invoke remote business rules.

There are two ways business rules can be used with the Smart Integration Connector Gateway:

OneStream BRAPIs interact with a specific local gateway and run on OneStream application servers.
Business rules that reference DLLs that are only accessible by the Local Gateway Server. These BRs are compiled and executed on the local gateway (Remote Business Rules when creating them in the Windows Desktop Client).

In these scenarios, the local gateway must have the allowRemoteCodeExec setting configured to True to enable remote execution.

The BR APIs are outlined below:

ExecRemoteGatewayRequest

ExecRemoteGatewayCachedBusinessRule

ExecRemoteGatewayJob

ExecRemoteGatewayBusinessRule

GetRemoteDataSourceConnection

GetRemoteGatewayJobStatus

GetSmartIntegrationConfigValue

GetGatewayConnectionInfo

Incompatible Business Rules

ExecRemoteGatewayRequest

Initiates a request to a local gateway as specified in the remote request object. This request is dispatched to the Smart Integration Connector local gateway connection data source with the specified command remote invoked.

NOTE: This method is used for request and response type interactions to a remote endpoint that runs for three or less minutes. The default execution timeout is 90 seconds and can be overridden by setting the CommandTimeout property on the RemoteRequestDTO instance provided.

Parameter details:

RemoteRequestDTO: Remote request object populated with the remote command and endpoint
Returns: RemoteRequestResultDto - Result of execution including the status and any exceptions which may have occurred on the remote endpoint

Following is an example connector business rule that would run on the OneStream application server sending a remote request and block of code to a Local Gateway Connection:

Copy

Dim objxfRemoteRequestResultDto As RemoteRequestResultDto
Dim objxfRemoteRequest As New RemoteCodeRequestDto
' Indication the desire is to run a remote block of code
objxfRemoteRequest.connectionType = RemoteCommandType.RemoteCodeExec
' Name of the remote host to pass to
objxfRemoteRequest.gatewayHostforRequest = "testconnection"
Dim strCode As String
strCode = "using System;...." ' Valid block of C# or VB.NET code
objxfRemoteRequest.LanguageType = RemoteCodeLanguageType.CSHARP
objxfRemoteRequest.remoteCodeBlock = strCode
objxfRemoteRequestResultDto=BRApi.Utilities.ExecRemoteGatewayRequest(objxfRemoteRequest)
Dim xfDT = New XFDataTable(si,objxfRemoteRequestResultDto.resultSet,Nothing,1000)

This BR API can also be used to invoke arbitrary SQL commands against a Smart Integration Connector local gateway connection data source at your site:

Copy

Dim drillBackInfo As New DrillBackResultInfo
Dim drillBackSQL As String = "SELECT CustName, InvDesc, BomCode, UnitPrice, Units, Amount, 'BomDetail' As DrillTypeCode FROM InvoiceMaterialDetail WHERE 
(InvYear = 2022) And (InvMonth = 'M3') And (PlantCode = 'H200') And (InvNo = 'I1-H200-AH2347-2022M3') 
And (ProdModel = 'P-Boy') And (DestinationCode = '1230') And (CustID = 'AH2347')ORDER BY BomCode"
Dim objxfRemoteRequestResultDto As RemoteRequestResultDto
Dim objxfRemoteRequest As New RemoteRequestDto
' Indicate this is a remote SQL command request
objxfRemoteRequest.connectionType = RemoteCommandType.SQLCommand
objxfRemoteRequest.GatewayRemoteDBConnection = "RevenueMgmt" ' Name of the connection defined in the remote endpoint
objxfRemoteRequest.gatewayHostforRequest = "testconnection"  ' Name of the remote host to pass to
objxfRemoteRequest.RemoteCommand = drillBackSQL
objxfRemoteRequestResultDto=BRApi.Utilities.ExecRemoteGatewayRequest(objxfRemoteRequest)
Evaulate the results to determine if it was successful
If (objxfRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Success) Then
   Dim xfDT = New XFDataTable(si,objxfRemoteRequestResultDto.ResultSet,Nothing,1000)
   drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.DataGrid
   drillBackInfo.DataTable = xfDT
   Return drillBackInfo
Else
   drillbackinfo.TextMessage = "Not Successful"
   drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.TextMessage
  Return drillBackInfo
End If

Remote function returning a datatable (VB.NET) without parameters:

Copy


                'Here we are telling it to specifically call a remote Smart Integration Function called 
'GetDataFromDB at SIC Gateway called TestConnection with a method called RunOperation
    Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "GetDataFromDB", Nothing, " TestConnection", "RunOperation")
        If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Success) Then
        If (objRemoteRequestResultDto.ResultType = RemoteResultType.DataTable) Then
        BRApi.ErrorLog.LogMessage(si, "Data Returned: " & objRemoteRequestResultDto.ResultSet.Rows.Count) 
    End If 
                           
    Else
       If (Not (objRemoteRequestResultDto.remoteException Is Nothing)) Then
       Throw ErrorHandler.LogWrite(si, New XFException(si, objRemoteRequestResultDto.remoteException))
     End If 
 End If

ExecRemoteGatewayCachedBusinessRule

When a cache flag and key is provided to the ExecRemoteGatewayBusinessRule BR API, this method is used to invoke a previously cached method. This is intended to be used for high-frequency remote business rules to avoid the performance impact of recompiling a remote method on each invocation.

NOTE: Requires allowRemoteCodeExec = True on Smart Integration Connector local gateway. If the previously cached method is not invoked after 60 minutes, the remote cached method is purged.

Parameter details:

si: SessionInfo object used to create connection objects
cachedFunctionKey: Key of previously cached remote function to invoke
functionArguments: Array of objects aligning to function / method parameters. Null / Nothing if there are none required
remoteHost: Name of remote host to invoke operation. (Smart Integration Connector Local Gateway Name)
executionTimeOut: Timeout (in seconds) on the remote job
Returns: RemoteRequestResultDto - Result of execution including the status and any exceptions which may have occurred on the remote endpoint

Here is the rule in C#:

Copy

 try
    {
    // Caches a SIC BR called GetDataFromDB on SIC Gateway called TestConnection 
    // and caches the function with the name GetDataFromDB with a cache key of GetDataFromDBCached
    RemoteRequestResultDto objRemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "GetDataFromDB", null, "TestConnection", "RunOperation", " GetDataFromDBCached ", false, 90);

 if (objRemoteRequestResultDto.RemoteResultStatus == RemoteMessageResultType.Success
     && objRemoteRequestResultDto.ResultSet != null
    && objRemoteRequestResultDto.ResultType == RemoteResultType.DataTable) 
    {
                                                            
BRApi.ErrorLog.LogMessage(si, "Data Returned - Rows:" + objRemoteRequestResultDto.ResultSet.Rows.Count);
    }
    else
    {
     if (objRemoteRequestResultDto.RemoteException != null) 
     {
    throw ErrorHandler.LogWrite(si, new XFException(si, objRemoteRequestResultDto.RemoteException));
    }
    else
    {
    BRApi.ErrorLog.LogMessage(si, "Remote Smart Integration Function Succeeded - no data/datatable returned");
    }
   }
                                                
    // Subsequent invocations of the remote BR can be run by specifying the endpoint and the cached key name
    RemoteRequestResultDto objRemoteRequestResultDtoCached = BRApi.Utilities.ExecRemoteGatewayCachedBusinessRule(si, " GetDataFromDBCached",null, " TestConnection ", 90);

    return null;
    }
    catch (Exception ex)
   {
   throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}

Here is the rule in VB.NET:

Copy

Dim argTest(0) As Object                                                             
argTest(0) = "2023"
                                                
    'Here we are telling it to specifically call a remote Smart Integration function called 
    'TestFileread at a remote gateway called TestConnection and caching the compiled 
    'result as a key called TestFileReadCache with a 90 second timeout
    Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "TestFileRead", argTest, " TestConnection ", "DeleteOldFileData","TestFileReadCache", false, 90)
    If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.RunOperationReturnObject) Then
                                                            
    'The delete method returns a true/false return type
    Dim result As Boolean
    ' ObjectResultValue introduced in v7.4 to simplify obtaining the return value from a method that doesn't return a 
    ' Dataset/Datatable
    result = objRemoteRequestResultDto.ObjectResultValue
                                                            
' Here we are invoking a compiled/cached method at a remote gateway called 
' TestConnection using the key of TestFilereadCache with a 90 second timeout.  
     Dim objRemoteRequestResultDtoCached As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayCachedBusinessRule(si, "TestFileReadCache", argTest, " TestConnection ", 90)                                                   
                                                            
BRApi.ErrorLog.LogMessage(si, "File Deleted: " & result) 
    Else
        If (Not (objRemoteRequestResultDto.remoteException Is Nothing)) Then
            Throw ErrorHandler.LogWrite(si, New XFException(si, objRemoteRequestResultDto.remoteException))
            End If 
    End If

ExecRemoteGatewayJob

There may be instances where a remote operation on the Smart Integration Connector Local Gateway host would need to process and assemble data that may take several minutes to run. In this situation, you could use this BR API to queue and run a remote business rule in an asynchronous manner where the remote Smart Integration Connector Local Gateway host returns a Job ID (GUID) that can later be used to obtain the job’s status or the results if the job is complete. When invoking this method, if the RemoteMessageResultStatus is returned as JobRunning (as shown in the example below), the RequestJobID is populated with the ID of the queued job that can later be used to obtain status.

NOTE: Requires allowRemoteCodeExec = True on Smart Integration Connector Local Gateway. There is a defined default limit of 30 minutes for remote jobs to execute before the job is cancelled, and an overloaded version of ExecremoteGatewayJob exists allowing the timeout to be provided, but can never exceed 4 hours. This is not configurable and if this timeout is reached, the status returned shows the timeout. If the result is not obtained within five minutes after the job completes (using the GetRemoteGatewayJobStatus BR API), the remote results are purged to ensure that result objects reclaim server memory on the Smart Integration Service host.

Here is a basic overview of invoking a remote job and displaying the returned remote Job ID in VB.NET.

NOTE: This is required to call back into GetRemoteJobStatus with the returned ID to obtain the result:

Copy

Dim drillBackInfo As New DrillBackResultInfo

Dim argInt As New XFSimpleObject(12)
argInt.Int32Value = 12
Dim argTest(1) As Object
argTest(0) = 100
argTest(1) = "test"

' Invoking a OneStream Business Rule as a remote job
Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayJob(si, "TestLongRunning", argTest, "ryantestconnection",String.Empty)
If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.JobRunning) Then
drillbackinfo.TextMessage = "Done "  & objRemoteRequestResultDto.RequestJobID.ToString()
drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.TextMessage
Return drillBackInfo
End If

Here is the rule in VB.NET to invoke a job, obtain the job ID, and 'poll' until completion:

Copy

Try
Dim jobID As Guid
‘ Invoking a long-running Job with a Smart Integration Function called GetDataFromDB on SIC Gateway called testConneciton
    Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayJob(si, "GetDataFromDB", Nothing, "TestConnection",String.Empty)
                                    
    ' If Successful, the status is retuned indicating the job is running with the job ID – Use this ID to interrogate if the job is compleed. 
           If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.JobRunning) Then
         jobID = objRemoteRequestResultDto.RequestJobID
         BRApi.ErrorLog.LogMessage(si, "Remote Job Queued and Running - JobID: " & jobID.ToString())
                                                
     ' Example waiting 20 seconds for job to complete
     For loopControl = 0 To 10
     System.Threading.Thread.Sleep(2000)
     Dim objJobStatus As RemoteJobStatusResultDto = BRApi.Utilities.GetRemoteGatewayJobStatus(si, JobID, " TestConnection ")
                                                                                    
If (objJobStatus.RemoteJobState = RemoteJobState.Running)
    BRApi.ErrorLog.LogMessage(si, "Remote Job Still running - JobID: " & jobID.ToString())                                                            
    Else If (objJobStatus.RemoteJobState = RemoteJobState.Completed)
                                                                        
   ' Checking the return type from the remote job
    If (Not objJobStatus.RemoteJobResult.ResultSet Is Nothing) Then
    Dim xfDT = New XFDataTable(si,objJobStatus.RemoteJobResult.ResultSet,Nothing,1000)                                    
    BRApi.ErrorLog.LogMessage(si, "Remote Job Completed - Datatable Returned - JobID: " & jobID.ToString())
        Return Nothing
            Else If (Not objJobStatus.RemoteJobResult.ResultDataSet Is Nothing) Then
            Dim xfDT = New XFDataTable(si,objJobStatus.RemoteJobResult.ResultDataSet.Tables(0),Nothing,1000)                                    
               BRApi.ErrorLog.LogMessage(si, "Remote Job Completed - Dataset Returned - JobID: " & jobID.ToString())
                Return Nothing                                                                                   
                       Else If (Not objJobStatus.RemoteJobResult.ObjectResultValue Is Nothing) Then                                      
                     BRApi.ErrorLog.LogMessage(si, "Remote Job Completed - Object Returned - JobID: " & jobID.ToString())
                          Return Nothing
          End If 
      Else If (objJobStatus.RemoteJobState = RemoteJobState.JobNotFound)
    BRApi.ErrorLog.LogMessage(si, "Remote Job Not Found - JobID: " & jobID.ToString())
    Return Nothing
       Else If (objJobStatus.RemoteJobState = RemoteJobState.RequestTimeOut)
         BRApi.ErrorLog.LogMessage(si, "Remote Job Timed Out - JobID: " & jobID.ToString())
      Return Nothing
      Else If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Exception)
      BRApi.ErrorLog.LogMessage(si, "Exception During Exeuction of Job: " & objRemoteRequestResultDto.RemoteException.ToString())
       End If
              Next 
                                                                                                  
       Else
        ' Exception occurred immediately during compile/initial run
          If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Exception)
              BRApi.ErrorLog.LogMessage(si, "Exception Executing Job: " & objRemoteRequestResultDto.RemoteException.ToString())
           Else
             BRApi.ErrorLog.LogMessage(si, "General Job Execution Error - State: " & objRemoteRequestResultDto.RemoteResultStatus.ToString())   
                  End If 
                    End If 

            Return Nothing
        Catch ex As Exception
    Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try

Here is the rule in C# to invoke a job, obtain the job ID, and 'poll' until completion:

Copy

try
{
    Guid jobID;
    RemoteRequestResultDto objRemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayJob(si, "GetDataFromDB", null, " TestConnection ",string.Empty);
    if (objRemoteRequestResultDto.RemoteResultStatus == RemoteMessageResultType.JobRunning) 
     {
        jobID = objRemoteRequestResultDto.RequestJobID;
        BRApi.ErrorLog.LogMessage(si, "Remote Job Queued and Running - JobID: " + jobID.ToString());
          
        // Example waiting 20 seconds for job to complete
        for (int loopControl=0; loopControl<=10; loopControl++)
            {
            System.Threading.Thread.Sleep(2000);
            RemoteJobStatusResultDto objJobStatus = BRApi.Utilities.GetRemoteGatewayJobStatus(si, jobID, " TestConnection");
                                                                                    
             if (objJobStatus.RemoteJobState == RemoteJobState.Running)
             {
             BRApi.ErrorLog.LogMessage(si, "Remote Job Still running - JobID: " + jobID.ToString());                                                
             }
             else if (objJobStatus.RemoteJobState == RemoteJobState.Completed)
             {
            // Checking the return type from the remote job
            if (objJobStatus.RemoteJobResult.ResultSet != null) 
                                    {
            XFDataTable xfDT = new XFDataTable(si,objJobStatus.RemoteJobResult.ResultSet,null,1000);
            BRApi.ErrorLog.LogMessage(si, "Remote Job Completed - Datatable Returned - JobID: " + jobID.ToString());
                return null;
                }
                    else if (objJobStatus.RemoteJobResult.ResultDataSet != null) 
                    {
                    XFDataTable xfDT = new XFDataTable(si,objJobStatus.RemoteJobResult.ResultDataSet.Tables[0],null,1000);
                    BRApi.ErrorLog.LogMessage(si, "Remote Job Completed - Dataset Returned - JobID: " + jobID.ToString());
                        return null;                             
                        }
                        else if (objJobStatus.RemoteJobResult.ObjectResultValue !=null) 
                          {
                        BRApi.ErrorLog.LogMessage(si, "Remote Job Completed - Object Returned - JobID: " + jobID.ToString());
                          return null;
                        }
                           else if (objJobStatus.RemoteJobState == RemoteJobState.JobNotFound)
                        {
                        BRApi.ErrorLog.LogMessage(si, "Remote Job Not Found - JobID: " + jobID.ToString());
                          return null;
                 }
                 else if (objJobStatus.RemoteJobState == RemoteJobState.RequestTimeOut)
            {
            BRApi.ErrorLog.LogMessage(si, "Remote Job Timed Out - JobID: " + jobID.ToString());
              return null;
            }
            else if (objRemoteRequestResultDto.RemoteResultStatus == RemoteMessageResultType.Exception)
            {
            BRApi.ErrorLog.LogMessage(si, "Exception During Exeuction of Job: "+ objRemoteRequestResultDto.RemoteException.ToString());
            return null;
            }
        } 
}  
 // End for loop
    }
        else
            {
            // Exception occurring immediately during compile/initial run
            if (objRemoteRequestResultDto.RemoteResultStatus == RemoteMessageResultType.Exception)
            BRApi.ErrorLog.LogMessage(si, "Exception Executing Job: " + objRemoteRequestResultDto.RemoteException.ToString());
           else
           BRApi.ErrorLog.LogMessage(si, "General Job Execution Error - State: " + objRemoteRequestResultDto.RemoteResultStatus.ToString());
                                                   
           return null;
            }  
            }
        catch (Exception ex)
        {
    throw ErrorHandler.LogWrite(si, new XFException(si, ex));
    }                                   
return null;

ExecRemoteGatewayBusinessRule

This is a core BR API that can be used to remotely invoke Smart Integration functions on a specified remote Smart Integration Connector Local Gateway host. The Smart Integration Connector Local Gateway must have allowRemoteCodeExec set to True for this BR API to invoke an operation successfully, otherwise the Smart Integration Connector Local Gateway host returns a result indicating that remote code execution is disabled.

This method takes a previously authored Smart Integration function, written in VB.NET or C#, in the OneStream application and passes it to the remote host for execution. With this BR API, it is expected that remote calls should take no more than 2-3 minutes to return a result to the caller as this BR API will block until a result is returned. If longer running or sync operations are needed, consider using the execRemoteGatewayJob BR API.

NOTE: Requires allowRemoteCodeExec = True on Smart Integration Service

Parameter details:

si: SessionInfo object used to create connection objects
brName: Name of the locally defined (within the OneStream Application scope) Smart Integration function
functionArguments: Array of objects aligning to function / method parameters. Null / Nothing if there are none required.
remoteHost: Name of remote host to invoke operation. (Smart Integration Connector name)
functionName: Name of the function in the Smart Integration function to invoke. If null or empty, a function/method with the name RunOperation is expected to exist within the authored code.
(Optional) cachedFunctionKey: Name used to cache the remote function to avoid recompiling the function on a subsequent call. This is optional and if missing or null the function will not be cached.
(Optional) forceCacheUpdate: Option indicating if a previously cached function should be replaced with this version. When true, and an existing function is found with a name specified in the cachedFunctionKey parameter, the BR is recompiled and recached. This is useful for situations where a remote function is cached and a change was made.
executionTimeOut: Timeout (in seconds) on the remote job (In 7.4, this is now an optional parameter and defaults to 90 seconds if the parameter is missing.)

Here is a C# drill-back example:

Copy

DrillBackResultInfo drillBackInfo = new DrillBackResultInfo();
DataTable dtf = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "SAP_Test", null, "gateway-SAP_Europe",string.Empty).ResultSet;
var xfDT = new XFDataTable(si, dtf, null, 1000);
drillBackInfo.DataTable = xfDT;
drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.DataGrid; return drillBackInfo;

Here is a VB.NETdrill-back example that invokes a remote business rule accepting 2 parameters:

Copy

Dim drillBackInfo As New DrillBackResultInfo 
Dim argTest(1) As Object   ' Creating an object array to package the method parameters
argTest(0) = 12            ' First parameter is an integer
argTest(1) = "test"        ' Second parameter is a string

'Remote Smart Integration Function Signature: ' Public Shared Function  RunOperation2(testval As Integer, teststr As String) As ArrayList 

'Invoking method RunOperation2 on endpoint testConnection passing in user defined parameters as an array 

Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule
(si, "TestValueTypeParam", argTest, "testConnection","RunOperation2") 

If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.RunOperationReturnObject) Then 
  Dim returnVal As System.Collections.ArrayList returnVal = objRemoteRequestResultDto.ObjectResultValue 

 'Simple demonstration without error checking to look at the first element of the    arraylist drillbackinfo.TextMessage = 
 "Completed! " & returnVal(0).ToString()  drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.TextMessage 
 Return drillBackInfo 
Else If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Success) 
' Demonstrating a 'pattern' whereby the caller can verify what the type is that's returned and handle properly. 
Dim xfDT = New XFDataTable(si,objRemoteRequestResultDto.resultSet,Nothing,1000) drillBackInfo.DataTable = xfDT 
drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.DataGrid 
Return drillBackInfo 
Else If (Not (objRemoteRequestResultDto.remoteException Is Nothing)) Then 
Throw ErrorHandler.LogWrite(si, New XFException(si,objRemoteRequestResultDto.remoteException)) 
End If 
Return Nothing 
End If

Below is a TestFileRead Remote Business Rule function in C# Referenced by Examples Below:

Copy

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Data.SqlClient;
using OneStreamGatewayService;

namespace OneStream.BusinessRule.SmartIntegrationFunction.GetDataFromDB
{
    public class MainClass
    {
        public DataTable RunOperation() 
        {
            DataTable dataTableResults = new DataTable();
            string connectionString, sql;
                           
            // The API Below is only available in 7.4 and allows the ability to
            // Obtain a remotely defined connection string.
            connectionString = APILibrary.GetRemoteDataSourceConnection("RevenueMgmt");
                           
            SqlConnection conn;
            conn = new SqlConnection(connectionStringconn.Open();
            sql = "Select * FROM InvoiceMaterialDetail";
            SqlCommand cmd = new SqlCommand(sql, conn);
            var dbreader = cmd.ExecuteReader();
            dataTableResults.Load(dbreader);
            return dataTableResults;
        }
    }
}

Remote function returning a scalar value (VB.NET) or object with parameters:

Copy

Dim argTest(0) As Object                                                             
argTest(0) = "2023"
                                          
   'Here we are telling it to specifically call a remote Smart Integration Function called TestFileRead at SIC Gateway 
   'called TestConnection with a method called DeleteOldFileData
   Dim objRemoteRequestResultDto As 
   RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "TestFileRead", argTest, "TestConnection", "DeleteOldFileData")
   If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.RunOperationReturnObject) Then
   'The delete method returns a true/false return type
   
           Dim result As Boolean
           'ObjectResultValue introduced in v7.4 to simplify obtaining the return 
           'value from a method that doesn't return a Dataset/Datatable
        result = objRemoteRequestResultDto.ObjectResultValue
                                                      
          'Previous to v7.4, The result was returned in a compressed format and it is required that 
          'InfateJsonObject was invoked
          'result = CompressionHelper.InflateJsonObject(Of Object)
          '(si,objRemoteRequestResultDto.resultDataCompressed)
                                                      
         BRApi.ErrorLog.LogMessage(si, "File Deleted: " & result) 
    Else
        If (Not (objRemoteRequestResultDto.remoteException Is Nothing)) Then
            Throw ErrorHandler.LogWrite(si, New XFException
            (si, objRemoteRequestResultDto.remoteException))
        End If
    End if

Remote function returning a scalar type/object (C#):

Copy

try
{
    object[] argTest = new object[1]; 
       argTest[0] = "2023";
     
       'Here we are telling it to specifically call a remote Smart Integration Function called TestFileRead at SIC Gateway 
       'called TestConnection with a method called DeleteOldFileData

       RemoteRequestResultDto objRemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "TestFileRead", argTest, " TestConnection", "DeleteOldFileData");

       if (objRemoteRequestResultDto.RemoteResultStatus == RemoteMessageResultType.RunOperationReturnObject
    && objRemoteRequestResultDto.ObjectResultValue != null) 
   
    bool result;
    if (bool.TryParse(objRemoteRequestResultDto.ObjectResultValue.ToString(), out result))
        
         BRApi.ErrorLog.LogMessage(si, "File Deleted: " + result.ToString());          
           else
           BRApi.ErrorLog.LogMessage(si, "Returned a non-boolean value");      
     else
         if (objRemoteRequestResultDto.RemoteException != null)   
        throw ErrorHandler.LogWrite(si, new XFException(si, objRemoteRequestResultDto.RemoteException));
                                           
    return null;
    catch (Exception ex)
        throw ErrorHandler.LogWrite(si, new XFException(si, ex))
     End Try

Remote function returning a datatable (C#):

Copy

try
    {
     // Here we are telling it to specifically call a remote Smart Integration Function called GetDataFromDB at SIC Gateway called TestConnection with a method called RunOperation
    RemoteRequestResultDto objRemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "GetDataFromDB", null, "TestConnection", "RunOperation");

    if (objRemoteRequestResultDto.RemoteResultStatus == RemoteMessageResultType.Success
        && objRemoteRequestResultDto.ResultSet != null
        && objRemoteRequestResultDto.ResultType == RemoteResultType.DataTable) 
        {
                                                            
    BRApi.ErrorLog.LogMessage(si, "Data Returned - Rows:" + objRemoteRequestResultDto.ResultSet.Rows.Count);
    }
        else
        {
            if (objRemoteRequestResultDto.RemoteException != null) 
        {
            throw ErrorHandler.LogWrite(si, new XFException(si, objRemoteRequestResultDto.RemoteException));
        }
            else
        {
             BRApi.ErrorLog.LogMessage(si, "Remote Smart Integration Function Succeeded - no data/datatable returned");
        }
      }
                                              
    return null;
   }
  catch (Exception ex)
  {
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}

GetRemoteDataSourceConnection

This remote business rule will return the connection string associated with a Local Gateway Configuration Data Source.

NOTE: Requires allowRemoteCodeExec = True on Smart Integration Local Gateway.

Parameter details:

Data Source: The name of the Local Gateway Configuration Data Source.

Here is the rule in VB.NET :

Copy

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Namespace OneStream.BusinessRule.SmartIntegrationFunction.GetRemoteDataSource_VB
    Public Class MainClass
        Public Shared Function RunOperation() As DataTable
            Dim dataTableResults As New DataTable
            
            ' Get the remotely defined connection String
            Dim connectionString As String  = OneStreamGatewayService.APILibrary.GetRemoteDataSourceConnection("Sales_Data1");
            
            Dim conn As SqlConnection = New SqlConnection(connectionString)
            ' Insert custom code
                        
            Return dataTableResults
        End Function
    End Class
End Namespace

Copy

using System; 
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Data.SqlClient;


namespace OneStream.BusinessRule.SmartIntegrationFunction.GetRemoteDataSourceSample
{
      public class MainClass
      {
            public DataTable RunOperation() 
            {
                  DataTable dataTableResults = new DataTable();
                  
                  // Get the remotely defined connection string
                  string connectionString = OneStreamGatewayService.APILibrary.GetRemoteDataSourceConnection("Sales_Data1");
                  
                  SqlConnection conn = new SqlConnection(connectionString);
                  // Insert custom code
                                    
                  return dataTableResults;
            }
      }
}

GetRemoteGatewayJobStatus

This BR API returns the status or the results of a previously remotely queued job invoked against a specified Smart Integration Connector Local Gateway host.

NOTE: Requires allowRemoteCodeExec = true on Smart Integration Service.

Parameter details:

si: SessionInfo object used to create connection objects
JobID: GUID of remote job ID returned upon successful call to ExecRemoteGatewayJob
remoteHost: Name of remote host to invoke operation (Smart Integration Connector Name)

The sample below invokes a job as part of a data management job inside a OneStream extenderrule. The example demonstrates a simple Smart Integration Function that sleeps 2 seconds 1000 times in a loop simulating a long running task. The corresponding extender rule illustrates how this long running function can be invoked as a job, returning a job ID and subsequently polled until it's completed.

Here is the 'long running' Smart Integration Function in VB.NET:

Copy

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Threading

Namespace OneStream.BusinessRule.SmartIntegrationFunction.LongRunningTest
    Public Class MainClass
        Public Shared Function RunOperation() As DataTable
            For i As Integer = 1 To 1000
                thread.Sleep (2000)
                Next
        
        Dim result As String
        result = OneStreamGatewayService.APILibrary.GetSmartIntegrationConfigValue("test")

        Dim Table1 As DataTable
        Table1 = New DataTable("TableName")

        Dim column1 As DataColumn = New DataColumn("SettingName")
        column1.DataType = System.Type.GetType("System.String")

        Table1.Columns.Add(column1)

        Table1.Rows.Add(result)
    Return Table1        

        End Function
    End Class
End Namespace

It would be typical to invoke long running jobs as part of a Data management/Extender Rule and the code below is an example on how this could be accomplished inVB.NET:

Copy

Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object 
Try 
Dim jobID As Guid 
Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayJob
(si, "LongRunningTest", Nothing, "testConnection",String.Empty) 

If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.JobRunning) Then 
jobID = objRemoteRequestResultDto.RequestJobID BRApi.ErrorLog.LogMessage
(si, "Remote Job Queued and Running - JobID: " & jobID.ToString()) 
'Example waiting 20 seconds for job to complete 
For loopControl = 0 To 10 
  System.Threading.Thread.Sleep(2000) 

Dim objJobStatus As RemoteJobStatusResultDto = BRApi.Utilities.GetRemoteGatewayJobStatus(si, JobID, "testconnection2") 
If (objJobStatus.RemoteJobState = RemoteJobState.Running) 
BRApi.ErrorLog.LogMessage(si, "Remote Job Still running - JobID: " & jobID.ToString()) 
Else If (objJobStatus.RemoteJobState = RemoteJobState.Completed) 
' Checking the return type from the remote job 
If (Not objJobStatus.RemoteJobResult.ResultSet Is Nothing) Then 
Dim xfDT = New XFDataTable(si,objJobStatus.RemoteJobResult.ResultSet,Nothing,1000) 
BRApi.ErrorLog.LogMessage(si, "Remote Job Completed - Datatable Returned - JobID: " & jobID.ToString()) 
Return Nothing 
Else If (Not objJobStatus.RemoteJobResult.ResultDataSet Is Nothing) 
Then Dim xfDT = New XFDataTable(si,objJobStatus.RemoteJobResult.ResultDataSet.Tables(0),Nothing,1000) 
BRApi.ErrorLog.LogMessage(si, "Remote Job Completed - Dataset Returned - JobID: " & jobID.ToString()) Return Nothing 
Else If (Not objJobStatus.RemoteJobResult.ObjectResultValue Is Nothing) Then BRApi.ErrorLog.LogMessage
(si, "Remote Job Completed - Object Returned - JobID: " & jobID.ToString()) 
Return Nothing 
End If 
Else If (objJobStatus.RemoteJobState = RemoteJobState.JobNotFound) BRApi.ErrorLog.LogMessage
(si, "Remote Job Not Found - JobID: " & jobID.ToString()) Return Nothing 
Else If (objJobStatus.RemoteJobState = RemoteJobState.RequestTimeOut) BRApi.ErrorLog.LogMessage
(si, "Remote Job Timed Out - JobID: " & jobID.ToString()) Return Nothing 
Else If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Exception) 
BRApi.ErrorLog.LogMessage(si, "Exception During Exeuction of Job: " & objRemoteRequestResultDto.RemoteException.ToString()) 
End If 
Next 
Else ' Exception occuring immediately during compile/initial run 
If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Exception) 
BRApi.ErrorLog.LogMessage(si, "Exception Executing Job: " & objRemoteRequestResultDto.RemoteException.ToString()) 
Else 
BRApi.ErrorLog.LogMessage(si, "General Job Execution Error - State: " & objRemoteRequestResultDto.RemoteResultStatus.ToString()) 
End If 
End If 
Return Nothing 
Catch ex As Exception 
Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) 
End Try 
End Function

GetSmartIntegrationConfigValue

This BR API allows access to the Local Gateway Local Application Data Settings. Accessing the remotely stored secret or customer-defined configuration values is done using a new "Remote" equivalent of the BR API namespace. This feature can be used to:

Reference configuration parameters in a remote business rule running on a Smart Integration Connector Local Gateway Server
Store credentials to network resources allowing the developer of remote business rules to reference values stored in the configuration file instead of having them hard-coded and viewable by anyone with permission to edit a business rule.

These configuration values are defined and edited using the Smart Integration Connector Local Gateway Configuration Utility. The API used to obtain these values is demonstrated in the full business rule example below:

NOTE: Requires allowRemoteCodeExec = True on Smart Integration Local Gateway.

Here is the rule in C#:

Copy

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Data.SqlClient;


namespace OneStream.BusinessRule.SmartIntegrationFunction.GetRemoteDataSourceSample
{
    public class MainClass
    {
        public DataTable RunOperation() 
        {
            DataTable dataTableResults = new DataTable();
            
            // Get the remotely defined connection string
            string connectionString = OneStreamGatewayService.APILibrary.GetRemoteDataSourceConnection("Sales_Data1");
            
            SqlConnection conn = new SqlConnection(connectionString);
            // Insert custom code
                        
            return dataTableResults;
        }
    }
}

Here is another example in VB.NET:

Copy

Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Shared.Wcf
Imports OneStreamGatewayService

Namespace OneStream.BusinessRule.SmartIntegrationFunction.SecretTester

Public Class MainClass
   Public Shared Function RunOperation() as bool
         Dim result As String
          ' APILibrary is the class containing new remote BRAPI methods
          ' GetSmartIntegrationConfigValue returns the string value of a found configuration
          ' element -- returns empty string if the specified key is not found
       result = APILibrary.GetSmartIntegrationConfigValue("test")
       return true
   End Function
 End Class
End NameSpace

GetGatewayConnectionInfo

From a OneStream business rule, you can invoke this API to obtain gateway details such as:

GatewayName: Name of the remote gateway
GatewayVersion: Version of the Smart Integration Connector Gateway Service running on the remote host
RemoteGatewayPortNumber: Bound Port at Gateway, the port of the remote service this direct connection is associated with.
RemoteGatewayHost: Name of the remote host associated with the direct connection.
OneStreamPortNumber: Bound Port in OneStream, the port number defined within OneStream that refers/maps to the specified direct connection.
SmartIntegrationGatewayType: Type of the Smart Integration Connection (0=Database Connection, 1=Direct Connection)

This API is useful for direct connections where the port number is required before connecting to remote services such as sFTP or remote Web APIs because each endpoint defined in OneStream to Smart Integration Connector Local Gateways has a different port number and would need to be known by the business rule developer at design time. This API makes it easy to look up the remote port by knowing the name of the direct connection defined in OneStream. It returns other useful information outlined below:

Here is the rule in C#:

Copy

GatewayDetails gatewayDetailInformation = BRApi.Utilities.GetGatewayConnectionInfo(si, "democonnection"); 
int oneStreamPortNumber = gatewayDetailInformation.OneStreamPortNumber;

Copy

Dim objGatewayDetails As GatewayDetails = BRApi.Utilities.GetGatewayConnectionInfo(si, "democonnection")

 sessionOpts.PortNumber = objGatewayDetails.OneStreamPortNumber

Copy

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Imports WinSCP
 

Namespace OneStream.BusinessRule.Extender.SFTP_Example
    Public Class MainClass
        Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
            
        Try
            
            ' Setup the objects to read Gateway Details from BRAPIs
            Dim objGatewayDetails As GatewayDetails = BRApi.Utilities.GetGatewayConnectionInfo(si, "WinSCP_Gateway")
            Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule
            (si, "SFTP_Password", Nothing, "rochester_gateway",String.Empty,"SFTP_Password", False, 600)
            
            ' Setup session options
            Dim sessionOptions As New SessionOptions
            With sessionOptions
                .Protocol = Protocol.Sftp
                .HostName = "localhost"           
                'HostName in this instance is in refrence to OneStream and will always be localhost.
                .UserName = "onestreamtest"       
                'sFTP server UserName
                '.Password = "**********"       
                'sFTP server Password
                .Password = CompressionHelper.InflateJsonObject(Of String)(si,objRemoteRequestResultDto.resultDataCompressed) 
                'result of remote business rule to provide password from Local Gateway Server
                '.PortNumber = 54321            'Bound Port in OneStream
                .PortNumber = objGatewayDetails.OneStreamPortNumber     'use BRAPI to populate Port Number
                .SshHostKeyFingerprint = "*****************************"  'SSH Host Key from sFTP host 
            End With
             
            Using session As New Session
                ' Connect
                session.Open(sessionOptions)
 
                ' Get the filepath
                ' BatchHarvest in this example is File Share / Applicaitons / GolfStream / Batch / Harvest
                Dim fileUPPath As String = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, Nothing)
                Dim fileDNPath As String = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, Nothing)
                                
                ' Upload or download files
                Dim transferOptions As New TransferOptions
                transferOptions.TransferMode = TransferMode.Binary
 
                Dim transferResult As TransferOperationResult
                ' Upload
                fileUPpath = fileUPPath & "\SFTP_TEST_UPLOAD.txt"
                transferResult = session.PutFiles(fileUPpath, "/", False, transferOptions)
                
                'Throw on any error
                transferResult.Check()
                
                ' Download
                fileDNpath = fileDNPath & "\SFTP_TEST_DOWNLOAD.txt"
                transferResult = session.GetFiles("\SFTP_TEST_DOWNLOAD.txt", fileDNpath, False, transferOptions)
                
                'Throw on any error
                transferResult.Check()
              
            End Using
 
            Return Nothing
        Catch ex As Exception
            Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
            Return Nothing
        End Try
        
        End Function
    End Class
End Namespace

Incompatible Business Rules

The following business rules are not compatible with Smart Integration Connector:

CreateSAPConnection
BRApi.Database.SaveCustomDataTable

If you attempt to use these business rules you will run into an error.

Obtain Data through a WebAPI

In this scenario, you have a WebAPI (IPaaS integration or another accessible REST API) to obtain and pass back data to OneStream. You can use the following remote business rule in Smart Integration Connector to invoke the API. If you have results that are in JSON format, you can convert them to a data table and send them back to OneStream. If the data from the WebAPI is in JSON, you can process the data in Smart Integrator Connector. Additionally, you can send the raw data back as a string to a data management job for further testing.

Direct connections are preferred for this method and can be invoked using business rules within OneStream similar to the sFTP example provided above.

Use the following OneStream business rule to invoke the request.

Copy

Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "RemoteWebAPISample", Nothing, "testconnection",String.Empty) If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.Success) Dim xfDT = New XFDataTable(si,objRemoteRequestResultDto.resultSet,Nothing,1000) End If

Use the following remote business rule to execute the request in C#

Copy

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using Newtonsoft.Json;
using System.Net.Http.Headers;

namespace OneStream.BusinessRule.SmartIntegrationFunction.RemoteWebAPISample
{
        public class MainClass
        {
               private static readonly HttpClient internalHttpClient = new HttpClient();
               
               static MainClass()
               {
               internalHttpClient.DefaultRequestHeaders.Accept.Clear();
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/x-www-form-urlencoded"));
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/octet-stream"));
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("text/plain"));
            internalHttpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("*/*"));                       
               }
               
               public DataTable RunOperation() 
               {
                var stringTask = internalHttpClient.GetStringAsync(https://localhost:44388/WeatherForecast);

                       var msg = stringTask;
                       DataTable dt = (DataTable)JsonConvert.DeserializeObject(stringTask.Result, (typeof(DataTable)));

                       return dt;
               }
        }
}