When using a merge statement to load a fact table it is important to ensure that the source component/s do not contain more than one record with the same composite foreign key. Often this can be resolved by performing an aggregate on the source component.
However, it is recommended that this aggregation be performed outside of the fact component in LeapFrogBI. In the below example of a fact component source sql, the join between t185.LFBI_RecordTime and the target dimension will occur before the aggregation per t-sql logical processin order. reference
This statement will work fine as long as the aggregation does not result in the combining of 2 or more records with different LFBI_RecordTime values. If this is the case, then a problem may occur when trying to lookup the correct foreign key (k1.[LFBI_ID]).
Generated by LeapFrogBI
SELECT
max(t185.[LFBI_RecordTime]) AS [LFBI_RecordTime],
ISNULL(k1.[LFBI_ID], -1) as [FK_d1000],
sum(ALL t185.[SubTotal]) AS [SubTotal_27],
sum(ALL t185.[TotalDue]) AS [TotalDue_28]
FROM [austx01\ss2012].[LFBIDestination01].[dbo].[t_T2000Params] AS t185
inner join [austx01\ss2012].[LFBIDestination01].[dbo].[d_d1000] as k1 ON
t185.[LFBI_RecordTime] Between k1.[LFBI_Start] and k1.[LFBI_End] and
t185.[SalesOrderID] = k1.[SalesOrderID]
GROUP BY
ISNULL(k1.[LFBI_ID], -1)
To avoid this issue, simply aggregate the source component by the business keys that will be used in the lookup “on” clause outside of the fact component. By doing so the fact source will be ensured to only include one record per combined foreign key. 
