Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

ETL approach for replacing multiple dates in one row.

Go down

ETL approach for replacing multiple dates in one row. Empty ETL approach for replacing multiple dates in one row.

Post  dwuser30 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  dwuser30 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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