Tuesday, October 30, 2007

Handling Late-Arriving Facts

When you want to handle this kind of data, you must set the surrogate key and the start date of the dimension data with a look up component at the first, and then you must check what data is the late-arriving one. The rows that those operation dates are lower than the start date of their dimension’s start date (Dim.StartDate>Fact.OperationDate) are late-arriving facts which their surrogate keys must be corrected with the appropriate dimension row. For specifying the correct dimension row you must send these facts to the look up component and correct the surrogate key. Finally you must add all these rows together.
The only tip that is remained is you must correct the dimension’s surrogate key with modifying the SQL of look up component in its advanced tab, for example for Order System the SQL must set as the follow:

Select *
From (Select * from [dbo].[DimProduct] as refTable)
where [refTable].ProductAlternateKey]=?
and startDate <= ? and endDate > ?

The parameters must be set to ProductAlternateKey, OrderDateAlternateKey, OrderDateAlternateKey.


Pedro said...

Good Post Amin!
If you write a few words more would be perfect for people that need more knowloedge in this subject!!
Cheers my friend!!!

Amin said...

Dear Pedro,
I will write another post about this and will explain the situation and the solutions,