Recently while working on a different solution for loading inferred member solution in our fact table load I ran into this article on SQL Server Central article. This article explains very well what an inferred member is.
This is a great article and I started using it but found the need to have some modifications. The client I was working with rewrote the script component to fit these needs so with his permission I thought I would share it with you! To make sure I give the proper credit thanks for the great script David Purdy!
The package below demonstrates a typical fact table load that performs lookups to join back to each dimension to return back to the proper surrogate key. When that lookup does not return a key by default the package would fail but we are ignoring those nonmatches so that the next transform will pick them up.
In this solution the script component following the Lookup does a proper inferred member insert back into the dimension and returns the new key immediately after it is created.
The description property is used to centralize some of the logic for the insert and select required. Here is the what is in the description:
insert into dbo.DimCustomer(CustomerAlternateKey,AuditIsInferred) Values(?,1);select CustomerKey from dbo.DimCustomer where CustomerAlternateKey = ?
This helps handle the insert into the dimension and returns the new surrogate key. Notice that my example has a column called AuditIsInferred to identify if a record is inferred or not. The example below shows a dimension with a single business key column but it you had multiples here is a template to follow:
insert into schemaname.tablename(businesskeycolumn1,businesskeycolumn2…,InferredColumn) Values(?,?…,1);select surrogatekey from schemaname.tablename where businesskeycolumn1 = ? and businesskeycolumn2 = ?…
You will need to ensure all columns are checked in the Input Columns page and then make your surrogate key readwrite because you will need to write and return back a value for this column.
You must also point to the correct connection for the dimension on the connection managers page.
Back on the Script page hit Edit Script and paste in the code below. I’ve commented out instructions on how to adapt this code for your use.
' Microsoft SQL Server Integration Services Script Component
' Write scripts using Microsoft Visual Basic 2008.
' ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
Imports System.Data.OleDb
Imports System.Collections
Imports System.Reflection
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent
'If more than one natural key is used uncomment another line below
Private parmNatKeyIns1, parmNatKeySel1 As OleDbParameter
'Private parmNatKeyIns2, parmNatKeySel2 As OleDbParameter
'Private parmNatKeyIns3,parmNatKeySel3 As OleDbParameter
'Private parmNatKeyIns4,parmNatKeySel4 As OleDbParameter
Private objInsCommand, objSelCommand As OleDbCommand
Private inputBuffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer
Public htCache As New Generic.SortedDictionary(Of String, Integer)
Private CacheHits As Integer = 0
Private InferredMembers As Integer = 0
Private ForcedLookups As Integer = 0
Private NullNKs As Integer = 0
Public Overrides Sub PreExecute()
'This Sub initializes the SQL Connection and Stored Procedure
MyBase.PreExecute()
objInsCommand = New OleDbCommand
objSelCommand = New OleDbCommand
'Define the command object
With objInsCommand
.CommandText = Me.ComponentMetaData.Description.Split(New Char() {";"c})(0) + ";select scope_identity()"
.CommandType = CommandType.Text
.Connection = New OleDbConnection(Connections.Connection.ConnectionString)
.Connection.Open()
'Change to the proper data type for the natural key.
'If there is more than one natural key uncomment as many lines as necessary
parmNatKeyIns1 = .Parameters.Add("@NK1", OleDbType.VarWChar, 15)
'parmNatKeyIns2 = .Parameters.Add("@NK2", OleDbType.VarWChar, 3)
'parmNatKeyIns3 = .Parameters.Add("@NK3", OleDbType.VarChar, 10)
'parmNatKeyIns4 = .Parameters.Add("@NK4", OleDbType.VarChar, 10)
.Prepare()
End With
'Define the select command object
With objSelCommand
.CommandText = Me.ComponentMetaData.Description.Split(New Char() {";"c})(1)
.CommandType = CommandType.Text
.Connection = New OleDbConnection(Connections.Connection.ConnectionString)
.Connection.Open()
'If more than one natural key is used uncomment another line below
parmNatKeySel1 = .Parameters.Add(parmNatKeyIns1.ParameterName, parmNatKeyIns1.OleDbType, parmNatKeyIns1.Size)
'parmNatKeySel2 = .Parameters.Add(parmNatKeyIns2.ParameterName, parmNatKeyIns2.DbType, parmNatKeyIns2.Size)
'parmNatKeySel3 = .Parameters.Add(parmNatKeyIns3.ParameterName, parmNatKeyIns3.DbType, parmNatKeyIns3.Size)
'parmNatKeySel4 = .Parameters.Add(parmNatKeyIns4.ParameterName, parmNatKeyIns4.DbType, parmNatKeyIns4.Size)
.Prepare()
End With
End Sub
Public Overrides Sub PostExecute()
MyBase.PostExecute()
'Finalize expensive objects
htCache = Nothing
objInsCommand.Connection.Close()
Dim sMessage As String = Me.ComponentMetaData.Name & " (Inferred Members = " + InferredMembers.ToString + ";Cache Hits = " + CacheHits.ToString + ";Forced Lookups = " + ForcedLookups.ToString + "; Null NKs = " & NullNKs.ToString & ")"
Me.ComponentMetaData.FireInformation(0, Me.ComponentMetaData.Name, sMessage, "", 0, False)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Change below to use the surrogate key column used for this lookup
Dim SK_IsNull As Boolean = Row.CustomerKey_IsNull
'Change below to use the natural key column used for this lookup.
'If multiple natural keys were used uncomment before the 'Or' and add second column
Dim NK_IsNull As Boolean = Row.CustomerAlternateKey_IsNull 'Or Row.xxx_IsNull 'Or Row.yyy_IsNull Or Row.zzz_IsNull
'Change below to use the natural key column used for this lookup.
'If multiple natural keys were used uncomment before the '+' and add second column
Dim NK_CombinedKey As String = Row.CustomerAlternateKey.ToString '+ Row.xxx.ToString '+ Row.yyy.ToString + Row.zzz.ToString
Dim SurrogateKey As Integer
If NK_IsNull Then
'Change below to use the surrogate key column used for this lookup
Row.CustomerKey = -1
NullNKs += 1
ElseIf SK_IsNull Then
If Not htCache.TryGetValue(NK_CombinedKey, SurrogateKey) Then
'Change below to use the natural key column used for this lookup.
'If multiple natural keys were used uncomment the next line add second column
parmNatKeyIns1.Value = Row.CustomerAlternateKey : parmNatKeySel1.Value = parmNatKeyIns1.Value
'parmNatKeyIns2.Value = Row.xxx : parmNatKeySel2.Value = parmNatKeyIns2.Value
'parmNatKeyIns3.Value = Row.yyy : parmNatKeySel3.Value = parmNatKeyIns3.Value
'parmNatKeyIns4.Value = Row.zzz : parmNatKeySel4.Value = parmNatKeyIns4.Value
Try
SurrogateKey = CInt(objInsCommand.ExecuteScalar())
InferredMembers += 1
Catch ex As Exception
SurrogateKey = CInt(objSelCommand.ExecuteScalar())
ForcedLookups += 1
End Try
htCache.Add(NK_CombinedKey, SurrogateKey)
Else
CacheHits += 1
End If
'Change below to use the surrogate key column used for this lookup
Row.CustomerKey = SurrogateKey
End If
End Sub
End Class
Once this is done connect it back into your data flow stream and you should be prepared to handle inferred records now. This process will need to be done for each lookup that you wish to allow inferred records in your dimensions. Feel free to send me any questions or comments on how to improve it. I’m definitely looking for ways to make it more dynamic or even a C# version of this!
Devin Knight 