Handling Failed FK Lookup

When a fact component attempts to load a record, a foreign key to a dimension lookup is performed.  This lookup (join) may fail if the dimension does not have a record that meets the fk join definition.  In this case LeapFrogBI will place a -1 in the FK_#### field to flag it as an unknown dimensoin member. This works fine, but in some cases it might be neccesary to try to reload the failed lookup facts during subsequent runs.  This can be simple logic that always reloads a specified range of dates.  Another approach would be to not load records into the fact table if a lookup fails.  Instead, take these failed lookup records and flag them as needing to be included during subsequent attempts to load in hopes that the dimension has a corresponding record available at this later time. The below image describes one option for fulfilling this need.  The purple boxes represent custom SSIS script tasks that can be included in a parent package which orchestrates the load precedence.  This scenario demonstrates how LeapFrogBI functionality can be extended to meet custom needs.  As long as the structure of the component outputs are not changed, customization is not going to interupt the LFBI component’s ability to execute successfully.  Manipulating data, altering statistics, converting tables to views, etc… are examples of the type of customization that is easily accomplished.

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.