ETL approach for replacing multiple dates in one row.

We have a Fact table that stores several key dates in the life cycle of a customer. Each of these dates is supposed to be a surrogate key pointing to the date dimension. My questions pertains to efficiently replacing these dates with surrogate keys in the Date Dimension,when loading the Fact Table. Each row in my source stream contains such multiple dates. What would be the best approach of replacing all these dates?

I am currently looking at joining the source stream with the Date Dimension joined multiple times for each date column in the fact table.

Select test.*, D.Date_Ref_ID, d2.date_ref_id
from sourcetable test
inner join Dimension_ddb.dimdate D
on test.Date1 = D.full_dt
inner join Dimension_ddb.dimdate d2
on test.Date2 = D2.full_dt;
... upto 7-8 joins for 7-8 date columns.

I was thinking that there probably is a better alternative. Thanks in advance.

If you are using SQL to do ETL, then what you describe is pretty much how you do it. However, I would use outer joins to the date dimension to allow for bad dates in the source.

Ok, NG- thanks a lot.

I will self join the date dim a few times; and utilize a Database Join Lookup (within Kettle) that will let me join the source fact dates (as parameters) to each of those date dimensions and retrieve the different surrogate keys in one go.