how to make others to accept the use of surrogate key?

View previous topic View next topic Go down

how to make others to accept the use of surrogate key?

Post  jon on Thu Aug 25, 2011 7:42 am

I have a ODS database pulls data from two different systems, say SysA and SysB. there are times, i have sysA_pk and/or sysB_pk. i.e.

sysA_pk sysB_pk
1 A
2 NULL
NULL B

To represent them, I need a surrogate key column for join with fact tables and for update.

A new hire reporter wants to handle the NULL value on sysB_pk this way, cancat sysB_pk with its name. so, sysA_pk=NULL will be sysB_pk+name

in a the data warehouse, is this cancatination a good thing?

I think, surrogate key is a better solution. But, the reporter doesn't believe it. what should I show them to make them accept the use of surrogate key?

thanks.

jon

Posts : 11
Join date : 2010-05-10

View user profile

Back to top Go down

Re: how to make others to accept the use of surrogate key?

Post  VHF on Thu Aug 25, 2011 9:29 am

Building compound concatinated keys can be a useful technique, but this is typically done to create a unqiue business key (aka natural key) to identify a dimension record.

A DW should always use a surrogate key (SK) to join facts to dimension tables. For one thing, an integer SK results in faster, more efficent joins than a character key. It also provides some isolation from changes to the business key over time.

I have two source systems feeding my DW. In my dimension records I have two nullable business keys and a SK as the PK for the dimension. This technique has worked very well for handling data originating from multiple systems. The business keys are used to look up the appropriate dimension record when loading from the source system(s), but the SK is always used for joins within the DW.

SKsysA_pksysB_pk
11A
22NULL
3NULLB


VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: how to make others to accept the use of surrogate key?

Post  ngalemmo on Thu Aug 25, 2011 10:17 am

VHF is correct.

But, the reporter doesn't believe it. what should I show them to make them accept the use of surrogate key?

Tell them they would have a longer career in this field if they use surrogate keys.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: how to make others to accept the use of surrogate key?

Post  jon on Thu Aug 25, 2011 10:12 pm

Thanks, everyone.

"... longer career ..." sounds good!

jon

Posts : 11
Join date : 2010-05-10

View user profile

Back to top Go down

Re: how to make others to accept the use of surrogate key?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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