Drill back on connector data source BusinessRule

Question

Hello everyone, Im tryng to add a drillback option to my BR connector, but i have no idea how i can write it down on my script, any help or instructions from you guys is well apreciated!Here is my complete BR connector it pulls data from a biblent table:
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 = " &amp; wfname)

'If wfname.Length &gt; 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 " &amp; 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 = " &amp; 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("'" &amp; entity.EntityName &amp; "'")
            isFirstItem = False
        Next

whereClause.Append("AND Entity IN(" &amp; sbEntities.ToString() &amp; ") ")

' 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
&nbsp;

Henning · Answer

Hi, you can also find an example of a connector with drill back in the documentation:
Connector Data Source (onestream.com)
However, maybe a drill back may not be necessary here? You are pulling data from a BI Blend table and users need to see the source result details in an easy to consume way. Drill back is one option, but a nice dashboard with some relational blend features may be another nice way to go about it. E.g. a cube view with the cube data can be on the left and a grid with the BI Blend source data on the right which displays the details of a clicked-on source POV from the cube view.

FredLucas · Answer

Hi&nbsp;mtejjini,
If you've never wrote a drillback type rule my advice would be to download a marketplace solution like People Planning for example and use one of the PLP connector rules as a starting point.

Forum Discussion

Drill back on connector data source BusinessRule

Related Content

Smart Integration Connector (SIC) Content

Data source Connector - Nested Drill back

launch automatically the import that uses a connector data source

connector business rule

Data BI Blend to Cube Connector