how to make others to accept the use of surrogate key?
3 posters
Page 1 of 1
how to make others to accept the use of surrogate key?
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.
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
Re: how to make others to accept the use of surrogate key?
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.
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.
SK | sysA_pk | sysB_pk |
1 | 1 | A |
2 | 2 | NULL |
3 | NULL | B |
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Re: how to make others to accept the use of surrogate key?
VHF is correct.
Tell them they would have a longer career in this field if they use surrogate keys.
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.
Re: how to make others to accept the use of surrogate key?
Thanks, everyone.
"... longer career ..." sounds good!
"... longer career ..." sounds good!
jon- Posts : 11
Join date : 2010-05-10
Similar topics
» Customer Dim, Vendor Dim, or combined?
» Should I use the surrogate key?
» Surrogate Key Disadvantages??
» New at BI and data warehousing and trying to make sense of it all.
» Slowly Changing Dimensions - Design Review (Need More Clarification)
» Should I use the surrogate key?
» Surrogate Key Disadvantages??
» New at BI and data warehousing and trying to make sense of it all.
» Slowly Changing Dimensions - Design Review (Need More Clarification)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|