Wednesday, October 17, 2007

Handling Missing Dimension Lookups

There are three methods for handling inferred members:

  • Pre-data flow checkyou must insert these members as inferred members before adding fact data to the fact table.The best situation that you can use this method is when the number of dimensions that support inferred members is one or two.In this approach you have to process the fact table, two times.

  • Post-data flow process (best solution)You must insert all fact data except for the inferred members that must store in a stage table, then you must insert inferred members, finally you should run the fact load process for the stage table.

  • In-line data flow approachyou must set the second lookup item cache mode to “No Cache” to force it to get the key from the database instead of getting from the not-updated cache.You must check that the inferred member has not added before the inserting, because it is probable that some fact data with that key are exists. For each inferred member you must first check for the record, second add it to the dimension, and finally get its surrogate key, therefore this process is suitable when the inferred members make up very small minority of source records.

2 comments:

Mohit Nayyar said...

There is one more way to handle this and I guess this is more efficient as well...

http://mohitnayyar.blogspot.com/2008/05/ssis-approach-to-handle-inferred.html

- Mohit Nayyar

Amin said...

Dear Mohit,
I think these solutions are "Pre-data flow checks", because you are checking whether the dimension member is defined or not before adding fact data.
Thanks for the practical solutions that you described. These are fantastic.

Best wishes,
Amin