Storing natural keys in the dimension tables, to aid ETL matching

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay Way - 07 Apr 2008 19:04 GMT

I'm a beginner at DW and BI. I'm posting to this NG because my current
(and first) data warehouse project is using SQL Server 2005 and my
question is regarding the Kimball book "The Microsoft Data Warehouse
Toolkit" (2006).

The authors are saying to store in your dimension tables the natural-
key counterpart to the primary identity key, in the enterprise data
model, so that you can match on it when looking up surrogate keys
during fact table load (page 267). But this seems like a pollution of
the dimensional model with ETL-support data if the natural keys were
not specified in the data model by a business requirement.

Am I reading this wrong? Thanks for any reply.

-- Josh

Reply to this Message

David Portas - 08 Apr 2008 20:55 GMT

> I'm a beginner at DW and BI. I'm posting to this NG because my current
> (and first) data warehouse project is using SQL Server 2005 and my
[quoted text clipped - 11 lines]
>
> -- Josh

You will always have a potential natural key in your tables if you eliminate
duplicate rows properly in your ETL process. It wouldn't make much sense as
a business requirement to have rows with different surrogate keys that were
duplicated on all other columns.

Signature

David Portas

Reply to this Message