Surrogate/Business Key in ODS Environment

View previous topic View next topic Go down

Surrogate/Business Key in ODS Environment

Post  delish on Mon Jul 22, 2013 6:48 pm

I have an environment that consists of 4 source systems, an ODS, and a dimensional DW. They ODS uses surrogate keys and stores the source system/business keys. My question is, when creating the dimensional tables, what should my keys be?

1) Surrogate Key
option 1: Surrogate key copied from ODS
option 2: New surrogate key (identity column type in SQL Server)

2) Business Key
option 1: ODS business key (which is the same as the source system key)
option 2: ODS surrogate key

The possibly options would be both option 1s or both option 2s (you wouldn't mix SK option 1 with BK option 2 as they are incompatible). I'm thinking that a Kimball purist would go with option 2 but option 1 would make it a lot easier to trace data back to the source system. However, option 1 would require a full re-load of the dimension DW if the ODS were to be reloaded (since keys would change) whereas option 2 would not have that requirement.

Any thoughts?

delish

Posts : 5
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Surrogate/Business Key in ODS Environment

Post  ngalemmo on Tue Jul 23, 2013 3:02 am

The ODS should always provide the business key.  Never use a surrogate key from another system if at all possible.  You don't want the DW's keys to be dependent on another application.  The DW should be able to stand on its own.

But, it also depends on how you implemented the ODS. If the ODS is feeding the DW, it should include the business keys in the data it provides. If the ODS is accessing the DW for reference data, it may use the DW's surrogate key.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Surrogate/Business Key in ODS Environment

Post  delish68 on Fri Nov 08, 2013 1:42 am

ngalemmo wrote:The ODS should always provide the business key.  Never use a surrogate key from another system if at all possible.  You don't want the DW's keys to be dependent on another application.  The DW should be able to stand on its own.

But, it also depends on how you implemented the ODS.  If the ODS is feeding the DW, it should include the business keys in the data it provides.  If the ODS is accessing the DW for reference data, it may use the DW's surrogate key.
I'm just revisiting this and trying to understand the best approach to the DW business key.

1) The DW is being loaded from the ODS.
2) The ODS itself has surrogate keys (identity columns) and business keys (the source systems' primary key).

Should I populate the DWs business key columns with the ODS surrogate key or the ODS business key?

delish68

Posts : 3
Join date : 2013-07-22

View user profile

Back to top Go down

Re: Surrogate/Business Key in ODS Environment

Post  BoxesAndLines on Fri Nov 08, 2013 9:47 am

The business key.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Surrogate/Business Key in ODS Environment

Post  delish68 on Fri Nov 08, 2013 11:03 am

BoxesAndLines wrote:The business key.  
Any chance you could explain why it's better to have the business key flow through the ODS to the DW as opposed to keeping the PK/SK to BK pattern?

BK to BK (as suggested):


PK to BK:

delish68

Posts : 3
Join date : 2013-07-22

View user profile

Back to top Go down

Re: Surrogate/Business Key in ODS Environment

Post  ngalemmo on Fri Nov 08, 2013 1:10 pm

It creates a dependency on the ODS. Unless there truly is a dependency, such as sharing dimension tables, best practice is to avoid use of another system's surrogate key. Otherwise you will be in a world of hurt if that system changes or goes away.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Surrogate/Business Key in ODS Environment

Post  delish68 on Fri Nov 08, 2013 1:41 pm

Makes sense. Thank you.

delish68

Posts : 3
Join date : 2013-07-22

View user profile

Back to top Go down

Re: Surrogate/Business Key in ODS Environment

Post  BoxesAndLines on Fri Nov 08, 2013 2:20 pm

Plus people always want to query using business keys.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Surrogate/Business Key in ODS Environment

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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