Need to send data from OneStream to Snowflake using an existing SIC ODBC connection

If the credentials for the SIC Gateway 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.

Can you give a little more detail as to what you are trying to do? Built a solution that takes cube view data and write's it directly to snowflake, no flat files. Using a DM step, calls the extender rule to export the cv data, then calls the Smart Integration Function rule

Hi Tyesha,
Here’s an example to help get you started.

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
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.SnowflakeActualsExport
    Public Class MainClass
        Public Function Main(
            si As SessionInfo,
            globals As BRGlobals,
            api As Object,
            args As ExtenderArgs) As Object

            Try
                Select Case args.FunctionType

                    Case Is = ExtenderFunctionType.Unknown,
                         ExtenderFunctionType.ExecuteDataMgmtBusinessRuleStep

                        PushActualsToSnowflake(si)

                End Select

                Return Nothing

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

        End Function

        ''' <summary>
        ''' Reads actuals for the current workflow period from the OneStream
        ''' application database and writes them to Snowflake via the named
        ''' SIC connection. Deletes existing rows for the period first so the
        ''' operation is idempotent.
        ''' </summary>
        Private Sub PushActualsToSnowflake(si As SessionInfo)

            Try
                ' workflowTime is a platform-controlled token; interpolation is safe.
                Dim workflowTime As String = si.WorkflowClusterPk.WorkflowTimePk.ToString()

                Dim readSql As String =
                    $"SELECT Account, Entity, Amount
                      FROM XFW_Actuals
                      WHERE WorkflowTime = '{workflowTime}'"

                Dim deleteSql As String =
                    $"DELETE FROM ONESTREAM_ACTUALS WHERE WORKFLOW_TIME = '{workflowTime}'"

                ' Read source rows from the OneStream application database.
                Dim sourceData As DataTable
                Using appConnection As DbConnInfo =
                    BRApi.Database.CreateApplicationDbConnInfo(si)

                    sourceData = BRApi.Database.ExecuteSqlUsingReader(appConnection, readSql, True)

                End Using

                ' Write to Snowflake via the named SIC connection.
                Using snowflakeConnection As DbConnInfo =
                    BRApi.Database.CreateExternalDbConnInfo(si, "Snowflake")

                    ' Clear existing rows for this period before reloading.
                    BRApi.Database.ExecuteActionQuery(snowflakeConnection, deleteSql, Nothing, False)

                    ' Insert one row per source record. Account, entity, and amount
                    ' are internal application database values; workflowTime is a
                    ' platform token. Both are safe to interpolate.
                    For Each row As DataRow In sourceData.Rows

                        Dim account As String = Convert.ToString(row("Account"), CultureInfo.InvariantCulture)
                        Dim entity As String = Convert.ToString(row("Entity"), CultureInfo.InvariantCulture)
                        Dim amount As String = Convert.ToDecimal(row("Amount")).ToString(CultureInfo.InvariantCulture)

                        Dim insertSql As String =
                            $"INSERT INTO ONESTREAM_ACTUALS (ACCOUNT, ENTITY, AMOUNT, WORKFLOW_TIME)
                              VALUES ('{account}', '{entity}', {amount}, '{workflowTime}')"

                        BRApi.Database.ExecuteSql(snowflakeConnection, insertSql, Nothing, False)

                    Next

                End Using

            Catch ex As Exception
                Throw New XFException(
                    si, $"Failed to push actuals to Snowflake. WorkflowTime={si.WorkflowClusterPk.WorkflowTimePk}", ex)
            End Try

        End Sub
    End Class
End Namespace

Just for information,

using the SIC connection to Snowflake (if using ODBC) you are constrained to write back using INSERT commands as explained in the example above, which can be perfectly fine for not-too-large datasets. If you need to push back larger quantities of data, you might have to take an alternate approach which consists of sending data out in a file, to either Snowflake stage or any other file repositories supported by Snowflake, then issue a command to Snowflake to go and ingest that content which will then process the contente in bulk mode. If you need more assistance with this, our Remote Consulting team can assist you if you log a case through the Service Now Portal.

Joakim