You can use Smart Integration Connector to access data from your Local Gateway Connection Data Sources or through Direct Connections.
Examples
Data Adapters Example
-
Go to Application > Presentation > Workspaces > [choose Workspace] > [choose Maintenance Unit] > Data Adapters.
-
Create or select an existing data adapter.
-
Verify that the Database Location is External and the External Database Connection is the custom connection that you defined earlier.
-
Enter a valid SQL Query.
-
Test
the data adapter and view the results.
SQL Table Editor Example
The following use case describes how to send a query after establishing a connection.
-
Go to Application > Presentation > Workspaces > [choose Workspace] > [ Maintenance Unit] > [choose Maintenance Unit] > Components > SQL Table Editor.
-
Create Dashboard Component or open a SQL Table Editor.
-
Choose SQL Table Editor and select OK.
-
Verify the following:
-
Database Location is External,
-
External Database Connection is the custom connection that you defined earlier,
-
Table Name is defined as the table you want to return data from.
-
-
Open the associated dashboard and run the query. The OneStream Smart Integration Connector will connect to the external database. If it connects correctly, the query will populate.
NOTE: If you plan on modifying data with SQL Table Editor using Smart Integration Connector, then you will need to write back data with a custom business rule using the Execute Dashboard Extender Business Rule feature under the Save Data Server Task action.
Grid View Example
-
Go to Application > Presentation > Workspaces > [choose Workspace] > [ Maintenance Unit > [choose Maintenance Unit] > Components > Grid View.
-
Create Dashboard Component or open a grid view.
-
Choose Grid View and select OK.
-
Configure the grid to use the data adapter.
-
Run the associated dashboard to see the data.
Perform a Drill Back
The following snippet describes how to load data from a local gateway connection data source and how to perform a drill back. The example below has been updated from the Standard SQL Connectors business rule. If you do not have the Snippet Editor with the OneStream Application, you can find the Snippet Editor on the MarketPlace.
-
Download and install the Snippet Editor from the MarketPlace.
-
Navigate to Application > Tools > Business Rules.
-
Expand Connector and select a Business Rule.
-
Navigate to Snippets > SQL Connector > Standard SQL Connectors.
-
Copy the Sample Business Rule.
-
Enter the connection name. In this example, “Northeast Sales” is the Gateway Connection Name as defined in the application configuration.
Copy' Create a Connection string to the External Database (prior to using the gateway)
Private Function GetConnectionString(ByVal si As Sessioninfo, ByVal globals As BRGlobals, ByVal api As Transformer) As String
Try
' Named External Connection
' -------------------------------------------
Return "Revenue Mgmt System"
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
' Create a Connection string to the External Database (using the Gateway)
Private Function GetConnectionString_Gateway(ByVal si As Sessioninfo, ByVal globais As BRGlobals, ByVal api As Transformer) As String
Try
' Named External Connection - Gateway
' ---------------------------------------------
Return "Northeast Sales"
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function -
Enter the drill back information to your database.
CopyIf args.DrillCode.Equals(StageConstants.TransformationGeneral.DrillCodeDefaultValue, StringComparison.InvariantCulturelgnoreCase) Then
' Source GL Drill Down
drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.FileShareFile, New NameAndDesc("InvoiceDocument","Invoice Document")))
drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.DataGrid, New NameAndDesc("MaterialTypeDetail","Material Type Detail")))
drillTypes.Add(New DrillBackTypeInfo(ConnectorDrillBackDisplayTypes.DataGrid, New NameAndDesc("MaterialTypeDetail_Gateway","Material Type Detail (Smart Integration)"))) -
Edit the level of drill back information returned.
Example: This example shows previously existing code that leverages a VPN based SQL connection and the Gateway based method shown in the second "Else If" block.
Else If args.DrillBackType.NameAndDescription.Name.Equals("MaterialTypeDetail", StringComparison.InvariantCultureIgnoreCase) Then
' Level 1: Return Drill Back Detail
Dim dri1lBackSQL As String - GetDrillBackSQL_Ll(si, globais, api, args)
Dim drillBackInfo As New DrillBackResultInfo
drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.OataGrid
drillBackInfo.DataTable = api.Parser.GetXFDataTableForSQLQuery(si, DbProviderType.SqlServer, connectionstring. True, drillBackSQL, False, args.PageSize, args.PageNumber)
Return drillBacklnfo
Else If args.DrillBackType.NameAndDescription.Name.Equals("MaterialTypeDetail_Gateway", StringComparison.lnvariantCultureIgnoreCase) Then
' Level 1: Return Drill Back Detail
Dim drillBackSQL As String = GetDrillBackSQL_Ll(si, globais, api, args)
Dim drillBackInfo As New DrillBackResultInfo
drillBackInfo.DisplayType = ConnectorDrillBackDisplayTypes.OataGrid
drillBackInfo.DataTable = api.Parser.GetXFDataTableForSQLQuery(si, DbProviderType.Gateway, connectionstring_gateway. True, drillBackSQL, False, args.PageSize, args.PageNumber)
Return drillBacklnfoPerform a Write Back
You can perform a write back using Smart Integration Connector leveraging the defined credentials to the local gateway dataSource at the Smart Integration Connector Gateway. If the credentials have permission to insert, update, and/or delete records in a remote dataSource, a OneStream business rule could be leveraged to write-back, update, and/or delete data as needed to support a financial process.
Example: The following example shows how to insert rows and columns to a Smart Integration Connector SQL remote database. Other types of databases (ODBC and OLEDB) are not compatible with this example.
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
Namespace OneStream.BusinessRule.Extender.SIC_BulkCopyExample
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
' SIC Gateway name
Dim sicGatewayName As String = "Northeast_HQ"
' SIC remote rule
Dim sicRemoteRule As String = "update_DB"
' SIC remote rule function
Dim sicRemoteRuleFunction As String = "RunOperation"
' Create and populate DataTable
Dim dt As New DataTable()
dt.Columns.Add("Scenario", GetType(String))
dt.Columns.Add("Time", GetType(String))
dt.Columns.Add("Entity", GetType(String))
dt.Columns.Add("Account", GetType(String))
dt.Columns.Add("Amount", GetType(Double))
dt.Rows.Add("Actual", "2023M3", "Houston Heights", "Net Sales", 100.25)
dt.Rows.Add("Actual", "2023M3", "South Houston", "Net Sales", 1230.66)
' Compress data table before passing into remote business rule
Dim dtCompress As CompressionResult = CompressionHelper.CompressJsonObject
(Of DataTable)(si, dt, XFCompressionAlgorithm.DeflateStream)
Dim dtObj(2) As Object ' Create object to store arguments for remote business rule
dtObj(0) = dtCompress ' compressed datatable
dtObj(1) = "SIC_WriteBack" ' remote database table name
dtObj(2) = "RevenueMgmt" ' remote data source name
' Execute remote business rule to bulk copy to target table
Dim bulkRemoteResults As RemoteRequestResultDto
=BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, sicRemoteRule,
dtObj, sicGatewayName,sicRemoteRuleFunction,String.Empty, False, 600)
' Get result status
If bulkRemoteResults.RemoteResultStatus <>
RemoteMessageResultType.RunOperationReturnObject Then ' Check if successful
' Failed, do something
BRAPi.ErrorLog.LogMessage(si,"Failed with status:" & bulkRemoteResults.
RemoteResultStatus.ToString)
End If
' Get returned message
Dim returnedMsg As String = CompressionHelper.InflateJsonObject(Of String)
(si,bulkRemoteResults.resultDataCompressed)
BRAPi.ErrorLog.LogMessage(si,returnedMsg)
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
The Extensibility Rule above calls the following Smart Integration Function:
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Data.SqlClient
Imports OneStream.Shared.Common
Imports OneStreamGatewayService
Namespace OneStream.BusinessRule.SmartIntegrationFunction.SIC_Functions
Public Class MainClass
' Function to bulk copy a compressed data table to a SQL database table
' Pass in compressed data table, database table name and data source name
Public Shared Function RunOperation(dtCompress As CompressionResult,tablename As String,
dataSource As String) As String
' -----------------------------------------------------------------------------------------------
' Get SQL connection string
Dim connString As String = APILibrary.GetRemoteDataSourceConnection(dataSource)
' Inflate compressed datatable
Dim dt As DataTable = CompressionHelper.InflateJsonObject(Of DataTable)
(New SessionInfo,dtCompress)
If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
' Check data table has been created and is populated
' Create sql connection to DWH
Using sqlTargetConn As SqlConnection = New SqlConnection(connString)
sqlTargetConn.Open ' Open connection
Using bulkCopy = New SqlBulkCopy(sqlTargetConn)
bulkCopy.DestinationTableName = tableName ' DWH table
bulkCopy.BatchSize = 5000
bulkCopy.BulkCopyTimeout = 30
bulkCopy.WriteToServer(dt) ' Bulk copy data table to database table
End Using
End Using
Else
Throw New Exception("Problem uncompressing data in SIC gateway")
End If
Return $"{dt.Rows.Count} rows bulk inserted into table {tableName}"
End Function
End Class
End Namespace
Support for SFTP
Smart Integration Connector provides support for connecting to SFTP servers to send and retrieve files. Perform the steps in the following sections to establish a connection and then send and retrieve files.
IMPORTANT: It is best practice to utilize SSH.NET for Secure File Transfer Protocol (SFTP) tasks.
IMPORTANT: For current WinSCP users, it is recommended to transition your SFTP operations to the SSH.NET library. In a future release of OneStream, WinSCP will be phased out.
NOTE: You must have an SFTP server available on a port. The port must be allowed for inbound and outbound connections on the Local Gateway Server. For this example, we have used port 22.
-
Login to OneStream.
-
Navigate to System > Administration > Smart Integration Connector.
-
Create a New Gateway and fill out all of the corresponding details for your Gateway and the Gateway Server.
-
From Connection Type, select Direct Connection (e.g., SFTP, WebAPI).
-
For Bound Port at Gateway, enter 22.
-
For Remote Gateway Host, enter the IP address or resolvable host name of the machine where your SFTP server is located.
-
For Bound Port in OneStream, enter -1 to automatically assign an unused port number. You can also specify your own port number by entering a value greater than 1024 and less than 65535. It is a best practice to use a higher value because it is less likely that number will be in use as this port number must be globally unique across all applications hosted on the OneStream servers.
-
Click OK.
-
Copy the Gateway to the OneStream Smart Integration Connector Local Gateway Server Configuration.
-
Save the Local Gateway Server configuration and restart the Smart Integration Connector Gateway service.
Example: Here is an example of how you can upload and download files through an SFTP extensibility rule.
C# SFTP Example
Below you can find the C# example for STFP.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
using Renci.SshNet;
namespace OneStream.BusinessRule.Extender.SFTP_SSH_C
{
public class MainClass
{
public object Main(SessionInfo si, BRGlobals globals, object api, ExtenderArgs args)
{
try
{
// --------------------------------------------------
// SSH.NET EXAMPLES
// --------------------------------------------------
// Setup SSH.NET session options from values in Cloud Administration Tools (CAT) Key Management - Secrets
var username = BRApi.Utilities.GetSecretValue(si, "SFTP-UserName");
var password = BRApi.Utilities.GetSecretValue(si, "SFTP-Password");
var authenticationMethod = new PasswordAuthenticationMethod(username, password);
var connectionInfo = new ConnectionInfo("52.151.252.48", username, authenticationMethod);
// Get the filepath - BatchHarvest in this example is File Share / Applications / GolfStreamDemo_v36 / Batch / Harvest
var fileDNpath = BRApi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, null);
var fileSFTPpath = Path.Combine(fileDNpath, "SFTP_TEST_DOWNLOAD_" + DateTime.UtcNow.ToString("MM-dd-yyyy-HHmmss") + ".txt");
var fileSCPpath = Path.Combine(fileDNpath, "SCP_TEST_DOWNLOAD_" + DateTime.UtcNow.ToString("MM-dd-yyyy-HHmmss") + ".txt");
// SFTP Example
using (var sftpClient = new SftpClient(connectionInfo))
{
sftpClient.Connect();
using (var downloadStream = new FileStream(fileSFTPpath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None))
{
sftpClient.DownloadFile("SFTP_TEST_DOWNLOAD.txt", downloadStream);
}
}
// SCP Example
using (var scpClient = new ScpClient(connectionInfo))
{
scpClient.Connect();
scpClient.Download("SFTP_TEST_DOWNLOAD.txt", new FileInfo(fileSCPpath));
}
return null;
}
catch (Exception ex)
{
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}
}
}
}
VB STFP Example
Below you can find the VB example for STFP.
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 Renci.SshNet
Namespace OneStream.BusinessRule.Extender.SFTP_SSH
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
' --------------------------------------------------
' SSH.NET EXAMPLES
' --------------------------------------------------
' Setup SSH.NET session options from values in Cloud Administration Tools (CAT) Key Management - Secrets
Dim username As String = BRApi.Utilities.GetSecretValue(si, "SFTP-UserName")
Dim password As String = BRApi.Utilities.GetSecretValue(si, "SFTP-Password")
Dim authenticationMethod = New PasswordAuthenticationMethod(username, password)
Dim connectionInfo = New ConnectionInfo("52.151.252.48", username, authenticationMethod)
'Get the filepath - BatchHarvest in this example is File Share / Applications / GolfStreamDemo_v36 / Batch / Harvest
Dim fileDNPath As String = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, Nothing)
Dim fileSFTPpath = Path.Combine(fileDNpath, "SFTP_TEST_DOWNLOAD_" & DateTime.UtcNow.ToString("MM-dd-yyyy-HHmmss") & ".txt")
Dim fileSCPpath = Path.Combine(fileDNpath, "SCP_TEST_DOWNLOAD_" & DateTime.UtcNow.ToString("MM-dd-yyyy-HHmmss") & ".txt")
' SFTP Example
Using sftpClient = New SftpClient(connectionInfo)
sftpClient.Connect()
Using downloadStream = New FileStream(fileSFTPpath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None)
sftpClient.DownloadFile("SFTP_TEST_DOWNLOAD.txt", downloadStream)
End Using
End Using
' ' SCP Example
Using scpClient As New ScpClient(connectionInfo)
scpClient.Connect()
scpClient.Download("SFTP_TEST_DOWNLOAD.txt", New FileInfo(fileSCPpath))
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 NamespaceTransferring Files from Local FileShare
You can use a Data Management job to move files Smart Integration Connector from a local FileShare. To do this, you build an extender business rule and call it through a data management job. This extender business rule will call a Smart Integration Function (remote function) and obtain the results.
Step 1 - Setup the Remote Server / Remote Share
To get started, setup the Smart Integration Function:
-
Navigate to Application > Tools > Business Rules.
-
Open the Smart Integration Function folder.
-
Create a new business rule (for example, TestFileRead).
-
Copy and paste the following business rule code snippet.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
namespace OneStream.BusinessRule.SmartIntegrationFunction.TestFileRead
{
public class MainClass
{
public byte[] RunOperation(string year)
{
string fname = @"c:\temp\hw_" + year + ".csv";
byte[] buffer = System.IO.File.ReadAllBytes(fname);
return buffer;
}
public byte[] GetOtherFileData(string year)
{
string fname = @"c:\temp\zw_" + year + ".csv";
byte[] buffer = System.IO.File.ReadAllBytes(fname);
return buffer;
}
public bool DeleteOldFileData(string year)
{
string fname = @"c:\temp\zw_" + year + ".csv";
try
{
System.IO.File.Delete(fname);
return true;
}
catch (IOException)
{
return false;
}
}
}
}Step 2 - Pull file from Extender Business Rule
-
Navigate to Application > Tools > Business Rules.
-
Open the Extensibility Rules folder.
-
Create a new business rule (for example, ProcessRemoteFileData).
-
Copy and paste the following business rule code snippet.
CopyImports 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 System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database
Namespace OneStream.BusinessRule.Extender.ProcessRemoteFileData
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
Dim stepNumber As String = "1"
If (Not args.NameValuePairs Is Nothing) Then
' Extracting the value from the parameters collection
If (args.NameValuePairs.Keys.Contains("step")) Then
stepNumber = args.NameValuePairs.Item("step")
End If
BRApi.ErrorLog.LogMessage(si, "File Processing Step: " & stepNumber)
End If
Select Case stepNumber
Case Is = "1"
GetData(si)
Return Nothing
Case Is = "2"
CleanupData(si)
Return Nothing
End Select
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
Return Nothing
End Function
Public Sub CleanupData(ByVal si As SessionInfo)
Dim argTest(0) As Object
argTest(0) = "2023"
' Here we are telling it to specifically call
Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "TestFileRead", argTest, "entergatewayname", "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
Dim objRemoteRequestResultDtoCached As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayCachedBusinessRule(si, "TestFileReadCache", argTest, "entergatewayname", String.Empty)
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
End Sub
Public Sub GetData(ByVal si As SessionInfo)
' Demonstrating how to pass parameters
' We create an object array that matches the number of parameters
' To the remote function. In this case, we have 1 parameter that is a string
Dim argTest(0) As Object
argTest(0) = "2023"
' This is where you can allow caching of the remote function. We are passing in true at the end to force the cache to be updated
' We are also allowing the function to run for 90 seconds.
' String.empty means this will look for a remote function/method called "RunOperation"
Dim objRemoteRequestResultDto As RemoteRequestResultDto = BRApi.Utilities.ExecRemoteGatewayBusinessRule(si, "TestFileRead", argTest, "entertestconnection", String.Empty,"TestFileRead", True, 90)
If (objRemoteRequestResultDto.RemoteResultStatus = RemoteMessageResultType.RunOperationReturnObject) Then
Dim bytesFromFile As Byte()
bytesFromFile = objRemoteRequestResultDto.ObjectResultValue
Dim valueAsString As String = System.Text.Encoding.UTF8.GetString(bytesFromFile)
Return valueAsString
bytesFromFile = Convert.FromBase64String(objRemoteRequestResultDto.ObjectResultValue)
'bytesFromFile = objRemoteRequestResultDto.ObjectResultValue
Dim valueAsString As String = System.Text.Encoding.UTF8.GetString(bytesFromFile)
' Do something with the files here....
BRApi.ErrorLog.LogMessage(si, "File Contents: " & Left(valueAsString,10))
' We are saving the file into the OneStream Share here
' This is an option to allow other OneStream functions to process the data
'Dim groupFolderPath As String = FileShareFolderHelper.GetGroupsFolderForApp(si, True, AppServerConfig.GetSettings(si).FileShareRootFolder, si.AppToken.AppName)
Dim groupFolderPath As String = BRAPi.Utilities.GetFileShareFolder(si, FileShareFolderTypes.BatchHarvest, Nothing)
Using sw As StreamWriter = New StreamWriter(groupFolderPath & "\outputfile.csv")
sw.Write(valueAsString)
sw.Close()
End Using
Else
If (Not (objRemoteRequestResultDto.remoteException Is Nothing)) Then
Throw ErrorHandler.LogWrite(si, New XFException(si, objRemoteRequestResultDto.remoteException))
End If
End If
End Sub
End Class
End Namespace -
Test your Extender Business Rule via the Execute Extender button in the toolbar.
Step 3 - Automate from Data Management / Task Scheduler
After the Extensibility Rule has been created and tested you can automate from a Data Management Job and associate Task Schedule. See Task Scheduler for more information.
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.
See Multiple WebAPI Connections for best practices on scenarios with multiple WebAPIs.
NOTE: Data transferred over a Direct Connection to a WebAPI is transferred directly over HTTP(S) and not converted to parquet format. OneStream does not control the return format.
Single WebAPI Connection
To set up a single WebAPI connection:
-
Set up a Direct Connection Gateway.
-
Export the Configuration and import to your Local Gateway Server. See the Export and Import the Gateway Configuration section for more information on this process.
-
Refresh your Gateways and verify this new Gateway is online.
IMPORTANT: Copy your Bound Port in OneStream. You will reference this later in the extensibility rule.
-
Create the Extensibility Rule below:
IMPORTANT: If you copy the business rule below and are having trouble communicating with your webAPI after compiling, ensure that you have set your host header correctly. Refer to "api.open-meteo.com" in the code sample below.
Copyusing System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using System.Net;
using System.Net.Http;
using Newtonsoft.Json;
using System.Net.Http.Headers;
namespace OneStream.BusinessRule.Extender.SIC_WebAPI
{
public class MainClass
{
private static readonly HttpClient internalHttpClient = new HttpClient();
public object Main(SessionInfo si, BRGlobals globals, object api, ExtenderArgs args)
{
try
{
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("*/*"));
// The header must be set or some connections maybe refused.
internalHttpClient.DefaultRequestHeaders.Host = "api.open-meteo.com";
// In this example, 20540 is the Bound Port in OneStream for the Gateway being used.
var stringTask = internalHttpClient.GetStringAsync("https://localhost:20540/v1/forecast?latitude=40.73&longitude=-73.94&daily=temperature_2m_max,temperature_2m_min&temperature_unit=fahrenheit&timezone=America%2FNew_York");
// Display the result in the exception dialog as an example.
throw new Exception(stringTask.Result);
}
catch (Exception ex)
{
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}
}
}
} -
Compile and test the business rule. If the extensibility ran successfully, you should see the correct data that corresponds with the business rule in the Exception dialog box.
Multiple WebAPI Connections
If you are using more than one WebAPI, the best practice is to perform this process using a single Gateway and multiple remote Business Rules.
Use the following OneStream business rule to invoke the request.
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 IfUse the following remote business rule to execute the request in C#.
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;
}
}
}
Sending Email through Smart Integration Direct Connections
Prior to using this business rule, you must have your email server configured. You must establish a direct connection before sending email. See Single Web API Connection for more information on setting up an initial direct connection. The following business rule can send email from an Extender Business rule.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using Microsoft.CSharp;
using OneStream.Finance.Database;
using OneStream.Finance.Engine;
using OneStream.Shared.Common;
using OneStream.Shared.Database;
using OneStream.Shared.Engine;
using OneStream.Shared.Wcf;
using OneStream.Stage.Database;
using OneStream.Stage.Engine;
using System.Net.Mail;
using System.Net;
using System.Net.Security;
using System.Text.RegularExpressions;
using System.Security.Cryptography.X509Certificates;
namespace OneStream.BusinessRule.Extender.smtp_direct_test
{
public class MainClass
{
public SessionInfo SI;
private const string smtpHostName = "smtp.azurecomm.net"; // expected name to match the cert.
public object Main(SessionInfo si, BRGlobals globals, object api, ExtenderArgs args)
{
var client = new SmtpClient();
var email = new MailMessage();
try
{
SI = si;
// Add custom validation callback to look for expected cert (Host will be localhost, which causes this to fail without a custom callback)
ServicePointManager.ServerCertificateValidationCallback += ValidationCallback;
client.UseDefaultCredentials = false;
client.Port = 20542;
client.Host = "localhost";
client.EnableSsl = true;
client.Credentials = new System.Net.NetworkCredential("<UserName>", "<Password>");
email.From = new MailAddress("DoNotReply@domain.com");
email.To.Add("test@onestreamsoftware.com");
email.Subject = "Test from SIC Gateway";
email.IsBodyHtml = false;
email.Body = "Forwarded test from SIC Gateway";
client.Send(email);
return null;
}
catch (Exception ex)
{
throw ErrorHandler.LogWrite(si, new XFException(si, ex));
}
finally
{
// Remove the custom ValidationCallback. It's recommended to remove this before any other network calls.
ServicePointManager.ServerCertificateValidationCallback -= ValidationCallback;
email.Dispose();
client.Dispose();
}
}
public bool ValidationCallback(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
{
var policyErrors = (sslPolicyErrors as SslPolicyErrors?) ?? SslPolicyErrors.None;
var certSubject = certificate?.Subject ?? string.Empty;
var certName = string.Empty;
// Extract the certName from the certSubject
string namePattern = @"CN=([^,]+)";
var match = Regex.Match(certSubject, namePattern);
if (match.Success)
{
certName = match.Groups[1].Value;
}
if (
(policyErrors == SslPolicyErrors.RemoteCertificateNameMismatch || policyErrors == SslPolicyErrors.None)
&& certName == smtpHostName)
{
// verify the certName matches the expected smtpHostName. No other SslPolicyErrors should be present.
return true;
}
else
{
return false;
}
}
}
}Support for DLL Migration
For OneStream Platform version 8.0 and above, all customer-supplied DLLs will be referenced through Smart Integration Connector. To use a DLL, copy the DLLs to the Referenced Assemblies Folder in the Local Gateway Server Utility and reference this DLL within your Smart Integration Function. See Referenced Assemblies Folder.
To verify the Referenced Assemblies Folder path:
-
Open the OneStream Local Gateway Configuration and Run as Administrator.
-
Navigate to and open Local Application Data Settings.
-
The file path under Referenced Assemblies Folder opens to the default location.
-
Click the OK button.
See the following SAP example for this process in use. See Smart Integration Connector Settings for more information on these fields.
Support for ERPConnect (SAP)
As an alternative to creating a Local Gateway Connection to your SAP database, you can connect to SAP using third-party DLLs, such as ERPConnect##.dll. ERPConnect##.dll can be referenced using a Smart Integration Connector Remote business rule. Although ERPConnect45.dll can no longer enable a connection to SAP systems starting with Platform version 8.0, a newer version ERPConnectStandard20.dll is available through the download DLL Packages from the Platform page of the Solution Exchange. ERPConnect requires additional libraries to be obtained from SAP as well, which can reside in the same reference assembly folder as ERPConnect.
For additional information, see the Theobald Software ERPConnect Help Center .
To get started:
-
From the Platform page of the Solution Exchange, download the DLL Packages, which contains the ERPConnectStandard20.dll file.
-
Extract the compressed zip file and then move the ERPConnectStandard20.dll to your Referenced Assemblies Folder.
-
Install the required Visual C++ Redistributable latest supported downloads.
-
Login to your sap.com account and then download SAP NetWeaver RFC Library DLL (sapnwrfc.dll) and associated icudt57.dll, icuin57.dll, icuuc57.dll files.
-
Copy SAP NetWeaver RFC Library DLL (sapnwrfc.dll) to the Referenced Assemblies folder.
-
Copy icudt57.dll, icuin57.dll, and icuuc57.dll to C:\Windows\System32.
-
-
Modify your business rules to use the ERPConnectStandard20.dll.
-
Navigate to Application > Tools > Business Rules.
-
Expand the Smart Integration Function list.
-
Create a new Smart Integration Function or select an existing one.
-
Click the Properties tab.
-
Enter ERPConnectStandard20.dll in the Referenced Assemblies field. The Smart Integration Connector Gateway server will attempt to locate this DLL in the previously defined folder: Referenced BusinessRule AssemblyFolder.
-
Add Imports for ERPConnect and ERPConnect.Utils.
Copyusing System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using ERPConnect;
using ERPConnect.Utils;
namespace OneStream.BusinessRule.SmartIntegrationFunction.ERP_Connect_Test
{
public class MainClass
{
public const string UserName = "";
public const string Password = "";
public const string Host = "";
public DataTable RunOperation()
{
using (R3Connection con = new R3Connection())
{
con.UserName = UserName;
con.Password = Password;
con.Language = "EN";
con.Client = "800";
con.Host = Host;
con.SystemNumber = 00;
con.Protocol = ClientProtocol.NWRFC; // Optional: If the NW RFC libraries are used.
con.UsesLoadBalancing = false;
con.Open();
ReadTable table = new ReadTable(con);
table.AddField("MATNR");
table.AddField("MAKTX");
table.WhereClause = "SPRAS = 'EN' AND MATNR LIKE '%23'";
table.TableName = "MAKT";
table.RowCount = 10;
table.Run();
return table.Result;
}
}
}
} -
Verify you can compile the function on your Gateway.
You are now ready to add your custom code.
