Aggregate to Prevent Duplicates on Fact Load

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

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]
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.

What We Do

We provide SMBs with custom data analytics services.

Built to Spec
We design custom solutions.

Low Cost per User
Empower as many report users as you like.

You Own It
Everything from the data solution to reports is yours to keep.

No upfront costs and no long-term commitment.

Available 24/7
We monitor and maintain everything 7 days a week.

Unlimited access to a team of data experts.

Find out how 
our clients
are getting

10x ROIwithin 6 months


Become Part of the LeapFrogBI Community

Join our newsletter to receive our latest insights and articles on automated, customized reporting in the healthcare industry.


Have any questions? Reach out to us, we would be happy to answer.