approachs for generating surrogate key
Posted: Sun May 18, 2008 1:21 pm
Hello Gurus,
in v7, there are 5 method to implement surrogate key:
(1) ETL approach (using surrogate key stage). but where do you maintain the latest surrogate key ?
(2) Database approach. bulk load natural keys into a database and into a key mapping table. but I for every new record with new keys, I must put them into key mapping table to get corresponding surrogate key. is that right?
(3) Using a lookup stage to retrieve new surrogate key values from a database via a sparse lookup. what is the source for lookup stage ? what is the key for lookup ?
(4) why not write records directly into dimension table with surrogate key defined as identity ?
(5) create surrogate key using column generate stage. so is there the same issue as ETL approach ? (ie. HOW TO maintain the latest surrogate key ). any other issues ?
I have summarised 5 approach for surrogate key. and leave some question for every approach. hope I can get reply for some gurus.
thanks very much!
in v7, there are 5 method to implement surrogate key:
(1) ETL approach (using surrogate key stage). but where do you maintain the latest surrogate key ?
(2) Database approach. bulk load natural keys into a database and into a key mapping table. but I for every new record with new keys, I must put them into key mapping table to get corresponding surrogate key. is that right?
(3) Using a lookup stage to retrieve new surrogate key values from a database via a sparse lookup. what is the source for lookup stage ? what is the key for lookup ?
(4) why not write records directly into dimension table with surrogate key defined as identity ?
(5) create surrogate key using column generate stage. so is there the same issue as ETL approach ? (ie. HOW TO maintain the latest surrogate key ). any other issues ?
I have summarised 5 approach for surrogate key. and leave some question for every approach. hope I can get reply for some gurus.
thanks very much!