ETL approach for replacing multiple dates in one row.

View previous topic View next topic Go down

ETL approach for replacing multiple dates in one row.

Post  dwuser30 on Wed Dec 01, 2010 10:36 pm

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.

dwuser30

Posts : 7
Join date : 2010-08-29

View user profile

Back to top Go down

Re: ETL approach for replacing multiple dates in one row.

Post  ngalemmo on Wed Dec 01, 2010 11:59 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: ETL approach for replacing multiple dates in one row.

Post  dwuser30 on Thu Dec 02, 2010 9:21 pm

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.

dwuser30

Posts : 7
Join date : 2010-08-29

View user profile

Back to top Go down

Re: ETL approach for replacing multiple dates in one row.

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum