Late Arriving Facts & SSIS
One of my colleague asked us how are we currently handling Late Arriving Facts against Type2 dimension within SSIS.My answer was we don’t. Well I do ask the client do you have late arriving facts. Most of the time they don’t know what is late arriving facts. Once explained they turned around and say this never happens in our organisation. I am sure pretty sure it does happen and most of the time client doesn’t recognise it.
If you do have a warehouse with Type1 dimension late arriving facts is not an issue for you. This is an issue when you have type2 dimension. Kimball call this "messy situation".
I did ask this to 3 SSIS developers at SQLPass this week. The answer is not easy and will get back to you. In the meantime we have discussed about this scenario among ourselves and decided one of two ways.
1. Do it using SQL rather than SSIS
2. Split the records first in the Data Flow Task and handle it using Lookup. I will explain it in detail how to do in a separate posting.
Thanks
Sutha