Hello All,
Can some one tell me how to build a dimension table (which has start_Date,end_Date) when they is change in attribute new record need to be inserted and end date of old record should be updated with start date of new change
I'm able build dimeinsion using wizard and enabled track changes but end date of old attribute is not Channing.
i think I'm missing some option. can some one help how resolve this issue. Below is sample data.
Source table : Store
Store_ID Chain_Id Create_date
1 100 12/02/2007
2 100 12/02/2006
3 101 12/02/2006
1 102 04/03/2007
target table : store_dim
Start_date End_date Store_sk Store_id Chain_id
12/02/2006 04/03/2007 1 1 100
12/02/2006 01/02/2009 2 2 100
12/02/2006 01/02/2009 3 3 101
04/03/2007 01/02/2009 4 1 102
Thanks in advance.
Kanth
So basically Start_date is MIN(Create_date) for Store_id and End_date is MAX(Create_date) for Stored_id? If so, two subselects with joins should help you.
SELECT
A.CD as Start_date,
B.CD as End_date,
M.Store_id,
M.Chain_id
FROM
Store M,
(SELECT MIN(Create_date) AS CD, Store_id FROM Store GROUP BY Store_id) A,
(SELECT MAX(Create_date) AS CD, Store_id FROM Store GROUP BY Store_id) B
WHERE
M.Store_id=A.Store_id AND M.Store_id=B.Store_id
Go to Target domension table and see if track is 2 for identified attributes those can change in future.
And in target dimension you need to have Effective Start date and Effective End Date or Current Indicator.These are system generated and we should not source these attributes.
Thanks,
Siva