Surrogate/Business Key in ODS Environment

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?

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.

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?

The business key.

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:

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.

Makes sense. Thank you.

Plus people always want to query using business keys.