Track Changes.

[Srik]

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

[COGNOiSe administrator]

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

[SSNCOG]

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