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.