06-12-2024 04:47 AM
Hi,
I am trying to load data from BiBlend to cube using a import validate load steps on my workflow and using also a connector, but i truggle a bit onthe connector Businessrule, i would like to know how the BR can connect to my biblend table.
I have already done a br connector cube to cube, but never a biblend to cube.
if you have any example of the script that i can add to my BR in order to connect the BiBlend Data table it would help a lot.
Thanks,
Solved! Go to Solution.
06-12-2024 05:09 AM
Hi, if you already have a connector to the cube, I assume you are mostly there. If you share your current BR, maybe folks here are able to help you over the finishing line more easily.
My first assumption is that you are not using the function to create an external database connection info:
Private Sub ExecuteSqlOnExternalDB(ByVal si As SessionInfo, ByVal script As List(Of String))
If Not script.Count = 0
'Use the name of the database, used in OneStream Server Configuration Utility >> App Server Config File >> Database Server Connections
Dim extConnName As String = "BIBLend Local Database"
Using dbConnApp As DBConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, extConnName)
'trigger only one sql cmd
Dim sqlCmd As String = String.join(vbnewline,script)
BRAPi.Database.ExecuteActionQuery(dbConnApp, sqlCmd, False, True)
End Using
End If
End Sub
06-12-2024 05:09 AM
Hi, if you already have a connector to the cube, I assume you are mostly there. If you share your current BR, maybe folks here are able to help you over the finishing line more easily.
My first assumption is that you are not using the function to create an external database connection info:
Private Sub ExecuteSqlOnExternalDB(ByVal si As SessionInfo, ByVal script As List(Of String))
If Not script.Count = 0
'Use the name of the database, used in OneStream Server Configuration Utility >> App Server Config File >> Database Server Connections
Dim extConnName As String = "BIBLend Local Database"
Using dbConnApp As DBConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, extConnName)
'trigger only one sql cmd
Dim sqlCmd As String = String.join(vbnewline,script)
BRAPi.Database.ExecuteActionQuery(dbConnApp, sqlCmd, False, True)
End Using
End If
End Sub
06-13-2024 06:45 AM
thank you Henning, this is what i was missing the external connection!
06-12-2024 05:24 AM
Try something like this, you'll have to update the connection, table name, query, etc.
Select Case args.ActionType
Case Is = ConnectorActionTypes.GetFieldList
'Get the list of field names in the source table by selecting one row
Dim lstFields As New List(Of String)({"TransID, VendorId, VendorName, InvNo, InvYear, InvMonth, InvDesc, GLAccount, WorkDay, ProdModel, UnitPrice, Units, Amount, PONumber"}) 'Update accordingly
Return lstFields
Case Is = ConnectorActionTypes.GetData
Dim connectionString As String = "BIBlend Local DB"
Dim provType As DbProviderType = DbProviderType.SqlServer 'Update accordingly
Dim sFirstPartNameBIBlendTable As String = "BIB_xxxx" 'Update with your table name
Dim sWFScenario As String = BRApi.Finance.Members.GetMemberName(si,DimTypeId.Scenario,si.WorkflowClusterPk.ScenarioKey)
Dim sWFTime As String = BRApi.Finance.Time.GetNameFromId(si, si.WorkflowClusterPk.TimeKey)
Dim sBIBlendTableName As String = $"{sFirstPartNameBIBlendTable}_{sWFScenario}_{sWFTime}"
Dim sqlStatement As New Text.StringBuilder
sqlStatement.Append("Select TransID, VendorId, VendorName, InvNo, InvYear, InvMonth, InvDesc, GLAccount, WorkDay, ProdModel, UnitPrice, Units, Amount, PONumber") 'Update accordingly
sqlStatement.Append($"From {sBIBlendTableName} Where 1=1")
api.Parser.ProcessSQLQuery(si, provType, connectionString, False, sqlStatement.ToString, False, api.ProcessInfo)
Case Is = ConnectorActionTypes.GetDrillBackTypes
Case Is = ConnectorActionTypes.GetDrillBack
End Select
Return Nothing
06-13-2024 06:44 AM
Thanks Fred Lucas, your script helped a lot!
06-13-2024 07:00 AM
I share my script here if it can help anyone else and dont hesitate to correct it if its bad written, it can help me to update it.
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.Connector.Central_Connector_BiBlend
Public Class MainClass
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 extConnName As String = "OneStream BI Blend"
Dim dbConn As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, extConnName)
Dim connectionString As String = dbConn.ConnectionString
'Get the Field name list or load the data
Select Case args.ActionType
Case Is = ConnectorActionTypes.GetFieldList
'Return Field Name List if using Field List Method to get field list in GetFieldList
'-----------------------------------------------------------
Dim fieldList As List(Of String) = GetFieldList(si, globals, api)
Return fieldList
Case Is = ConnectorActionTypes.GetData
'Dim connectionString As String = "BIBlend Local DB"
Dim provType As DbProviderType = DbProviderType.SqlServer 'Update accordingly
Dim sFirstPartNameBIBlendTable As String = "BIB_Dev_CENTRAL_TASKSCentral_Loads_Operationnel" 'Update with your table name
Dim sWFScenario As String = BRApi.Finance.Members.GetMemberName(si,DimTypeId.Scenario,si.WorkflowClusterPk.ScenarioKey)
Dim sWFTime As String = BRApi.Finance.Time.GetNameFromId(si, si.WorkflowClusterPk.TimeKey)
Dim sBIBlendTableName As String = $"{sFirstPartNameBIBlendTable}_{sWFScenario}_BiB_{sWFTime}"
Dim wfKey As String = String.empty
Dim wfname As String = api.WorkflowProfile.GetAttributeValue(scenarioTypeID.Actual,sharedconstants.WorkflowProfileAttributeIndexes.Text4)
'brapi.ErrorLog.LogMessage(si,"wfname = " & wfname)
'If wfname.Length > 0 Then
If wfname.Contains(sFirstPartNameBIBlendTable) Then
'Process Data
Dim sourceDataSQL As String = GetSourceDataSQL(si, globals, api, wfKey, sBIBlendTableName)
api.Parser.ProcessSQLQuery(si, DbProviderType.SqlServer, connectionString, False, sourceDataSQL, False, api.ProcessInfo)
'api.Parser.ProcessSQLQuery(si, DbProviderType.SqlServer, connectionString, False, "Select Entity From " & sBIBlendTableName, False, api.ProcessInfo)
Else
Throw New Exception("No BiBlend Table specified in Text4 of this WorkFlow")
End If
Return Nothing
End Select
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
'Create the field list SQL Statement Method to get field list (Recommended)
'-----------------------------------------------------------
Private Function GetFieldList(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer) As List(Of String)
Try
Dim sWFTime As String = BRApi.Finance.Time.GetNameFromId(si, si.WorkflowClusterPk.TimeKey)
'brapi.ErrorLog.LogMessage(si,"sWFTime = " & sWFTime)
Dim fields As New List(Of String)
'Comment/Uncomment to make fields valid. This controls what can be mapped in Data Source as well as what the Query itself will bring in
'fields.Add("Wfk")
'fields.Add("Wsk")
'fields.Add("Wtk")
'fields.Add("Fak")
'fields.Add("Ri")
'fields.Add("Rt")
'fields.Add("Si")
'fields.Add("Lb")
'fields.Add("Tv")
fields.Add("Entity")
'fields.Add("EtT")
'fields.Add("EtR")
'fields.Add("Pr")
'fields.Add("PrT")
'fields.Add("PrR")
'fields.Add("Cn")
'fields.Add("CnT")
'fields.Add("CnR")
'fields.Add("Vw")
'fields.Add("VwT")
'fields.Add("VwR")
'fields.Add("Sn")
'fields.Add("SnT")
'fields.Add("SnR")
'fields.Add("Tm")
'fields.Add("TmT")
'fields.Add("TmR")
fields.Add("Account")
'fields.Add("AcT")
'fields.Add("AcR")
fields.Add("Flow")
'fields.Add("FwT")
'fields.Add("FwR")
'fields.Add("Og")
'fields.Add("OgT")
'fields.Add("OgR")
'fields.Add("Ic")
'fields.Add("IcT")
'fields.Add("IcR")
fields.Add("FinalClient")
'fields.Add("U1T")
'fields.Add("U1R")
fields.Add("Client")
'fields.Add("U2T")
'fields.Add("U2R")
'fields.Add("U3")
'fields.Add("U3T")
'fields.Add("U3R")
'fields.Add("U4")
'fields.Add("U4T")
'fields.Add("U4R")
fields.Add("Governance")
'fields.Add("U5T")
'fields.Add("U5R")
fields.Add("Commodity")
'fields.Add("U6T")
'fields.Add("U6R")
'fields.Add("U7")
'fields.Add("U7T")
'fields.Add("U7R")
'fields.Add("U8")
'fields.Add("U8T")
'fields.Add("U8R")
'fields.Add("Fs")
fields.Add("RawAmount")
'fields.Add("ConvertedAmount")
'fields.Add("A1")
'fields.Add("A2")
'fields.Add("A3")
'fields.Add("A4")
'fields.Add("A5")
Return fields
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
'Create the data load SQL Statement
Private Function GetSourceDataSQL(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Transformer, ByVal wfKey As String, ByVal sBIBlendTableName As String ) As String
Try
'Create the SQL Statement
Dim statement As New Text.StringBuilder
Dim selectClause As New Text.StringBuilder
Dim fromClause As New Text.StringBuilder
Dim whereClause As New Text.StringBuilder
Dim orderByClause As New Text.StringBuilder
Dim fieldListForQuery As List(Of String) = GetFieldList(si, globals, api)
Dim selectedFields As String = String.Join(", ", fieldListForQuery.ToArray())
' Build SELECT clause
selectClause.Append("SELECT ")
selectClause.Append(selectedFields)
selectClause.Append(" ")
' Build FROM clause
fromClause.Append("FROM ")
fromClause.Append("v")
fromClause.Append(sBIBlendTableName)
fromClause.Append(" ")
' Build WHERE clause
whereClause.Append("WHERE 1=1 ")
' Get the list of assigned entities for this Workflow and put them together into a comma separated list
Dim assignedEntities As List(Of WorkflowProfileEntityInfo) = BRAPi.Workflow.Metadata.GetProfileEntities(si, api.WorkflowProfile.profilekey)
' Check to make sure there are entities assigned to the WFP
If assignedEntities Is Nothing Then
BRAPI.ErrorLog.LogMessage(si, "No Entities assigned to WF Profile")
Return Nothing
End If
' Loop through the Workflow Profile assigned Entities and get all the names as strings
Dim sbEntities As New StringBuilder()
Dim isFirstItem As Boolean = True
For Each entity As WorkflowProfileEntityInfo In assignedEntities
If Not isFirstItem Then
sbEntities.Append(", ")
End If
sbEntities.Append("'" & entity.EntityName & "'")
isFirstItem = False
Next
whereClause.Append("AND Entity IN(" & sbEntities.ToString() & ") ")
' Create the full SQL Statement
statement.Append(selectClause.ToString)
statement.Append(fromClause.ToString)
statement.Append(whereClause.ToString)
' Log the SQL statement for debugging
'BRAPI.ErrorLog.LogMessage(si, statement.ToString)
Return statement.ToString
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
06-13-2024 07:03 AM - edited 06-13-2024 07:20 AM
Thanks a lot for sharing this with the community, greatly appreciated! IMHO, this is how everyone benefits, sharing and learning from each other! 🙂
Just a quick suggestion, you can write the details of an error log message into the detail section of the error log, e.g. like this:
BRApi.ErrorLog.LogMessage(si,"sqlScript: See details below.", statement.toString)
or
Dim statement As New List(Of String)
BRApi.ErrorLog.LogMessage(si,"sqlScript: See details below.", vbCrLf & String.Join(vbCrLf, statement.ToArray))
That should make it much more readable especially for longer scripts. I also like to collect all of my error messages in a List(Of String) and write them into the error log only once, rather than creating a new entry in the error log each time I need to log something.
06-14-2024 12:30 PM
Thank you Henning, i will keep this in mind 👍
06-25-2024 01:20 PM
Hello everyone,
i want to add a drill back option to this connector and i was wondering if anyone had the chance to do it in other connector Business Rules or anys examples that can help.
Thanks