ETL approach for replacing multiple dates in one row.
2 posters
Page 1 of 1
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.
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
Re: ETL approach for replacing multiple dates in one row.
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.
Re: ETL approach for replacing multiple dates in one row.
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.
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
Similar topics
» Date dimension multiple dates
» Track hierarchical slowly changing data which allows relational multiple future dates and retro terms
» Multiple Fact tables, Aggregate tables or a different approach
» Fact with eff / exp dates referencing dimension with eff / exp dates
» Data Cleaning - replacing nulls and addresses
» Track hierarchical slowly changing data which allows relational multiple future dates and retro terms
» Multiple Fact tables, Aggregate tables or a different approach
» Fact with eff / exp dates referencing dimension with eff / exp dates
» Data Cleaning - replacing nulls and addresses
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|