SensibleAI Forecast Connector Business Rule

Author: Kelly Reed, Created: 2024-06-27

In order to get data from SensibleAI Forecast (FOR) into the Cube, there are a few OneStream items that need to be built and configured. The first thing that needs to be built out is the Business Rule that runs the query to tell OneStream which table and columns to pull. There are many different types of business rules in OneStream and each different type serves a different purpose in the application. For the purposes of establishing a connection between any outside Data Source and OneStream, we need to write a ‘Connector’ Business Rule.

In this particular Connector, we are running a few different functions of SQL code in VB.Net language. The first function establishes the list of fields we want to pull from the FOR table we are reading. We want to explicitly list each column name that aligns with a dimension in OneStream (the relevance will become clear in Step 2):

Private Function GetFieldList(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer) As List(Of String)
    Try
        Dim fields As New List(Of String)
        fields.add("Date")
        fields.Add("SeriesName")
        fields.add("TargetDim1")
        fields.Add("TargetDim2")
        fields.add("Location")
        fields.add("Value")
        fields.add("XperimentKernelID")
        fields.add("XperimentBuildID")
        fields.Add("XperimentSetID")
        fields.add("BuildInfoID")
        fields.add("ProjectID")
        fields.add("PredictionCallID")
        fields.add("ForecastName")
        'add others here
        Return fields
    Catch ex As Exception
        Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    End Try
End Function

caution

Make sure to always include the last 7 Identifier columns listed above. Those are what we use to integrate Insights in OneStream. Learn more about Insights in the article titled “Integrating Insights”.

The next function we need is the SQL call that defines the table name and any necessary “Where” or “Order by” clauses to filter the table prior to loading it in.

info

Only filter items out in the business rule that you have no intention of ever wanting or needing to see in Stage, Workflow, or Insights. Anything that you may want to be able to drill-back on in the future should be kept.

Below, we are only filtering on Forecast data since we never want any Actuals coming into our Forecast Scenario.

Private Function GetSourceDataSQL(si As SessionInfo, globals As BRGlobals, api As Transformer) As String
    Try
        Dim sql As New Text.StringBuilder(128)
        sql.AppendLine("SELECT *")
        sql.AppendLine("FROM TABLE_NAME")
        sql.Append("WHERE SeriesName = '3 Model Cascade'")
        Return sql.ToString()
    Catch ex As Exception
        Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    End Try
End Function

Lastly, we want to call our functions in ‘Main’ and define our data connection (where the table is stored). This will depend on the application but will typically be either “OneStream BI Blend” or “AI Services Data Sources”.

Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer, ByVal args As ConnectorArgs) As Object
    Try
        'Get the query information
        Dim connectionString As String = "OneStream BI Blend"
        'Get the Field name list or load the data
        Select Case args.ActionType
            Case Is = ConnectorActionTypes.GetFieldList
                'Return Field Name List
                Dim fieldList As List(Of String) = GetFieldList(si, globals, api)
                Return fieldList
            Case Is = ConnectorActionTypes.GetData
                'Process Data
                Dim sourceDataSQL As String = GetSourceDataSQL(si, globals, api)
                api.Parser.ProcessSQLQuery(si, DbProviderType.OLEDB, connectionString, True, sourceDataSQL, False, api.ProcessInfo)
                Return Nothing
        End Select
        Return Nothing
    Catch ex As Exception
        Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    End Try
End Function

info

The business rule documented above is just one way the Connector can be setup. While this is a fully functional rule and acceptable layout, by no means does this article claim this to be the best business rule format or syntax.

Our Connector Business Rule is now complete and can be viewed at any time in the Business Rules page of the Application tab in OneStream:

Access the connector with Application > Business Rules > Connector